… 1.53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_…in MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the… of improvement in the optimizer, as it is clearly a part of the optimizer’s job to select the best …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql…data | select count…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL…to see how much memory is being used by current MEMORY tables: mysql> select sum(data…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…in the dump but no data. Here is example: SESSION1: mysql> show tables; +——————–+ | Tables_in_dumptest | +——————–+ | A | | B | | C | +——————–+ 3 rows in set (0.00 sec) mysql> select count…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
…to show how to count the number of unique words, an how many times those words appear in…mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ 1 row in set (0.00 sec) How to…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…the ability to enable logging atomically, not just for new connections as in MySQL…SELECT wp_postmeta # 15 0xA01053DA94ED829E 0.0567 0.7% 213 0.0003 1.00 0.00 SELECT data_template_rrd data…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…full table scan to get the data. And this…the data file and sent and only they are counted. In practice this means Sort_rows may well understate sort activity happening on the system. Lets now increase sort_buffer_size and see how performance is affected: set sort_buffer_size=100000; mysql> select…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 16 Comments
…how long a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in…in an academic kind of way, to test different data…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
… response time in terms of 95/99 percentile and get your logs in the table, so how to get the data if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where page_type=’search’; +———-+—————–+ | count(*) | avg…
