… 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…_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer_size=10G # Disabling symbolic-links is recommended…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…both cache devices were set up so that time-based flushing was disabled. …0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ …cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems… MySQL Query Cache so it once again is appealing to improve performance for MySQL Applications ? Make it Lockless Can we re-implement MySQL Query Cache… applications. Many applications in MySQL have very large number of queries doing updates through primary key and use primary key for selects. I…
Post: Crashes while using MyISAM with multiple key caches
July 18, 2009 By Leave a Comment
… couple of years I have ran into random MySQL crashes in production when multiple key caches were used. Unfortunately this never was frequent… multiple key caches but to online key cache resize. It is just this code most actively used in case you’re using multiple key caches. It… be buggy and crashing I’d like to see them disabled as run time and enabled with –enable-buggy-features switch…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… in this system. innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema queries… MySQL 5.5 and before it was “ON” by default with query cache size of 0 which makes it disabled. I wish query_cache…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
… use MySQL with Active Active Clustering and MyISAM tables if you follow certain rules like enabling external locking, disabling query cache and delayed key… locks besides extra overhead you get from external locking. Plus key cache have to be invalidated with remote writes which makes even…
Post: Getting real life query speeds with MySQL
January 10, 2007 By 10 Comments
… often using SELECT SQL_NO_CACHE or disabling QueryCache for the test. This works by blocking query cache but still repeated runs execute… kind of other caches and QueryCache is only first in line. There is also all kinds of page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer… 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6 which takes 0.16s less as compared to MySQL…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… by changes done in MySQL 4.1 As Monty explained us in MySQL 4.1 the change to key cache locking was done so…. A second confirmation that key_cache is a problem is benchmark run with disabled key_cache (=0). Results for MyISAM with key_buffer_size=0 Threads… have more queries per second for 4, 8, 16 with disabled key_cache. The results for 2 threads is however quite unexpected. Though…
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 17 Comments
…s come to the MySQL configuration. MySQL Configuration I used Percona…_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0…, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`…
