…from MySQL 5.6 is to use a caching layer to store expensive result sets or objects. Some typical use cases include: Complicated query…key) if (data) { return data } data = callExpensiveFunction(params) storeCache(data, key) return data Memcached is a very popular (and proven) option used in …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… are two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single… indices to the buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… kernel in use for all of the benchmarks on both systems. The benchmark tool used was sysbench 0.5 and the version of MySQL used… run as well using the base HDD MySQL configuration for purposes of comparison. Given the apparent differences in time-based flushing in vCache and… = 0 query_cache_type = 0 ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M serverid = 101 key_buffer…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), … = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len …_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… is brewing. You pointed out an important use case for PARTITIONing (“uneven reads”, Re: #1); … are _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned … single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the … KEY `DepDelay` (`DepDelay`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… used further in query execution. There does not seems to be any optimizations applied to the query used for temporary table creation from the outer query…: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select * from…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… be traversed in both directions for different queries. This will use fast primary key for some queries and use key K as covering index for lookup in other… this column. This works because by scanning data in index order MySQL gets data in already sorted order and looks at “one group…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…hundreds of foreign key relationships in any complex database. We can find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN_USAGE …we can use the same SQL queries for quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> …
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise…: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row in set… to make a query faster, which means you need more manual care and tuning now. MySQL is conservative about “Using index” -in most cases…
