…really really matters. High-Memory Quadruple Extra Large Instance 68.4 GB of memory …log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive…data-set on the insert rate. We can see that adding the secondary indexes to the table has decreased the insert rate by…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…perform the lookup on t2 by a secondary key on the common join …’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, … InnoDB buffer pool size is set to 6G and the buffer …data is read from disk 2.2G vs 5G. However, there is one number in MariaDB 5.5 that is quite large…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…optimizer_switch=’mrr=on‘ optimizer_switch=’mrr_sort_keys=on‘ (only on MariaDB 5.5…group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by… 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 … reduced further when buffer size is set large enough so that the index …
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… see I’m using ORDER BY NULL clause as otherwise MySQL will sort the data after performing group by which is overhead we’re… I filled bug on this. So what was performance mysql> select count(*) cnt,c from gt group by c order by null limit 10… be true as I’ve seen significantly worse performance for large data sets. So I decided to see what happens if I create…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… it handles large data sets as part of the process we work on generation test database of significant size as testing your application on table… had was creating of summary tables which was done by executing some huge group by query, doing some stuff with results and then…” in EXPLAIN) this was not issue for given case as GROUP BY in question required temporary table plus it was test system…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…. Here are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY cnt DESC limit 10, the query… needs to traverse a lot of rows to perform group by so for large number of rows you’ve simply got to use… nutshell ORDER BY LIMIT can be very fast… if it is done using Index or it can be slow on large data sets if external…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… visited during that day) which resulted in on disk temporary table which as we know …_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031 | +———-+ 1 row in set (31.22… when I want to analyze data distribution but table is to large is to just limit …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…) performs in combination with Shard-Query. Data set It was important to choose a data set that was large enough to create queries that would… is possible and very large data sets may be processed. As each machine examines only a small subset of the data, performance can be…>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) GROUP BY Year) t2 ON…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… testing on a large data set against two MySQL column-store storage engines. I’d like to note that this effort was sponsored by Infobright… team should insist on. The second part of my analysis focused on testing a total of 29 queries on the large data set. I compared a…_fact” when run on InfiniDB returned a number that was higher than it should have been, and several GROUP BY queries returned unexpected…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… MySQL Group by Performance Tests…set faster than that. I placed temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set…on theoretical expectations rather than supported by large amount of testing. sort_merge_passes are not that bad. Setting your sort_buffer_size large…
