…_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You can see that the buffer pool… performance. Other then that, I have also disabled query cache to avoid contention caused 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
… Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you… Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information… and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer…_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5… slow as compared to both MySQL 5.5 and MySQL 5.6. For MariaDB 5.5 the query time decreases when the join…
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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled…, and the query time for MySQL 5.6 and MariaDB 5.5 jumps to ~11min (this is the query time for MySQL 5.5… when the correct query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL 5.6…
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 queries through speedier joins, 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…
Comment: Too many connections? No problem!
March 15, 2012 By 28 Comments
… have my website develop in PHP with MYSQL, these are MYSQL values: datadir=/data/mysql socket=/data/mysql/mysql.sock max_connections = 600 wait_timeout…-slow-queries=/var/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/drbd…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries in local…
Post: Return of the Query Cache, win a Percona Live ticket
August 26, 2011 By 4 Comments
… Live London ticket! But first, what’s new with the MySQL query cache? You may know that it still has the same fundamental… was still a query-cache single choke-point in the server. Now that’s gone. As of MySQL 5.5, the query cache mutex isn’t hit at all if query_cache_type is zero. We made…
