…queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries …one features even higher QPI speed (and thus, faster inter-process…mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… disk problem, I pointed the MySQL 5.6.10 at the MySQL 5.5.30 data directory. Tthe speed was essentially the same… tested performance of the query with the join in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo…=0; mysql> set global innodb_old_blocks_time=0; Query OK, 0 rows affected (0.00 sec) mysql> select straight_join sum(lo_extendedprice…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…speed stays about the same: mysql> select count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql…Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> call test_read(1000); Query…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against… = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City as ci where speaks_english(CountryCode) > 0; +——————–+ | sum…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… times. mysql> load data infile ‘/tmp/words’ into table words (chars); Query OK,…speed is near constant and the single threaded operation burns one CPU. To help completely demonstrate how Shard-Query…– AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… SQL logs in the database so It was pretty simple query: select sum(cnt) from (select count(*) cnt from performance_log_080306… I could trick MySQL to do group by a hash of the page instead of page itself: mysql> select sum(cnt) from (select count(*) cnt from performance_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
…queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing these queries… allocators that should help to speed up allocation,purging of the dirty…with decreasing performance in workloads with MySQL. # Test: POINT_SELECT:throughput, QPS # # Set …
Post: Feature Idea: Finding columns which query needs to access
November 17, 2006 By 16 Comments
… columns query needs to access to provide result set as it gives you ideas if you can use covering indexes to speed… to sum it up, not to mention more complex questions of subselects. It would be great tool for MySQL Performance Optimization if MySQL… clauses such as GROUP BY and ORDER BY. For complex queries especially if they do not refer to the columns with…
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
… response time rather than “all queries“. It is surely good all search queries complete in one seconds but … following simple formula applies wall_time=cpu_time+sum(wait_time)+lost_time The lost time is …the biggest part yet but if we’d speed up Sphinx and MySQL dramatically we would have to go …
