… data by primary key was slow. How slow ? Let me show. The query in question is (Q1): SELECT count(distinct username) FROM tracker where…: 38530 InnoDB_IO_r_bytes: 631275520 InnoDB_IO_r_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages… in order. +—————————+ | count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… server performance dramatically. The cause of majority of this slowness is not opening and closing tables, which can be …innodb_stats_on_metadata=0; Query OK, 0 rows affected (0.00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count…
Post: COUNT(*) for Innodb Tables
December 1, 2006 By 32 Comments
… guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often… for Innodb tables as different transactions may see different number of rows in the table. If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count… There are also clustered keys in Innodb which combine index access with data… completely disk bound can be very slow. One of the reasons elevating …
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… mentioned you can load Innodb Table Clustered Index in the buffer pool pretty efficiently by using something like SELECT count(*) FROM tbl WHERE non_index_col=0 This works relatively well (though can be slow for fragmented tables…
Post: Innodb Fuzzy checkpointing woes
May 10, 2006 By Leave a Comment
…time at certain time Innodb starts to flush pages very agressively seriously slowing down or even… case current implementation works just fine – Innodb flush threads selects range of LSN to flush each… related problem Innodb has – too many hard coded numbers. For example Innodb will count number of…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full: # Time: 111229 3… InnoDB_IO_r_wait: 0.001414 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… index the table I get the following record in the slow query log: # Time: 120911 17:18:05 # User@Host: root…_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F14 SET timestamp=1347398285; select * from sbtest where pad=’abc’; Rows_examined…_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…is event counters – “reservation count” and “signal count” show how actively innodb uses internal sync array – how…be free already. OS Waits are relatively slow, and if you get tens of …InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
…select … | 6360 | user | | NULL | 6361 | user | | NULL +——+——————+—————-+————- Notice all those processes in ‘statistics’ status. Why is that happening? Look at SHOW INNODB…
