… a hash table will be created based on the columns of table t1 that are two be used to join rows with table t2. This step is called the build step. After the hash table has been created, rows from table t2 are…_buffer_size=4M & mrr_buffer_size=4M Created_tmp_disk_tables 0 0 0 0 0 0 Created_tmp_tables 1 1 1 1 1 1 Handler…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x… disabled during these benchmark runs and that the disks are 4 5.4K disks in Software RAID5. Also note that the following… 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables 1 1 1 1 1 Created_tmp_tables 1 1 1 1 1 Handler_mrr_init…
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 …
Post: How to diagnose errors in the MySQL error log
March 26, 2012 By 4 Comments
…:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/tmp/#sql_21b2_0.MYI’; try to repair it 120326 16… understanding what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk filled up, MyISAM got an error. Little-known secret: MyISAM doesn’t handle a disk-full error gracefully 
Sometimes I’ve seen people having trouble…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… one of MySQL’s log or data files from disk. The database server will continue to function, however, because…’t have any problems until you try to create or drop a table, or the database itself. Likewise, if a… Overly zealous cron jobs to delete old files in /tmp can do the same thing. If the PID file…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… noticed a large number of temporary tables being created on disk. show global status like ‘Created_tmp%’ | Created_tmp_disk_tables | 91970 | | Created_tmp_files | 19624 | | Created_tmp_tables | 1617031 | Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables. mysqladmin…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Created_tmp_disk_tablesblogpercona.commanual Created_tmp_filesblogpercona.commanual Created_tmp…
Comment: MySQL Server Memory Usage
September 1, 2006 By 18 Comments
…’, ’0′ ‘Com_commit’, ’9558970′ ‘Com_create_db’, ’0′ ‘Com_create_function’, ’0′ ‘Com_create_index’, ’0′ ‘Com_create_table‘, ’31′ ‘Com_dealloc_sql’, ’0′ ‘Com…_truncate’, ’0′ ‘Com_unlock_tables‘, ’102′ ‘Com_update’, ’15146253′ ‘Com_update_multi’, ’0′ ‘Connections’, ’263413′ ‘Created_tmp_disk_tables‘, ’0′ ‘Created_tmp_files’, ’4001′ ‘Created_tmp_tables‘, ’669137′ ‘Delayed_errors…
Comment: Why MySQL could be slow with large tables ?
November 14, 2008 By 139 Comments
… M Handler_read_rnd_next 4,483 M Created_tmp_disk_tables 5,270 Created_tmp_tables 5,274 Created_tmp_files 37 k Key_reads 4,226 Key… (those using the mysqlclient10 compatibility package). old_passwords=1 big-tables [mysqld_safe] log-error=/var/log/mysqld.log pid-file… my.cnf for best performance & low disk usage? – may i remove old_passwords=1 & big-tables? — Web Server with Apache 2.2…
Comment: Why you should ignore MySQL's key cache hit ratio
March 1, 2010 By 27 Comments
…, total. The keys can either be in Cache or on disk. Now… Tune this server by allocating RAM that you have… frequency of what I call “expensive operations” Key_reads, opened_tables, created_tmp_disk_tables more than the ratio of the “expensive operation” to the…
