June 20, 2013

Post: Performance Schema tables stats

May 2, 2011 By 2 Comments

…and against single tables. Now after adding multi-tables support to sysbench, it is … 1. table_io_waits_summary_by_table mysql> select * from table_io_waits_summary_… COUNT_INSERT: 4292 SUM_TIMER_INSERT: 1343751174852 MIN_TIMER_INSERT: 31913539 AVG_TIMER_INSERT: 313082268 MAX_TIMER_INSERT: 57121140547…

Filed Under: MySQL

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

…. The problem with multi value inserts however is the following – even if you know number of values in insert statement you do not…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see…

Filed Under: Events and Announcements Tagged With:

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

… of adding a single new node. When you insert a subtree, you’re inserting multiple nodes, and you want to create new… the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any path… B (going up) and the descendants of D (going down). INSERT INTO TreePaths (ancestor, descendant, length) SELECT supertree.ancestor, subtree.descendant…

Filed Under: Insight for Developers, MySQL

Comment: Edge-case behavior of INSERT...ODKU

October 28, 2012 By 6 Comments

… there’s no tomorrow due to mysql‘s gap locking. The method I use is to insert into these types of tables… an insert array. When there is no users left, we then do the insert / update. For insert implode the insert_array and use a multi-insert. i.e insert into users (username, host_id…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…like this: CREATE TABLE recovery LIKE `#sql2-3c13-14`; INSERT INTO recovery SELECT * FROM `#sql2-3c13-14`; But … there’s something of an inconsistency present when MySQL won’t allow us to change an FK-…but it will allow us to change from a multi-byte character column to a single-byte character column…

Filed Under: Insight for DBAs, MySQL

Post: MySQL caching methods and tips

April 4, 2011 By 5 Comments

… to satisfy a single request is not very efficient. Use multi_get when possible. Once again, caching entire portions of pages… since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands… can be indexed appropriately for your queries. Using INSERT .. SELECT for summary tables The INSERT .. SELECT approach works best when there is…

Filed Under: Insight for Developers, MySQL Tagged With: , , , ,

Post: Recovery deleted ibdata1

August 10, 2012 By 9 Comments

…3600 –mysql-user=root run sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Running…more than 1(it’s never less than 1): ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Infinite Replication Loop

October 10, 2011 By 2 Comments

… more transactions that are replicating over and over in a Multi Master Replication relationship. This happens because those transactions belong to… time those looping statement were first inserted. Another way to see it is by monitoring your MySQL server with cacti, you will… wrong server-id are performed on the two slaves (mysql 3 and mysql 4) and the new events (with server-id 1…

Filed Under: MySQL

Post: Why Swapping is bad for MySQL Performance ?

January 18, 2010 By 11 Comments

… of these were designed before SSD era. For example Insert Buffer in MySQL makes a special effort to avoid (delay) IO. If… on its own lets see what swapping does for concurrent (multi CPU, multi client) processing. Database Locks/Latches are typically designed to… consumption spike you may prefer to see slowdown instead of MySQL being killed because of out of memory but do react…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… MyISAM list. What happens if we take the MyISAM stopwords, insert them into a table, and configure that table to be…) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO… 4. This may or may not be the case with multi-byte character sets or with ideographic languages such as Japanese…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts
Newer Posts »