… start suffering from the trx_list overhead created by concurrent updates. Once we step away from this… innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read…-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…$I_r100, sbtest$I_r1000, with roll-up sum for 10, 100, 1000 records in the main…ID=monotonically increasing ID $K=rand(0,10000) // distributed by pareto distribution $C, $PAD = random_string() BEGIN …innodb_log_file_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096 #####plugin options…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… range [0, 255] (inclusive). Quorum is computed using weighted sum over group members. Percona XtraDB Cluster binary will now be bundled with… Jaakola). Debian packages included the old version of innotop. Fixed by removing innotop and its InnoDBParser Perl package from source and…, this caused the SST failure due to insufficient privileges. Fixed by excluding lost+found folder if found. Bug fixed #1154095 (Alex…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values on… as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by… forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same time as GROUP BY WITH ROLLUP.
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… 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) | +———-+ | 1127031…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
Hi, Here is an easy way to run the subset sum check from SQL, which you can then distribute with Shard… d WHERE val in (-2,-3,-10,15,15,16) GROUP BY val; +—–+———-+———-+ | val | cd | CNT | +—–+———-+———-+ | -10 | 1 | 1 | | -3 | 1 | 1… numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count(*) | +—–+———-+ | -10 | 1 | | -3 | 1 | | -2 | 1 | | 0…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…’ and s_region = ‘AMERICA’ group by d_year, p_brand order by d_year, p_brand; — Q2.2 select sum(lo_revenue), d_year…’ and s_region = ‘ASIA’ group by d_year, p_brand order by d_year, p_brand; — Q2.3 select sum(lo_revenue), d_year… = ‘EUROPE’ group by d_year, p_brand order by d_year, p_brand; — Q3.1 select c_nation, s_nation, d_year, sum(lo…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by customer_id ‘> order by total_price…) -> JOIN demo.order_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+ | customer_id | total…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum(DepDelay >= 0) flight_delayed` FROM `aggregation_tmp_74082863` GROUP BY 1…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…’) total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length…),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length…
