…baseline HDD configuration for MySQL (configuration A, …buffer_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_log_file_size = 1G innodb_log_files_in_group…buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…x86_64/lib/mysql/libjemalloc.so [mysqld] gdb datadir=/mnt/data/mysql #for SSD innodb_…_buffer_pool_size = 36G innodb_log_file_size = 4G innodb_log_files_in_group …buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… UNIQUE the drawback is insert buffer will not be able to be used, which can be important for large, heavily written tables…: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL gets data in already sorted order…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…sort_buffer_sizeblogpercona.commanual Sort_merge_passesblogpercona.commanual Sort_rangeblogpercona.commanual Sort_rowsblogpercona.commanual Sort…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…sort_buffer allowed when a lot of sort merge passes are required for sort completion: mysql> show status like “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes | 321 | | Sort…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size is now 1GB. And a single query may cause multiple sort buffers… many of those per query. Sorting and grouping and DISTINCT and UNION may/…for user locks! I set my max_allowed_packet bigger and ran the following on my laptop: mysql…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. For the tests I…for sort MySQL will be a lot faster. First – Sorting. Sphinx is smart doing sorting because it does not try to sort…groups while MySQL reports NULL group as a result. SphinxQL at this point is rather picky – it wants AS for…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… MySQL does not bother to sort results, though this has a little difference in this case as number of groups is relatively small: mysql… sec) Use of extra temporary table for buffering helps us to get result set we’re looking for and workaround this limitation, though… execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same time as GROUP BY WITH…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ … ’1995-03-09′ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order …MySQL 5.6 (2) is for MySQL 5.6 w/ buffer size 6M MariaDB 5.5 (2) is for MariaDB 5.5 w/ buffer…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… (…) As you can see by utilizing the buffer for sorting the secondary key tuples by pk_column, …sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL… l_returnflag = ‘R’ and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c…
