…. sort_buffer_size – recently I worked with case which was running much faster with 32K sort_buffer_size, compared to 32M. The problem was memory allocation – sorting…(10) default NULL, `d` int(11) default NULL, PRIMARY KEY (`id`), KEY `d` (`d`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 select sql_no…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS | 10 | 100 | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5… value. sort_buffer_size was decreased to 256K from 2M. This change should help many small sorts for which allocation of 2M for sort buffer was…=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on…
Comment: MySQL Server Memory Usage
December 20, 2012 By 21 Comments
…= 150M key_buffer = 256M key_buffer_size = 256M myisam_sort_buffer_size = 16M join_buffer_size = 64M read_buffer_size = 16M sort_buffer_size = 8M table_cache = 3600 table_definition_cache = 4096 thread_cache_size = 16K wait_timeout = 120 connect_timeout = 60 tmp_table_size…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… you have decided with database size you need to check if there are any restrictions on Innodb Buffer Size you can use. Typically… have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are…
Comment: MySQL random freezes could be the query cache
March 29, 2009 By 23 Comments
… 0% or very low so somehow its still doing some sort of work. Trying to connect to it via the command… = 15 key_buffer = 16M key_buffer_size = 32M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 64 query_cache_limit = 0 #query_cache_size = 64M query_cache_size…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
… read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort, when reading rows in sorted order… fixed size (basically everything but BLOB/TEXT) MySQL can use read_rnd_buffer to optimize data retrieval – As data is sorted by the key… lucky. The read_rnd_buffer_size is important (optimization works in following conditions): Row pointers are stored in the sort_buffer, not the whole…
Comment: MySQL: what read_buffer_size value is optimal ?
September 21, 2008 By 25 Comments
… buffers to 128 kbyte? I put this in my my.cnf: key_buffer_size = 256M sort_buffer_size = 128K read_buffer_size = 128K read_rnd_buffer_size = 128K join_buffer_size = 128K myisam_sort_buffer_size… to the my.cnf): key_buffer_size 268435456 sort_buffer_size 131064 read_buffer_size 126976 read_rnd_buffer_size 126976 join_buffer_size 126976 myisam_sort_buffer_size 131072 The key_buff shows the proper…
Comment: New Forum Categories: Help Wanted, For Hire
March 5, 2013 By 1 Comment
… = 1024 thread_cache_size = 8 query_cache_size = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb… = 1024 thread_cache_size = 8 query_cache_size = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… have decent myisam_sort_buffer_size and large myisam_max_sort_file_size otherwise recovery may be done by key_cache rather than sort which can take… many tables which are being repaired, each may allocate myisam_sort_buffer_size and MySQL could crash or go swapping. Second – table will…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…-locking key_buffer = 384M max_allowed_packet = 256M table_cache = 512M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache_size = 80 query_cache_size = 32M thread_concurrency…
