…of queries. The query optimizer analyzes queries for common patterns the MySQL …query in a suboptimal form. Q: Doesn’t the primary key solution for random selection…performance like this, so it pays to test all different solutions, and measure the resulting performance! Q: Bill, back to the query…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions (TPS…=OFF performance_schema=0 warmup To warmup server and load data and indices to the buffer pool we use following queries: select avg…
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… the alpha stage. We are going to blog about other MySQL performance improvements introduced in our latest Percona Server release, stay tuned…
Post: 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar
March 22, 2013 By Leave a Comment
… to perform typical but challenging MySQL database administration tasks. My focus will be on the following tools: pt-query-digest, to select the queries you… these tools to solve typical, real-world MySQL database administration challenges, such as: Selecting which queries you should try to optimize to get…
Post: Webinar: SQL Query Patterns, Optimized
May 20, 2013 By Leave a Comment
…query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance. The query patterns will include: Exclusion Join Random Selection…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…InnoDB and TokuDB performs if PK is (`id`,`hid`,`mid`). This also will affect select performance, so we … = 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
…s ad hoc query leads to scanning the entire last two partitions, performance is not bad. …are _no_ parallel queries (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs …otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… dashboard_customer_sales is built), then the query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price… Deleted 100 order lines. Refreshed both views. Compared the refresh performance and query results. The view type does not usually* affect the…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row …Query performance continues to improve when grouping and filtering is used. Again, notice Q9. It doesn’t use any features which Shard-Query…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… by aggregating query log with mk-query-digest. When I filtered out only queries of … system shows from performance at 1 thread to the peak performance we see the …SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql…
