…to see the difference in numbers. Benchmark results For the purpose of this benchmark, …optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5…, the InnoDB buffer pool size is set to 6G and the buffer pool … 0, which means that the buffer was big enough and only 1 MRR range …
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 10 Comments
…’t it?) mysql> pager less mysql> show innodb status\G For big result sets, it’s a pretty handy way to be able to… are any lock waits: mysql> pager /tmp/lock_waits PAGER set to ‘/tmp/lock_waits’ mysql> show innodb status\G ——- TRX HAS…-and-filter manner. What sort of goodies can we think of here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… set (21.30 sec) We even can got read of sorting and add ORDER BY NULL so MySQL does not bother to sort results… | +——+———+ 11 rows in set (29.68 sec) Use of extra temporary table for buffering helps us to get result set we’re looking… 1 row in set (0.00 sec) As I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… you set sort_buffer_size to 128K — I can quite well connect and say “set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size is now 1GB. And a single query may cause multiple sort… the Big Ones like the buffer pool, query cache etc; leave some memory for other needs; and see what happens. The results…
Post: New SpecJAppServer results at MySQL and Sun.
November 12, 2008 By Leave a Comment
…SpecJAppServer Results More information from Tom Daly can be found here These results … setting for many workloads so unless you have repeatable reads requirement consider using this. sort…sort buffer because allocating it will likely be faster. query_cache_size = 0M – query cache disabled. Not a big…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… mandatory and it’s a range, so sorting sure will be problem. Especially when result set is big. At least it will be filesort and not necessary temporary table as we select from one table. To solve sorting issue on big sets, people can use pre-explain. Then decide either use…
Post: MySQL Session variables and Hints
December 28, 2006 By 2 Comments
… of particular query. First is MySQL Hints, such as SQL_BIG_RESULT, STRAIGHT_JOIN, FORCE INDEX etc. You place these directly into… is going to require large sort you can do SET sort_buffer_size=50000000 before executing query and SET sort_buffer_size=DEFAULT after executing the query. I noticed in production hints are used much more frequently than setting session…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… in set (29 min 53.01 sec) Also remember – not all indexes are created equal. Some indexes may be placed in sorted… first and then accessing rows in sorted order can be a lot of help for big scans. This will reduce the gap… aggregate result sets. So if you’re dealing with large data sets and complex queries here are few tips Try to fit data set…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… as well as cold provide quite unexpected improvements. sort_buffer_size – recently I worked with case which … count(*) from t1 where c in (select sql_big_result count(*) from t1 where d=50 group by c …should we go ahead and set them to 64M ? Not really – this could result in nasty suprises with full …
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… long hours (this is indeed one of the big reasons to use Innodb even if you do…however results in the challenge as results you’ve gotten for small data set may not apply to large data set … BTREE (even MyISAM which builds “normal” indexes by sort builds primary key and unique indexes using keycache) …
