…. Basically I have a problem regarding long running select query. A query runs for say 300 seconds and because the resultset is… table on disk. Now during those 300 second, other ‘innocuous’ queries which perform small insert or updates into completely different table… ‘Table_locks_immediate’ keeps increasing I have disabled the query cache by set global query_cache_size = 0; All this on a fairly decent…
Post: Percona Server for MySQL 5.5.31-30.3 now available
May 24, 2013 By Leave a Comment
… introduced a regression that could cause a memory leak if query cache was used together with InnoDB. Bug fixed #1170103. Fixed the…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… known as a “flight”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, each flight examines different time… the hot test. All OS caches are dropped at this time as well. These set of queries were tested on the SSB… in the largest table. You can find the individual SSB query definitions in my previous blog post. Test environment These tests…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… to use a caching layer to store expensive result sets or objects. Some typical use cases include: Complicated query result set (search… pages) Full objects (user or cart object built from several queries) Infrequently changing data (configurations, etc) In pseudo-code, here is… over InnoDB fetch (~.09 ms/op) This replaced $cache->load() with $db->query(“SELECT * FROM memcached.container WHERE id=’key_id’”); id…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… that’s much smaller than the working set, the no-cache approach is fairly crippled and ineffectual. FlashCache does substantially better…=500000 max_connect_errors = 10 table_open_cache = 10240 max_allowed_packet = 16M binlog_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…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…=500000 max_connect_errors = 10 table_open_cache = 2048 max_allowed_packet = 16M binlog_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…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…_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: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by definition, read-only transactions… non-trivial amount of updates to the database, all SELECT queries, even those participating in read-only transactions, start suffering from… innodb_io_capacity = 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… have recently accessed pages. When it starts handling queries after failover, the lower cache hit rates can lead to outages. Specifically, at… from disk. Replaying Queries Working with Groupon, we have developed a solution to keep the standby server’s caches hot. (See my Fosdem 2013 slides for information about discarded designs and benchmarks.) First, we set long_query_time…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… about Percona or MariaDB. (Re #4) There are _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of… if you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed… only 5 times. (Usually 4 of the 5 are cached from previous queries.) (Re #2) I have rambled on about things as…
