…Time | State | Info | Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB…
Comment: ext4 vs xfs on SSD
April 24, 2012 By 23 Comments
…Selecting something as important as an Operating System … Take a look at the bug list Red Hat at ant one point — the list is quite long…or patch list for MySQL/InnoDB at any one point in time. The list will be long, and to the… for a pay check. So in the first count, you are reacting to a patch issue …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…InnoDB dataset size ~95G), because the query was taking far too long to execute, ~11 hours in case of MySQL…Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select…both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times …
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…innodb_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked. select count(1),field1 from Table where REQUEST_TIME…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…MySQL. This is very helpful for measurement as otherwise we might not catch some long…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: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…prevent wide innodb primary keys on the MV. mysql> select mview…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select… method takes a very long time to refresh: mysql> call flexviews….
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…long does it take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample lock in share mode; +———-+ | count…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… of time which takes fraction of second to do, but it is not how it happens in practice. mysql> … 7 rows in set (0.00 sec) So how long does it take: mysql> select count(*) cnt,c from gt group by c …Do not forget about it especially if running only Innodb tables so you may think there is no …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
…taking a consistent backup of your MySQL…InnoDB…mysql-bin.000022 | mysql mysql> select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql…
