…_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… growth in data-set on the insert rate. We can see that adding the secondary indexes to the table has decreased the…
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 …instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection Related Allocations Another set of …
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…_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_…, the InnoDB buffer pool size is set to 6G and the buffer pool was…
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=… of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache … in-memory workload, the InnoDB buffer pool size is set to 6G and the buffer pool was warmed up, …
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…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…somewhere around of 99% of advice about tuning MySQL‘s key cache hit ratio is wrong, even when you hear…like on a machine that’s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB, …it as a heuristic for how big your working set is. Set the key_buffer_size to that. If …
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… has a very similar task of managing its own cache of file descriptors (set by innodb_open_files) As the time allows…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… 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… all entries in the table_cache used, not grouped by the table, with thread_id using the table set (0 if table is not in use… started) – MySQL anyway initializes the timer so it would not be much overhead to store that time together in the table cache. This…
Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2
May 3, 2011 By 13 Comments
…_pct=80 innodb_file_format=barracuda innodb_file_per_table max_connections=2000 table_cache=2000 Percona Server 5.1 Results For better… to not to penalize further TPS. MySQL 5.6.2 Results Now let’s test MySQL 5.6.2. It is important… the tested setting–multiple purge thread implementation at the moment seems to be the one in Percona Server 5.1. MySQL 5…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…tables to the key caches: mysql> select concat(“CACHE INDEX “,table_schema,”.”,table_name,” IN “,table_schema,”_”,table_name,”;”) from information_schema.tables where table_schema=’test’ and table…
