…). “The big LOAD DATA INFILE clogs the binary log and slows replication down. If the load takes 4 hours on the… slave’s filesystem). e.g. To reiterate this, make sure ‘/tmp/data.out’ does not exist in any of the slaves… you run this on master LOAD DATA INFILE ‘/tmp/data.out’ INTO TABLE some_data_table; Using this strategy, replication continues to happen…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 9, 2012 By 28 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… called the build step. After the hash table has been created, rows from table t2 are read and hash function is applied… that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL 5.5 and MySQL 5…=4M & mrr_buffer_size=4M Created_tmp_disk_tables 0 0 0 0 0 0 Created_tmp_tables 1 1 1 1 1 1….5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table… *************************** 2. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: tmp ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257… good tools to detect memory leaks like valgrind are too slow to run in production. So the best thing to do…
Post: Finding what Created_tmp_disk_tables with log_slow_filter
September 22, 2008 By 3 Comments
… 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 |… temporary storage for intermediate results. SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_diskâ€; Wait a…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…_query_time=0 and all the eXtra benefits from Percona slow query log patch), I knew some particular pages were taking… 0 0 0 0 0 # Tmp tables 0 15 0 1 0.03 0 0.17 0 # Tmp disk tbl 0 3 0…, 92% no # QC Hit 78% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no # String: # Databases prod…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… a table‘s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow… an application constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many a large tables, this can affect your server especially if… Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you can see db1 has about 294 tables…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…(*) from auto_inc; Compare that to Percona Server with log_slow_verbosity=full: # Time: 111229 3:11:26 # User@Host: msandbox… Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on… 0 0 # Boolean: # Filesort 100% yes, 0% no # Tmp table 100% yes, 0% no # Tmp table on 100% yes, 0% no # String: # Databases wp…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Slow_launch_threadsblogpercona.commanual slow_launch_timeblogpercona.commanual Slow_queriesblogpercona.commanual slow_query_logblogpercona.commanual slow… timed_mutexesblogpercona.commanual timestampblogpercona.commanual tmp_table_sizeblogpercona.commanual tmpdirblogpercona.commanual transaction_…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… you to change that behavior with the new parameter –log-slow-slave-statements. THE DETAILED INFORMATION ABOUT QUERY EXECUTION Each query…. # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No # Filesort: Yes Disk_filesort: No Merge_passes: 0… execution Tmp_table will be set. Sometimes that table must be created on disk instead of in memory, in such case Disk_tmp_table will…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
… from replication thread: mk-query-digest slow-log –filter ‘($event->{user} || “”) =~ m/[SLAVE_THREAD]/’ > /tmp/report-slave.txt In the report you….00 0 0.06 0 # Tmp table 14.19k 0 2 0.01 0 0.14 0 # Tmp table 8.30G 0 2.01M…% (5k) Full_join # 0% (7k) Full_scan # 0% (10k) Tmp_table # 0% (4k) Tmp_table_on_disk There is a lot of interesting you…
