…a “flight”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, each flight …used. O_DIRECT was used so, there was no caching of data at the filesystem level. The …datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… MySQL 5.6 is to use a caching layer to store expensive result sets or objects. Some typical use cases include: Complicated query… data set fits into the buffer pool, so there are no reads from disk. However, there is write activity stemming from… (~.06 ms/op) MySQL Select showed 27% increase over InnoDB fetch (~.09 ms/op) This replaced $cache->load() with $db->query(“SELECT * FROM…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… a solution for keeping the caches of a MySQL standby server hot using MySQL slow logs with long_query_time set to 0. Here… to a new name, and then truncates the original file. no copytruncate – Uses the rename() system call to move the file…/mysql/slow_query.log { nocompress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select @@global.long_query_time…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…. When it starts handling queries after failover, the lower cache hit rates can lead to …-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-… captured query traffic. This is an important step because update statements that match no…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single… vs. two-node (on the right) configuration. No surprises here, the general pattern is …= 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup …
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…. Percona Server 5.5 outperforms MySQL in about 2x times. Both MySQL 5.5.30 and MySQL 5.6.11 demonstrate notable drops… that had no notably impact on result. In the best possible combination I got ~15k operations per second for MySQL 5.6…_binlog=0 max_connections=5000 table_open_cache=5000 table-definition-cache=1000 query_cache_size=0 query_cache_type=0 performance_schema=0 #56only loose…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… vCache, and I also included a “no cache” test run as well using the base HDD MySQL configuration for purposes of comparison. Given… have “no cache“, “FlashCache”, “vCache with no time-based flushing”, and “vCache with time-based flushing.” As the images demonstrate, the no-cache system is…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… table is too big to be cached, but * One partition is small enough to be cached, and * Your accesses are to that…. (Re #4) There are _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at… only 5 times. (Usually 4 of the 5 are cached from previous queries.) (Re #2) I have rambled on about things as…
Post: MySQL Query Cache
July 27, 2006 By 70 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…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it… question is simple – because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are MySQL Caches – Innodb Buffer Pool and Key… is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed. OS Caches Operation Systems typically cache file IO…
