… software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for…_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that… by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL. Now…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you… down. What you might see instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table participating…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer… slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and the values…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
…/log/mysql-slow-queries.log long_query_time=15 log-queries-not-using-indexes query_cache_type=1 query_cache_size=32M thread_cache_size=30 table_cache=4096 join_buffer_size=6M key_buffer_size=12M server-id = 1 log-bin=/data/mysql…
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… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… as a first-class member of the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions MariaDB in many places…, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented key cache for improved scalability That’s really just a sampling. A good starting point for learning more is the MySQL versus…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… was fixed in MySQL 5.1 – in this version table_cache was significantly redone and split in table_open_cache and table_definition_cache and I assumed the behavior may be different as well. MySQL…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… activity on the given table. Just run “FLUSH TABLES mytable” and examine open tables later – if you see this table in table cache again chances are it is being used. Note however if you’re starting MySQL… started) – MySQL anyway initializes the timer so it would not be much overhead to store that time together in the table cache. This…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted … your key buffer compared to operating system cache. I also use it to see which tables may be worth to review in… whatever database you have active. It does not work with MySQL before 5.0 still of course 
