… once before timing, so when there is enough memory to cache every single row there should not be any disk reads… see what Benchmark really shows: Testing 100 out of 10000000 keys 24.79 seconds, 4034.53 lookups per second Testing 200… better results in such benchmark. Falcon with plans for row cache would fair better, so I would expect better results with…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… 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: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
…` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j… explain if someone curious, it did not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2… system calls it has to read row data from OS Cache. So what if we convert i and j columns to…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to… to be impractical. Here is why: List of rarely considered MySQL Server Memory Requirements Thread buffers can be allocated more than… values to run some queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was… – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce… you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the… I’ve seen query cache offering about double performance for simple queries with select done by primary key, obviously there is no…
Post: MySQL MyISAM Active Active Clustering - looking for trouble ?
March 15, 2007 By 4 Comments
… 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… takes long time for large tables. Furthermore as I remember MySQL simply was not repairing corrupted tables in this configuration even…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
…) Where group_id key is defined as KEY `group_id` (`group_id`,`parent_id`,`published`) As you can see MySQL selects to use… select_type: SIMPLE table: msg132 type: range possible_keys: group_id key: group_id key_len: 12 ref: NULL rows: 51 Extra: Using… causing their pages to be loaded replacing valuable data from cache). MySQL could look at the stats and adjust them appropriately – the…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… now. The query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with query cache size of 0 which…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL 5.0 with addition… MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key…
