---
author: Olivier De Ram
date: 2018-08-25T22:08:15+02:00
draft: false
title: MySQL Tuner
---

## MySQL Tuner ##

|         WAARDE | ACTIE
------------- | -------------
`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_*`:


`query_cache_type`  
Needs to be set to 1 to enable caching.  
`query_cache_size`  
Is the size of the cache. This can be in bytes, or you can use a M suffix to specify the amount of megabytes.  
`query_cache_limit`  
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.  
`table_open_cache`  
Indicates the maximum number of tables the server keeps open  

________
`innodb_buffer_pool_instances=2`  
_Enables the use of multiple threads for innodb._  

`query_cache_type=1`  
_Enables query caching._  

`join_buffer_size=1024K`  
_Increased the buffer size for non-indexed joins._  

`tmp_table_size=64M && max_heap_table_size=64M`  
_Increased the size for temporary tables._

`join_buffer_size`  
_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._

`thread_cache_size`  
_Het maximale aantal threads dat de server kan cachen voor hergebruik werd verhoogd._