Recently i have briefed few possibilities to increase MySQL response time and overall performance, so let’s talk how we can tweak configuration file of MySQL to bridge the gap between blazingly fast CPUs and comparatively slow disks, certainly “the memory”. By adjusting how much memory MySQL uses, you can often realize significant performance improvements, but before that we need to understand how MySQL uses memory. As MySQL is extensively tested for any memory leakage, we can focus our concern to any miss configuration and hence miss utilization of memory. MySQL provides different default configuration file that you can configure further, according to your server hardware and traffic.
After you compile MySQL you need to place your configuration files to /etc/my.cnf from installation directory some of the default config files that MySQL supplies are
- my-huge.cnf, is recommended for systems that have at least 1GB memory, and run mainly MySQL
- my-large.cnf, is for systems with slightly less memory (512MB), and also mainly running MySQL.
- my-medium.cnf, is tweaked for a system where MySQL and a Web server are running together with around 128MB, or around 64MB with MySQL alone.
- my-small.cnf, running on a system with less than 64MB, where MySQL can’t hog too many of the resources.
Besides the recommendations, the performance of MySQL totally depends on how you have configured it. So let’s see how mysql handles memory :
let’s see how MySQL uses memory, the article related to this topic on net are too heavy and are too geeky , i tried to pick most of them and divided into small chunks of information that is more likely easy to grasp:
- The key buffer (variable key_buffer_size) is shared by all threads; other buffers used by the server are allocated as needed(mostly at run time).
- Each connection uses some thread-specific space:
- A stack (default 64KB, variable thread_stack)
- A connection buffer (variable net_buffer_length)
- A result buffer (variable net_buffer_length)
- The connection buffer and result buffer are dynamically enlarged up to max_allowed_packet when needed. While a query is running, a copy of the current query string is also allocated.
- All threads share the same base memory.
- Only compressed ISAM and MyISAM tables are memory mapped. This is because the 32-bit memory space for GB’s of RAM is not large enough for most big tables.
- Each request that performs a sequential scan of a table allocates a read buffer (variable read_buffer_size).
- When reading rows in “random” order (for example, after a sort), a random-read buffer may be allocated to avoid disk seeks. (variable read_rnd_buffer_size).
- All joins are done in one pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based (HEAP) tables.
- Temporary tables with a large record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.
- If an internal in-memory heap table exceeds the size of tmp_table_size, the error The table tbl_name is full occurs, this is now handled automatically by changing the in-memory heap table to a disk-based MyISAM table as necessary.
- To work around table tbl_name is full problem for older servers, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option SQL_BIG_TABLES in the client program.
- Most requests that perform a sort, allocate a sort buffer and zero to two temporary files depending on the result set size.
- Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items, so the normal slow memory allocation and freeing is avoided.
- Memory is allocated only for unexpectedly large strings; this is done with malloc() and free().
- For each MyISAM and ISAM table that is opened, the index file is opened once and the data file is opened once for each concurrently running thread.
- For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * N are allocated (where N is the maximum row length, not counting BLOB columns).
- A BLOB column requires five to eight bytes plus the length of the BLOB data. The MyISAM and ISAM storage engines maintain one extra row buffer for internal use.
- For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.
- Handler structures for all in-use tables are saved in a cache and managed as a FIFO(first in first out). By default, the cache has 64 entries. If a table has been used by two running threads at the same time, the cache contains two entries for the table.
