Dexter Mysql tunning

@start "MySQL" /B "%DB_HOME%\bin\mysqld-nt" --standalone --lower-case-table-names=1 --basedir="%DB_HOME%" --port=%DB_PORT% --datadir="%DB_HOME%\data" --default-character-set=utf8 --set-variable=query_cache_type=2 --read_buffer_size=128K --read_rnd_buffer_size=2M --sort_buffer_size=4M --myisam_sort_buffer_size=2M --tmp_table_size=32M --max_heap_table_size=32M --key_buffer_size=8M --innodb_buffer_pool_size=128M --bulk_insert_buffer_size=8M --table_cache=256 --thread_cache=32  --innodb_flush_log_at_trx_commit=0 --low-priority-updates --log-slow-queries --long_query_time=5

 

--query_cache_type : defines whether to cache query results

                             0 - no caching

                             1 - always

                             2 - on demand

 

--read_buffer_size : each user thread use data blocks of this size for read operation.

                            size = 128K - 1M

                            can be increased to a higher value but testing required,

                            increasing might not always gives a positive impact

 

--read_rnd_buffer_size : used by user thread to read data after a sort operation

                                size = 2M - 10M

 

--sort_buffer_size : each thread that needs to sort, allocates a buffer of this size

                         size = 4M - 16M

 

--myisam_sort_buffer_size : buffer size allocated for sorting MyISAM indexes

                                     We use InnoDB

 

--tmp_table_size : Maximum size of a temporary table created.

                        Temporary tables needs to be created during sorting if data set is huge and no indexes can be used

                        size = 8M - 64M

 

--max_heap_table_size : Maximum size of an in-memory table. Generally equal to tmp_table_size

 

--key_buffer_size : Buffer size for storing MyISAM indexes

 

--innodb_buffer_pool_size : Buffer size for storing InnoDB indexes

                                    size = 128M - 50% to 75% of RAM

 

--bulk_insert_buffer_size : Buffer used during bulk insert

                                   will be useful during backup and restore

                                   size = 8M - 16M

 

--table_cache : Opening tables takes time and resources. All indexes realted to the table has to be loaded into the memory. This varibles sets the number of tables to be kept open for further use. Same table opened by two user threads requires two handles.

                    count = 256 - 2048

 

--thread_cache : Creation of thread takes time. No of threads to be pooled for further use.

                       count = 16 - 64

 

--innodb_flush_log_at_trx_commit : determines how redo log buffers are written to the log file

                                               0 - every second

                                               1 - default, on each commit and flush to disk

                                               2 - on each commit, no flush to disk

 

--low-priority-updates : updates have lower priority then read. so an update is blocked until there is a select statement for that table

 

--log-slow-queries --long_query_time=5 : This is analysis only. This will log all queries which take time more then the given seconds (second parameter)