…. 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… the query cache by set global query_cache_size = 0; All this on a fairly decent size server where I have 6GB innodb_buffer_pool_size split…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… the hot test. All OS caches are dropped at this time as well. These set of queries were tested on the SSB…_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage…_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…_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_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_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_type = 0 ft_min_word_len = 4 #default_table_type = InnoDB thread_stack = 192K tmp_table_size…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… default linkbench dataset has 10M ids(after load of data size of datadir ~10GB). We used this dataset to check server… threads. Then we loaded 10x dataset – 100M ids (size of datadir ~100GB), size of the buffer pool is the same – 30GB. So… sync_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
… non-trivial amount of updates to the database, all SELECT queries, even those participating in read-only transactions, start suffering from…_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M innodb_log_files_in_group… innodb_io_capacity = 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… partitioned table, as, depending on the size of the partitions, the whole partition can be cached, as opposed to a single table, where probably only recently read records will be cached, resulting in more disc reads. 2. If you need to… the same partition will be faster. 4. If you do queries on any other column, these could easily be quicker on…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… solution for keeping the caches of a MySQL standby server hot using MySQL slow logs with long_query_time set to 0…_query.log { nocompress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS…
Post: Beware large Query_Cache sizes
March 23, 2007 By 3 Comments
… ? If you set query_cache_size relatively high at 256MB or more. It can be seen worse if your query cache size is in Gigabytes. At the same time check how many queries do you have in cache – Qcache_queries_in_cache – if it… to solve the problem ? The easiest solution is to decrease query cache size – smaller values do not have such problems but also often…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… for query cache lock | 0.000004 | | Waiting on query cache mutex | 0.000002 | | Sending data | 0.003407 | | Waiting for query cache lock | 0.000003 | | Waiting on query cache…, requiring the cache to be locked! More specifically, the size of the blocks is set by the value of the query_cache_min_res_unit variable (4KB by default). For the query I…
