… log every query. (See part two for handling massive slow log volume.) The slow logs are served, via HTTP, by mysql_slowlogd… option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby… or insert statements that have duplicate key errors may be faster than an actual database write. The slow logs were split into chunks…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… INSERT IGNORE INTO articles (id, body,)VALUES(3558268,’sample text’); Filtering Slow Query Log – Especially after the changes to log all queries slow query… to find more optimization candidates. So “tail -f mysql-slow.log | mysql_slow_log_filter -T 0.5 -R 1000″ will look… others, for example adding indexes often help SELECT queries but slow down INSERT/UPDATE ones. Final Note: You do not have to…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
….00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT RAND… (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 . . . mysql> INSERT INTO t(c) SELECT RAND() FROM t; Query OK, 2097152 rows… to tmp table” part will not be affected by a slow tmpdir, but rebuilding the indexes will obviously take longer. Another…
Comment: Thoughts on MySQL Replication
October 17, 2008 By 18 Comments
… order it was placed into the master’s binlog? Or, does it mean that each query gets properly inserted into the database successfully… be solved by grouping these dependent queries together behind the slow query and executing them in order. It’s fairly easy…-dependent queries shouldn’t have to wait behind an extremely slow, unrelated query. Note that there may be system processes outside…
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
… sec) mysql> insert into article_comment values(4300000000,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article_comment values(4300000001,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into article_comment…, you will see 0 while queries will just run insanely slow. So how to avoid this problem ? It is actually quite…
Comment: Why MySQL could be slow with large tables ?
September 24, 2008 By 141 Comments
… one by one, and, for each list, insert the 50 * 49 / 2 pairs into a table with 3 columns (id1, id2, cnt…. 200M rows for 300K lists), and I noticed that inserts become really slow. I guess this is due to index maintenance. Therefore… some concurrency related checks (as I have exactly one process inserting into the table, which I could even stop when I want…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… new table with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then….000002 | | closing tables | 0.000003 | | freeing items | 0.000007 | | logging slow query | 0.000002 | | cleaning up | 0.000002 | +———————-+————+ 17 rows in…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
… intended, but INSERTing in them is a nightmare. I use multiple record INSERTs (150 each time), and the first INSERT usually takes 600… tables). Once again, I use multiple inserts into the table, with sets of 150 records each: INSERT INTO T (col1,col2) VALUES (1,1…, taking in consideration what I want from it?! Could the INSERTs be slow, dued to the size of the PRIMARY KEY?! Could…
Comment: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 6, 2009 By 26 Comments
… using an I/O-efficient algorithm. But when loading data into an existing table with indexes, MyISAM is much slower. Try… a few insertions or updates. For MyISAM, incremental insertions are slow and produce fragmented indexes. TokuDB can handle the worst-case insertions without fragmentation…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
…of data into the buffer pool. But we have to do this without death-by-swapping, which would be extremely slow …| | NULL | 4776 | user | | NULL | 6318 | user | Sending data | insert into tableB… | 6322 | user | update | insert into | 6327 | user | | NULL | 6328 | user | statistics | select … | …
