… 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: 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: 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: 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: 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: 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: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… 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… the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really…
