…might see instead is flushing tables regularly or reducing table cache reduces memory …tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32 bit… = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool…_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table ‘a2′ is in use (most likely by a MERGE table). Try FLUSH TABLES. mysql> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> truncate table a2; Query OK, 0 rows affected (0.00 sec) mysql…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…] Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL… ALTER TABLE to add an index then copy the old .frm file back into place re-open the table (Might need a FLUSH TABLES or mysqld restart here) From my testing, I saw that the error only happened when the table…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults… converting table to MyISAM I get about 600 deletes per second and about 4000 deletes per second for Innodb (with log flush… – in this case the table is small and fully fits in Innodb Buffer Pool and log flush on transaction commit was also…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… MySQL Caches you can restart MySQL and this is the only way to clean all of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… transactions which require a lot of complications and performance overhead. MySQL has to do several fsync() calls per transaction commit to… done in truly hot way without nasty “FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. Single Storage Engine…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL Versions to… recent Percona Server versions and MySQL 5.6 you can also store innodb stats in table so you have more control over… whenever it seems to be executing same on low level. FLUSH STATUS; SHOW STATUS can show whenever internal operation is different…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
… more tables than a mere 30,000. There have historically been no tests for anything near this many tables in the MySQL test… simple test program that creates 30,000 tables in a similar InnoDB configuration as default MySQL is pretty easy (easier than writing… of setting flush_method and flush_log_at_trx_commit I instead group the CREATE TABLE into transactions of creating 100 tables at a…
Post: Disaster: MySQL 5.5 Flushing
September 18, 2011 By 27 Comments
… raised topic of problems with flushing in InnoDB several times, some links: InnoDB Flushing theory and solutions MySQL 5.5.8 in search…. I took sysbench multi-tables workload, with 20 tables, 10,000,000 rows each. Total database size ~58GB. MySQL version: 5.5.16 Initial benchmark, which InnoDB configured for this hardware innodb_flush_log_at_trx_commit = 2 innodb_flush_method…
