… built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf…=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links is recommended to prevent assorted…
Comment: Percona XtraBackup 2.1.3 for MySQL available for download
May 30, 2013 By 3 Comments
… any bugs at http://bugs.percona.com/ key_buffer_size=52428800 read_buffer_size=131072 max_used_connections=13 max_threads=2002 thread_count=6 connection_count=6 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4432877 K bytes of…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache… #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… sense to configure MySQL as if it were using SSD storage or to just use an optimized HDD storage configuration? After creating…_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache… serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…not process query traffic except for replication. The buffer pool and adaptive hash index on the standby… You will need Percona Server in order to use innodb_fake_changes. Thread pool Percona Playback …no rows or insert statements that have duplicate key errors may be faster than an actual database…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…, faster inter-process/node communication). Results: First of all, we used a smaller dataset for this benchmarks round, since reportedly Dimitri…_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M… data and indices to the buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
…; $i++) { for ($i = 0; $i < 100000; $i++) { $obj = $cache->load(“key_$i”); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch / 1000000; While this benchmark… buffer pool, so there are no reads from disk. However, there is write activity stemming from the fact that this is using…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…a discussion is brewing. You pointed out an important use case for PARTITIONing (“uneven reads”, Re: #1); let …The last 2-3 partitions tend to stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc…is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1) …
Post: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… MyISAM, so they set key_buffer_size to 16G… and every few days MySQL crashes. Why ? Because key_buffer_size over 4GB in size… just fine until you have less than 4GB worth of key cache used and then it would crash. If you do not… not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… will use key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between “rate” and “ratio”. In this article, the key… just as fast to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let’s trust MyISAM… on the curve and use it as a heuristic for how big your working set is. Set the key_buffer_size to that…
