… the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or… buffer sizes. set sort_buffer_size=1000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (0.70 sec) mysql… sort_buffer_size ? mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order by i desc…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
…queries. ICE supports almost all of the MySQL aggregation functions. Notably, GROUP_CONCAT is not supported, which …higher than it should have been, and several GROUP BY queries returned unexpected results as well. I did…to them, but this is difficult given the size of the data set involved. ICE was …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…lru where table_name like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc; +——————-+——————+——-+——+——+———+—————–+ | table_name … rows in set (0.00 sec) mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_pool …
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… cache to keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 2…) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt group by c order by…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…. I inflated the document size significantly to over 3 million “words” by duplicating the entire set multiple times. mysql> load data infile ‘/tmp… run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple… (date_id) GROUP BY Year) t2 ON (t.Year=t2.Year); The performance of the following queries depends on the size of the… BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping and filtering is…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… of data sizes so here it goes: Find total number of tables, rows, total data in index size for given MySQL Instance SELECT…),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data…),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can be controlled by the variable read_rnd_buffer_size, while MariaDB… = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…’ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In… MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL… counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read_rnd_buffer_size=6M MariaDB 5.5…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…by 0x6B66F3B: log_init (mem0mem.ic:443) ==9090== by 0x6BC63B0: innobase_start_or_create_for_mysql (srv0start.c:1339) ==9090== by…with a previous read of size 8 by thread #14 ==9090…by 0x6B35B5E: fil_io (fil0fil.c:4432) ==9090== by 0x6B67EDA: log_group_write_buf (log0log.c:1290) ==9090== by…
