… both cold and hot. SSB Flight #4 There is one query in this flight,…key buffer. I used ALTER 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 socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…MySQL buffer pools or anything else that’s on your mind! There are numerous solutions for MySQL high availability. Many rely on MySQL… that have duplicate key errors may be faster…4’s workload warmed up with chunk 3. The result is similar for every single graph, each chunk warmed up the buffer…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… = 4 #default_table_type = InnoDB thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…time=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=innodb \ –oltp-read-only=off run…_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
…4 Single core VM 528M RAM Host-Only network 1 Box with http/php, 1 box with memcache or mysql… ($i = 0; $i < 100000; $i++) { $obj = $cache->load(“key_$i”); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch / … entire data set fits into the buffer pool, so there are no reads from …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… last 2-3 partitions tend to stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE…(Re #4) There are _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs… a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1)…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… core of original implementation which appeared in MySQL 4.0 Query Cache was designed to work … “cheat” as MySQL 5.5 uses for Innodb Buffer Pool and have multiple MySQL Query Cache instances …applications in MySQL have very large number of queries doing updates through primary key and use primary key …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
….err 11M BH_RSI_Repository 4.0K brighthouse.ini 4.0K brighthouse.log 4.0K brighthouse.seq 964K mysql 123M ontime1 124M ontime10 123M… socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-engine=INNODB innodb-buffer-pool-instances=2 innodb-buffer-pool-size… innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock-wait-timeout…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…. If you aware of MySQL history you may think Key Cache scalability was fixed with new Key Cache in MySQL 4.1, and indeed previously…, In MySQL 4.1 the lock is held only when key block (1KB to 4KB) is being copied from Key Cache to thread local buffer… (“a”,”b”); +———————————————+ | cmd | +———————————————+ | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows in set (2…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. So first MySQL versions did not bother naming variables with storage engines prefix. This is why we have key_buffer, not “myisam_key_buffer” for… keep it 4-32MB to be used for temporary tables. key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement…
