… 8 x 200G EBS volumes in software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server…_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1… caused by query cache on write heavy workload. OK, so that was all about the configuration of the EC2 instance and MySQL…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… N is the buffer size. In MySQL 5.6 the buffer size used by MRR can be controlled by the variable read_rnd_buffer…_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; In… and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… possibilities like InnoDB doing read_ahead by noticing the sequential access pattern. BKA is available in both MySQL 5.6 and MariaDB 5.5. You can read more about BKA in MySQL 5.6 here and BKA in MariaDB… > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In-memory workload Now let…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32 bit machine…_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional… from Table where REQUEST_TIME >= ’2012-03-26′ group by field1; 14 rows in set (15 min 54.29 sec) Please help to…
Post: Announcement of Percona XtraDB Cluster 5.5.20 GA release
April 3, 2012 By 10 Comments
…MySQL or Percona Server in the following meaning: Data compatibility. Percona XtraDB Cluster works with databases created in MySQL…. The Galera library is developed by Codership Oy company and Galera …/ Documentation Codership Wiki General Discussion group Launchpad project Bug reports Previous posts…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
… matching some value in the group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet…’re soring by population you would see MAX(Population) in the population column: SELECT City, Population City FROM City GROUP BY Country GROUPORDER BY Population…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… in this case as number of groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by… there is considerable penalty associalted with GROUP BY WITH ROLLUP in MySQL – it is significantly slower than standard group by even though the only thing it…
Post: Wrong GROUP BY makes your queries fragile
September 6, 2006 By 7 Comments
… it, especially as it is connected to MySQL Performance. No it might not affect MySQL Performance per say but it limits our… replaced by SELECT id,login,max(login_time) FROM log GROUP BY id which may be faster especially if GROUP BY is executed via sorting. In this case even though we do not know which value from the group will be…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 43 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This …3 GROUP BY id; +—-+————————-+ | id | GROUP_CONCAT(client_id) | +—-+————————-+ | 3 | 5,6,7 | +—-+————————-+ Handling in PHP: old way: with group…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…PRIMARY’ WHERE K.REFERENCED_TABLE_NAME IS NOT NULL GROUP BY K.CONSTRAINT_NAME; … rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql>…
