…problem for some users. Now, I understand that my PRIMARY KEY (`hid`,`mid`,`id`) where `id` is sequential, and `hid`,`mid` is low…key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer…myisam_recover socket=/var/lib/mysql/mysql.sock user=…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
… tuning MySQL‘s key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer… key cache hit ratio should be very high, ideally above 99.9%” or “if your key cache hit ratio is low, your key_buffer_size… might be causing a performance problem. But I haven’t shown you how to choose an appropriate key_buffer_size. Let’s look…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
MySQL 5.5 and Percona Server 5.5 do not solve all scalability problems…62743720, 62743962, 62744142, 62744144, 62744146) With secondary key defined on (group_id,deleted)… configured with innodb_buffer_pool_instances=16 and 400GB buffer pool. Here …cond_wait,os_event_wait_low,sync_array_wait_event,mutex…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… but it is only part of the problem. The other problem is – it is hard to manage resources, …dirty index blocks from key_buffer to disk, but it also comes at great danger if MySQL Server crashes or … cases table corruption will be mild, especially on low loaded servers. Many users do not even know …
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
…problem to a great extent but does not get rid of it completely. In the new releases of MySQL…buffer pool and prevents the buffer pool mutex from being kept for long) Take the buffer…not null auto_increment primary key) And during the benchmark,… MySQL. The dips are there, but not as low as in MySQL…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…cache alot but still we have db problems. a. The thing that I really, …mysql/slow-queries.log max_connections=650 open_files_limit=6144 max_connect_errors=10000 interactive_timeout=3600 wait_timeout=3600 key_buffer…of buffer pool innodb_thread_concurrency=64 # Default is 8 wich is a way to low. …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…ratio will not be good if it is too low there will be a lot of pages which …problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix this problem…other areas in Innodb than buffer-pool which had it locking relaxed in latest MySQL 5.0 Hopefully the …
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 45 Comments
… a while to write about using MySQL Partitioning for Performance Optimization and I just got …the low level are separate table. This means when you’re doing lookup by partitioned key you will… (and index) is much larger than buffer pool size. The first problem was replication lag, which are mainly…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
… key_buffer=750M join_buffer=10M max_heap_table_size=50M tmp_table_size=64M max_allowed_packet=16M table_cache=1800 record_buffer…_slow_queries=/var/log/mysql-slow.log sql-mode=TRADITIONAL concurrent_insert=2 low_priority_updates=1 The problem was that at about…, the above query would execute in 0.00 seconds. The problem was: why would this table get so fragmented or come…
Post: Opening Tables scalability
November 21, 2006 By 27 Comments
… only great test case for general MySQL problem. During warmup process I noticed I get very low CPU usage and disk Usage and… operation if you care about scalability. MySQL has same problem with key buffer but that was fixed in MySQL 4.1, this one still remains. I do not blame Monty – implementing first MySQL…
