… SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer analyzes queries for… spent developing and testing the query optimizer. The developers have to make a decision about which query patterns the optimizer recognizes, and which are…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…’s ability to optimize queries for a star schema. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each set is known as a “flight”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, each flight examines different time…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… complete performance schema, online DDL and several other InnoDB and query optimizer improvements. However, I plan to focus on a series of… necessary, depending on the query optimizer and the MySQL version, we always recommend to check manually the optimizations proposed by Percona Toolkit. The…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… nothing wrong with a virtual-machine approach, but for the optimal production deployment you want two identical physical boxes with at… example, if you see a large number of long-running queries in the output of SHOW PROCESSLIST that might be a… and PRM simply attempt to connect and run a simple query, but it’s always possible to build something more involved…
Post: Webinar: SQL Query Patterns, Optimized
May 20, 2013 By Leave a Comment
… type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance. The query patterns will include…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the perfect case for read-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by… list when creating read views. However, the more general descriptors optimization in Percona Server showed fairly close results in our tests… on our tests setup and clarifies the scope of descriptors optimization. Server Configuration: [mysqld] user=root port=3306 innodb_status_file…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
… read-only server nor rewriting application queries. Furthermore, it can be combined with the read-only optimization in MySQL 5.6 to….e. all queries are single-statement SELECT statements in autocommit. Which is the perfect case for read-only optimization in MySQL 5…-only optimization in MySQL 5.6 does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… this configuration B. The next one contained four SSD-specific optimizations based partially on some earlier work that I’d done…’d determined that configuration C appeared to produce the most optimal results, I moved on to reviewing FlashCache performance versus that… join_buffer_size = 4M thread_cache_size = 1000 query_cache_size = 0 query_cache_type = 0 ft_min_word_len = 4 thread…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… and omitting the corresponding secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server for MySQL was… returning immediately if there is a transaction that executed a query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… and omitting the corresponding secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server for MySQL was… returning immediately if there is a transaction that executed a query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix…
