… an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can be… the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection… key, the result was a query that took half the time. An important conclusion of my…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep(2); set global long_query_time…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement …time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
…-only optimization in MySQL 5.6 does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped into regular (i.e. not marked as read-only) transactions. In this case MySQL 5.6 scalability goes back to the good old MySQL 5.5 times and we see identical results…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… (~.06 ms/op) MySQL Select showed 27% increase over InnoDB fetch (~.09 ms/op) This replaced $cache->load() with $db->query(“SELECT * FROM memcached… additional memcached hardware Cost of operations time to maintain an additional system Impact of warmup time to application Cost of disk space…
Post: 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar
March 22, 2013 By Leave a Comment
… typical but challenging MySQL database administration tasks. My focus will be on the following tools: pt-query-digest, to select the queries you should…, real-world MySQL database administration challenges, such as: Selecting which queries you should try to optimize to get better response times How to efficiently…
Comment: Rotating MySQL slow logs safely
April 19, 2013 By 7 Comments
… order of operations is: 1. logrotate renames the log file. MySQL continues to write to the renamed file because it has… paused (set global long_query_time=2000;) 2b. There is a small wait for buffered logs to be flushed (select sleep(2);) 2c. File handles are reopened (FLUSH LOGS;) 2d. Slow logging resumed (set global long_query_time=@lqt_save;)
Comment: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
… single SELECT queries (i.e. the –skip-trx option to sysbench). The 2nd one is the default sysbench mode, i.e. “BEGIN; SELECT…” mode. 2) There was no fix from Percona at the time MySQL bug #49169 was closed. However, as you can see from…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id…10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 …size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len =…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (Please provide specific references if you find otherwise.) I would argue that single-row queries (SELECT…
