Server configuration and optimization is definitely not your job if you are not a DBA. You can’t just set the server parameters to some arbitrary values expecting a nice performance boost, because it depends on different factors. It needs good understanding over hardware, operating system and server configuration. Any way I believe every programmer must have a basic understanding of the database server to code better. All DBMS provide a mechanism to store the data in memory called caching to improve the performance. From the programmers perspective we are mainly concerned of whether our queries and data are cached or not. For this we should know when the server will (or not) cache query and data.
Understanding the cache and buffers
Generally speaking there are three different caches in all database systems (with their own names) which face most of the memory demands.
- Metadata cache
- Code cache
- Data cache
Metadata cache
All DBMS have a metadata system to store the system status or internal informations, like information schema in MySql and data dictionary in oracle. These data repositories will be constantly updated when our database is queried or updated. All database systems possess metadata cache to hold system reference data in memory. In MySql it is named as table cache where as in oracle it is named as dictionary cache.
Code cache
Database server need to parse queries for syntax, security and error checking before they are actually executed. Then our server generates an execution plan. This is called “hard parsing” which is a costly process. The function of code cache is to store the result of hard parsing, so that whenever an identical query is executed again it will read from the cache (soft parsing) instead of hard parsing. The amount of cpu saving by doing so is not small. Query must be exactly the same to read from the cache. For example “select * from table” and “Select * from table” are considered as different queries.
If the result is returned from the cache, server increments a status variable named ‘Qcache_hits’. If the data in table is changed using DML or DDL statements server invalidates the cached result and remove it. Our database server won’t always store the queries in cache, especially when the query contain any of the non deterministic functions like SYSDATE(), CURDATE(), NOW(), RAND(), LAST_INSERT_ID(), UNIX_TIMESTAMP() with no argument etc. or it refers to user defined functions or stored program variables. We can force MySql to cache query using the hint SQL_CACHE in the statement. Query results will be cached if it is cacheable and the value of query_cache_type” system variable is ON or DEMAND.
Data cache
MySql support multiple storage engines and thus different types of caches too. For MyISAM storage engine it is termed as ‘Key cache’ and this can store index blocks in memory whereas InnoDB provides a mechanism named “Buffer cache” (just like oracle). This can hold index blocks as well as data blocks in memory. To minimize disk IO MyISAM employs a mechanism to keep the most frequently accessed datablocks in memory. This memory area is shared among all sessions. We can create multiple key caches and tables can be assigned to them.
Set GLOBAL my_cache.key_buffer_size = 64*1024;
Cache index table1 in my_cache;
The first statement will create new cache area of size 10kb and the second will assign the indexes from table1 to my_cache. To destroy this cache you can set its value to zero.
InnoDB maintains a buffer pool for caching data and indexes in memory. InnoDB manages the pool as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables.
There are a number of ratios to indicate the cache efficiency, like buffer pool hit ratio. Value just above 90% is considered as efficient. If its more than 95% it’s a bad indication…..!. Your server may not be caching effectively. This value indicates that most of the time, our memory is busy with serving data which is not a good habit.
Important system variables relate to cache
System variables can be classified into static and dynamic variables. Those variables that can be changed in runtime are called dynamic. They may have Global (Common for all sessions) or Session scope or both. Most of the variables are dynamic and can be set with global or session scope.
key_buffer_size – is the size of buffer used for index blocks. You can increase the size of this variable to get better index handling for reads and multiple writes. Its maximum limit is 4GB in 32 bit platform. It should be noted that this value defines only the maximum limit. The actual usage can be lesser than this value. 20-25% of the total memory is an acceptable value for this variable.
table_cache – is the number of open tables from all threads (connections). Increasing this value will increase the file descriptors that MySql needs. You can check whether you need to increase the table cache by checking the Opened_tables status variable.
sort_buffer_size - Each session that needs to do a sort allocates a buffer of this size.
read_buffer_size - Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential reads you may need to increase the value of this parameter. The value must be a multiple of 4kb.
query_cache_type – This variable enables query cache. 0(OFF),1(ON) and 2(DEMAND) are the possible values. If its value is set to 2 MySql will cache query if we issue select query along with SQL_CACHE hint.
query_cache_size – This variable sets the size of query cache. It need at least 40kb to allocate the data structure and the value must be multiple of 1024.
Some useful tips
To change the value of a variable you can use the statement
SET [SESSION] @@Key_buffer_size = 1M;
You can restore its default value by assigning to DEFAULT.
To see the value of a variable
SHOW variables like ‘variable_name’;
To remove the contents in query cache you can use ‘RESET QUERY CACHE’ statement.
To remove the content in date cache you can use ‘FLUSH TABLES’ statement.
To view the count of query cache use the statment 'SHOW STATUS LIKE 'Qcache_hits'
Enable slow query log to find the cpu intensive queries.
Use 'SHOW FULL PROCESSLIST' to view all queries that are currently running.
MySQL Workbench is an excellent tool for server administration, MySQL programming as well as database designing, which can be downloaded from here.