…10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22…innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_… let’s come down to the interesting part. Results With the table purchases_noindex, that has no…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…the benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have … ’1995-03-09′ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order … at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… benchmarks, to see the difference in numbers. Benchmark results For the purpose of this benchmark, I have … and l_returnflag = ‘R’ and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_… bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
….3 and MySQL 5.6 optimizer improvements, and both of them look very good indeed. Peter will present the results in an “optimizer standoff” talk at the upcoming MySQL conference, and Timour and Sergei from… group commit fix, and MySQL 5.6 offers microsecond timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… of groups is much larger). The other idea came to my mind is using GROUP BY WITH ROLLUP so I can get grouped result set together with total value for the groups: mysql> select grp, count(*) cnt from dt where slack like “a%” group by…
Post: Testing the Group Commit Fix
July 13, 2011 By 13 Comments
… main branch yet), and here are the results of my testing of the new Group Commit in Percona Server 5.1. As… Server 5.1.57 with the Group Commit Fix applied. The first graph shows the results when we have a slow fsync… there is also result to compare binary logs enabled and disabled. All results are for binaries with group commit fix. Results with disabled write…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… which was done by executing some huge group by query, doing some stuff with results and then populating tables. This all worked… crazy. Why ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not… similar behavior. This call users underlying mysql_use_result API call which does not store all result set in memory but instead streams…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
….A,Foo.B) WHERE Foo.A IS NULL; If the result set of this query is empty, then there are no… an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN… result set, it shows us which rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But…commanual defaults_extra_fileblogpercona.commanual defaults_fileblogpercona.commanual defaults_group_suffixblogpercona.commanual delay_key_writeblogpercona.commanual Delayed_errorsblogpercona….
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… and speed of loading Accuracy of results of queries over the large data set Speed of results of queries over the large… of the queries. ICE supports almost all of the MySQL aggregation functions. Notably, GROUP_CONCAT is not supported, which is something I… higher than it should have been, and several GROUP BY queries returned unexpected results as well. I did not change any “out…
