… used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer…=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
… reporting any bugs at http://bugs.percona.com/ key_buffer_size=52428800 read_buffer_size=131072 max_used_connections=13 max_threads=2002… is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4432877 K bytes of memory…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 22, 2013 By 32 Comments
Does Percona have improvements to keep the InnoDB insert buffer from getting full (as in do background reads for ibuf merges when it is getting full)? That is the key to sustaining good throughput for InnoDB on iibench.
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…_method = O_DIRECT innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size = 36G innodb_log…_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
…) appears below: #####fixed innodb options innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_file_per_table = true innodb_data…_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
… server does not process query traffic except for replication. The buffer pool and adaptive hash index on the standby server will… would be heavily I/O bound after failover as the buffer pool pages were loaded from disk. Replaying Queries Working with… that match no rows or insert statements that have duplicate key errors may be faster than an actual database write. The…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside…_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_buffer_pool_size = 52G innodb_log_file_size = 2000M… 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
…(); for ($i = 0; $i < 100000; $i++) { $cache->save(new Object(), “key_$i”); } $totalTimeStore = $timer->stop(); $avgTimeStore = $totalTimeStore / 100000; $timer->start(); for…; $i++) { for ($i = 0; $i < 100000; $i++) { $obj = $cache->load(“key_$i”); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch / 1000000; While this benchmark… in mind that the entire data set fits into the buffer pool, so there are no reads from disk. However, there…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…. (The last 2-3 partitions tend to stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always… DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1) find partition…
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 only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be…
