…_server:3307/slow | percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin thread-pool –thread-pool-threads-count 100 –session-init-query \”set innodb…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_… enabled full query logging and log_slow_verbosity=full you can also … min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 98 1753697 # Exec time 52 …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value | +——————————+——-+ | Innodb…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… than data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated runs test… pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages….00 sec) mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… impact server performance dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved….08 sec) mysql> set global 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: InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
… innodb_log_file_size), MySQL 5.5.10 with innodb_adaptive_flushing=ON (default)). The problem here is that the system has quite slow… (and that is an important factor with slow disks); and 3) MySQL does not support innodb_log_file_size > 4GB (it is supported…. To make the situation even worse, the count of flushed neighbor pages is counted toward the number of pages we asked to…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…mysql> 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 … very slow. One of the reasons elevating this problem in MySQL is…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…the table I get the following record in the slow query log: # Time: 120911 17:18:05 # User@Host…the access type to the tables is “const” MySQL does not count it as access to two tables. In … 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; …
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 thousands …, thread declared inside InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query …
