… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key… of having multiple key caches. But I’ll give the simple version here. In my opinion, you should choose a key_buffer_size that… and authoritative — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf, and you have…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql and an /etc/my.cnf, and the server is installed in /customsoftware/mysql with a different my.cnf…_clients | 205174 | | Aborted_connects | 29 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 9630066 | | Bytes_received | 38563413074… in the mysql.* tables, running mk-duplicate-key-checker to …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…my.cnf [client] port=3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql…cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key… transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can… I can analyze workload. P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…_allowed_packet). And keys for user locks! I set my max_allowed_packet bigger and ran the following on my laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get… setting up a my.cnf for a server, I usually calculate the Big Ones like the buffer pool, query cache etc; leave some…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
… make sure caches were clean. OLTP benchmark As the goal was to compare performance with different IO alignment, not different MySQL configurations… Server 5.0.92-87 with the following my.cnf configuration: [mysqld] datadir=/data/mysql socket=/var/run/mysqld/mysqld.sock innodb_file… binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want …key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf…cache->length); 14272 if (!(cache->buff=(uchar*) my_malloc(size,MYF(0)))) 14273 DBUG_RETURN(1); /* Don’t use cache */ /* purecov: inspected */ 14274 cache->end=cache…
Comment: What to tune in MySQL Server after installation
December 7, 2006 By 116 Comments
… are on same gig network. All my tables are MyIsam. Following is out of mytop MySQL on localhost (5.0.22-standard….7k qps varies from 3000 to 8000. My.cnf key_buffer = 4096M max_allowed_packet = 32M table_cache = 812 sort_buffer_size = 1024M read_buffer_size = 512M myisam_sort_buffer_size = 1024M thread_cache = 16 query_cache_size = 512M max_connections = 300 max_user_connections = 300…
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
…_zip_clean). Bugs Fixed: #802825 / #61341 in MySQL (Yasufumi Kinoshita). Compatibility Collations Two new …my.cnf have been kept for compatibility and warnings will be printed for the deprecated options (innodb_buffer_pool_shm_key…Fixes When adding a table to the cache, the server may evict and close …
Post: What's up with HandlerSocket?
March 28, 2011 By 42 Comments
…operations do not invalidate the query cache – Exactly what it sounds like, …potentially read stale data from the MySQL interface. No support for auto increment … a couple of lines to my.cnf to enable the InnoDB plugin, …has focused almost entirely on primary-key-select workloads because that is where…
