MySQL Tuner

MySQL Tuner

query_cache_size AFBLIJVEN
table_cache AFBLIJVEN (maximumwaarde = 64)
table_open_cache AFBLIJVEN
join_buffer_size Verhogen indien Joins performed without indexes++
tmp_table_size Verhogen = max_heap_table_size
max_heap_table_size Verhogen = tmp_table_size
query_cache_type =1 indien =0

mysql tuning improvements table_cache NOOIT hoger dan 64 Sudo vim /etc/my.cnf query_cache_size’ => ‘256M’, It caches the select query along with the result set, which enables the identical selects to execute faster as the data fetches from the in memory. Caching voor select queries en bijhorende result sets, wat het mogelijk maakt om identieke selects sneller op te vragen uit memory. ‘open_files_limit’ => ‘4096’, Changes the number of file descriptors available to mysqld. You should try increasing the value of this option if mysqld gives you the error Too many open files. ‘join_buffer_size’ => ‘256K’, The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. Minimumgrootte van buffer voor index en table scans.

‘max_heap_table_size’ => ‘32M’, This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. Max grootte van user-created memory tables These 2 need to be the same size!!! ‘tmp_table_size’ => ‘32M’, The maximum size of internal in-memory temporary tables. Max grootte van interne in-memory tijdelijke tabellen

table_cache=64 (maximumwaarde!!) Table_Cache should always - well mostly anyway - be significantly bigger than the total number of tables in the server. Otherwise it’ll keep opening and closing tables. Maximumwaarde voor caching van geopende tabellen. thread_cache_size=4 How many threads the server should cache for reuse. Aantal threads dat de server kan cachen voor hergebruik. ‘innodb_buffer_pool_size’ => ‘1G’, The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB.

Sudo service mysql/mariadb reload (restart enkel onder toezicht)

  • query_cache_*:

Needs to be set to 1 to enable caching.
Is the size of the cache. This can be in bytes, or you can use a M suffix to specify the amount of megabytes.
Is the maximum size of an individually cached query. Queries over this size won’t go into the cache. This is also in bytes, or megabytes with the M suffix. 1MB is a safe bet.
Maximumgrootte voor elke individuele gecachte query. Queries groter dan dit zullen niet gecacht worden.
Indicates the maximum number of tables the server keeps open

Enables the use of multiple threads for innodb.

Enables query caching.

Increased the buffer size for non-indexed joins.

tmp_table_size=64M && max_heap_table_size=64M
Increased the size for temporary tables.

Omwille van het aantal JOIN queries uitgevoerd zonder indexes, werd de minimumgrootte van de buffer voor index en table scans verhoogd.

max_heap_table_size & tmp_table_size
De maximale grootte van user-created memory tables en van interne in-memory tijdelijke tabellen werd verhoogd.

Het maximale aantal threads dat de server kan cachen voor hergebruik werd verhoogd.

More Reading
Newer// Let's Encrypt
comments powered by Disqus