…; $i < 10; $i++) { for ($i = 0; $i < 100000; $i++) { $obj = $cache->load(“key_$i”); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch / 1000000; While this…/cmd Keep in mind that the entire data set fits into the buffer pool, so there are no reads from disk… replaced $cache->load() with $db->query(“SELECT * FROM memcached.container WHERE id=’key_id’”); id is PK of the container table While there…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… this spherical read-only cow and throw some data updates into the mix, limitations of read-only transactions become obvious. In…_per_table = true innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 2000 max_connections=5000 table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and indices to…
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table… than one would hope. As we already discussed MyISAM key cache has serious mutex contention issue as there is global mutex…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… key cache for all actively accessed tables (assuming there are only few of them), allocating key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static…) Pass this via SELECT INTO OUTFILE or pipe it to mysql directly as explained here to create key caches. Now you can…
Comment: Beware of MyISAM Key Cache mutex contention
August 14, 2008 By 10 Comments
… the MyISAM tables with one thread and two threads. table_cache=495, Open_tables=120, Opened_tables=133. 1. Two threads and one table, the average….02 sec) mysql> LOAD INDEX INTO CACHE lps.user_1,lps.user_2,lps.user_3,lps.user_4 IGNORE LEAVES; +————+————–+———-+———-+ | Table | Op | Msg…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
…, `links_in` int(10) unsigned NOT NULL, `cache_author` varchar(255) NOT NULL, `cache_site` varchar(255) DEFAULT NULL, `anchor` varchar(255… file for (case 1) – 43032M What if load data and indexes separately: load dump into table with default format and without indexes (case 2a… two times and space decreased by 6%. Now with compressed table: load dump into table with default format and all indexes (case 3) – 228m55…
Post: Living with backups
November 6, 2008 By 28 Comments
…nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary… Since the hit ratio becomes worse as the cache is being filled with random information additional …or include things like processlist information, CPU load avergages, I/O load, etc. In this case tar is…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…when we have significant variance in the load and many things can be happening concurrently….localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 … you might also look into changing how buffer pool is split into young and old sublists…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into…-host-cache skip-name-resolve sort-buffer-size=512K sql-mode=STRICT_TRANS_TABLES symbolic-links table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction…
