…Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large… by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ …
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… at MySQL we often start “sharding” or splitting data other way because operationally working with large table and data sizes in MySQL can be painful, lacking online operations to alter table, defragmentation etc. Yes there…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… few seconds, yet the impact of FLUSH TABLES WITH READ LOCK can be quite large because of the time it may take… look at what the problem is. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as… no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their… or an application constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many a large tables, this can affect your server especially if…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… MySQL into removing smaller files with DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table… removals much more efficiently: EXT3 mysql> drop table large_table; Query OK, 0 rows affected (7.44 sec) XFS mysql> drop table large_table; Query OK, 0 rows…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… (opened_tables) to 10/sec or less by using large table_cache you should do so. However if you have so many tables you… fixed in MySQL 5.1, lets see how MySQL 5.4 behaves. MySQL 5.4.2 MySQL 5.4.2 has higher default table_open… not fixed even in Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… FAA, so there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row… machines. With enough machines, massive parallelism is possible and very large data sets may be processed. As each machine examines only…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
… fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL… out I’ve created table with simple structure: mysql> show create table dt2 \G *************************** 1. row *************************** Table: dt2 Create Table: CREATE TABLE `dt2` ( `grp` int(10…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test…_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE | OFF | ON | | SYNC_MASTER_INFO | 0… more consistency among variables values. I do not expect large impact here. table_open_cache is increased from 400 to 2000 by…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… referring to the amazing MySQL manual, especially the option and variable reference table. But just as frequently,…Key_writesblogpercona.commanual languageblogpercona.commanual large_files_supportblogpercona.commanual large_page_sizeblogpercona.commanual large_pagesblogpercona.commanual last_insert_…
