…numbers. Benchmark results For the purpose of this benchmark, I have used TPC-H Query #…, the InnoDB buffer pool size is set to 6G and the buffer pool was…, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_… 0, which means that the buffer was big enough and only 1 MRR range scan…
Post: Handling big result sets
June 26, 2006 By 13 Comments
…); // Performing SQL query $query = ‘SELECT * FROM longf’; $result = mysql_query($query) or die(‘Query failed: ‘ . mysql_error()); // retrive result while ($line = mysql_fetch_array($result, MYSQL_NUM)) { } // Free resultset mysql_free_result($result); $time_end… mysql_unbuffered_query and this link says: mysql_unbuffered_query() sends a SQL query query to MySQL, without fetching and buffering the result rows automatically, as mysql_query() does. So mysql_query…
Post: Large result sets vs. compression protocol
December 20, 2007 By 6 Comments
mysql_connect() function in PHP’s MySQL interface (which for reference maps to mysql_real_connect() function in MySQL C API) has a $client…. That’s almost 1.5x improvement in total run time. MySQL query time improvement is even greater. On the other hand 1…. The bottom line: if you’re fetching big result sets to the client, and client and MySQL are on different boxes, and the connection…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
…. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once you have spotted the query which… one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are the same (run SHOW INDEXES…-stats-sample-pages to get more accurate results. In recent Percona Server versions and MySQL 5.6 you can also store innodb…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…row store for queries which access a significant amount of data. One big reason for …the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] 12359 $ tail…READ-COMMITTED user=mysql wait-timeout=86400 To be continued You can now set up a…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
… set (0.00 sec) You will notice the second query without the additional derived
Post: Fighting MySQL Replication Lag
September 22, 2008 By 6 Comments
…query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag First fact you absolutely need to remember is MySQL… as one big chunk which can’t be interleaved by any other query execution. … will take SELECT query and the table to which store its result set and use …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… lot of help for big scans. This will reduce…sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query…query into several, run in parallel and aggregate result sets. So if you’re dealing with large data sets and complex queries…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into the query to change how query… you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query. I noticed in…
