mysql server performance tuning mysql-tuning

You can run mysql service with all the default settings unchanged. However, those default settings are suitable for even the modest server. In mysql service runs on a server with modern hardware setup certain parameters than determine the server running environment (e.g. memory) should be change so that mysql service can utilize the power of the server.

By default, mysqld's memory settings are rather conservative and should be increased if possible. There are caches such InnoDB buffer pool which caches data and index information for InnoDB tables.

On the other hand, even the server is powerful. If queries are not optimized, resources may be wasted resulting a limited capacity of the serM
ver.

Tuning Memory Parameters
Memory is important because more memory allows for less swapping be the operating system and for larger buffers improving caching and reducing disk usage.

Typically, you set parameter values using options in the [mysqldd] section of an option file so that the server uses them consistently each time it starts. For system variables that are dynamic, you can change them while the server runs to test how the changes affect performance. After you determine optimum values this way, set them in the option file for use in subsequent server restarts.

You may get ideas of how to tune the parameters by looking at the sample files provided, we mentioned the my-innodb-heavy-4G.ini file, it may not be 100% fit for your system, but you can use it as starting point to save time.

[mysqld]
key_buffer_size=256M
table_cache=256
sort_buffer_size=1M
query_cache_type=ON
query_cache_size=16M

The Table Cache

When the server opens a table, it maintains information about that table in the table cache, which is used to avoidd reopening tables when possible. The next time a client tries to access the table, the server can use it immediately without opening the table again if it is found in the cache.

However, if the cache is full and a client tries to access a table that isn't found there, somee open table must be closed to free an entry in the cache for the new table. The table that is closed then must be reopened the next time a client accesses it.

The table cache is a global cache for all open tables, it is used to cache the status of individual tables.

If you mainly run simple queries on many tables, the table cache should be increased.

The MyISAM Key Cache
The key cache (key buffer) is a resource in which the server caches index blocks that it reads from MyISAM tables. The key_buffer_size system variable controls the size of the key cache.

Key buffer is a glbal buffer, it caches index blocks for MyISAM tables only.

You can create additional key caches and assign specific tables to them If a table is heavily used and you want to make sure that its inddex information never is displaced ffrom the cache by indexes from other tables, create a separate cache and dedicate it for use by the table.

set global city_cache.key_buffer_size=4194304;
cache index word.city in city_ache;
load Index into cache world.city; 

Query Cache

Mysql supports a query cache that greatly increases performance under condtions such that the server's query mix include  select statements that processed repeatedly and return the same results each time. Using the query cache can result in the tremendous performance boost and reduction in the server load, especially for disk-or-processorr-intensive queries;

The query cache is global , so a query result placed in the cache can be returned to any client that has the necessary privileges for the tables referred to by the query.


Show variables like 'query_cache%';

Configuring Query Cache

my-opts.cnf

[mysqld]
innodb_file_per_table
federated
log-output=FILE,TABLE
log-slow-queries=sysslow.log
log-bin=sysbinary
sys_binlog=1
query_cache_type=1
query_cache_size=20M
query_cache_limit=2M


Modify query cacche value during Mysql runtime

set global query_cache_type=on;
set global query_cache_size=10485760;
set global query_cache_limit=1048576;

Turn on / off query cache

set session query_cache_type=on;

set session query_cache_type=off;

Measuring Query Cache Unilization

Show status like 'Qcache%';

Sort Buffer

The sort buffer is used ffor operations such as order by and group by.

sort_buffer_size

Join Buffer

join_buffer_size

Record Buffer

It is used to perform sequential table scans, reducing the number of reads from disk.

read_buffer_size

The Explain Statement

Explain select * from sample.employees;

Explain produces serveral columns of information. In the example just shown, null is the possible_keys and key columns shows for the first query that no index is considered available or usuable for processing the query.

For the second query, the table's PRIMARY KEY column (the Code column that contains three-letter county codes) can be used , and is in fact the index that the optimizer would choose. The rows column of the Explain output shows the effect of this difference.

The ROWS column indicates the total rows to be read in the EXPLAIN SELECT statement.

For the first query, the value is 240, which happens to be the number of rows in the Country table. In other words, My/sql wouldd scan all rows of the table, which is inefficient.

For the second query, only one row need be examined. This is because MySQL can use the table's primary key to go directly to the siingle relevant row.

As a result, we can conclude that we should use the code column rather that the country column of possible to look up Country tablee records.

EXPLAIN  is useful even for simple statement of this kindd. However, the real power of  EXPLAIN lies in which it can tell you about joins, which may selects multiple tables.

It is important to analyze queries that involve join with EXPLAIN because join havve the potential to cause more server processing if not defined correctly.

Analyzing Query

The following example demonstrates how to use EXPLAIN to analyze and optimize a sample query. The purpose of the query is a answer the querstion. Which cities have a population of more that 8 million and to display for each city its name and population, along with the country name.

This question could be answered using only city information, exceipt that to get each country's name reather that its ccode, city information must be joined to country information.

The example uses tables created from world database information. Initially, these tables will have no indexes, so EXPLAIN will show that the query is not optimal. The example then adds index and uses EXPLAIN to determine the effect of indexing on query performance.

Select countrylist.name , citylist.name, citylist.population from countrylist, citylist
where countrylist.code=citylist.coutry.code
and citylist.population > 800000;

For Country List, the value of ALL indicates a full scan of all rows. For CityList, the value of ALL indicates a scan of all its rows to find a match for each CountryList row. In other words , all combinations of rows will be checked to find country code matches between the two tables.

The number of row combinations is given by the product of the rows values, where rows represents the optimizer's estimate of how many rows in a table it will need to check at each stage of the join, In this case, the product is 240 x 4079 or 978980.

When Using  EXPLAIN on jining statements the total rows to be examined will be the product of row value in the EXPLAIN result.

EXPLAIN shows that MySQL would need to check nearly a million row combinations to produce a query result that contains only 10 rows. Clearly , this query would benefit from the creation of indexes that allow the server to look up infromation faster.

Alter table countryList add primary key (code) ;
alter table citylist add index(countryCode);

The possible_keys in the EXPLAIN SELECT shows if there are any indexes you maybe able to  use. When possible_keys column values is NULL , no index is considered available for this query.

When the Key values is NULL, no index could be used to perform the query, the number show next to rows represents the number of rows the table need to examined. You should index the column which being specified in a WHERE clause frequently.

If the value of the type column in a EXPLAIN is eq_ref, it means that the equality test is perfromed by referring to the column named in the ref field.

The ordering of tables shown in an EXPLAIN output determines the table from which data will read first.

The id value of EXPLAIN is just an incremental number to identify the rows from the output.

ref indicates which indexed column or columns are used to choose rows fromm the table.

The key_len represents the bytes will be used from index rows.

The result from EXPLAIN shows that indexing CountryList. Code as a primary key improves query performance. However, it still indicates a full scan of the CityList table.

The product of the rows now is just 4079. That's much better than 978960, but perhaps further improvement is possible. The WHERE clause of the query restricts CityList rows based on their population values, so try creating an index on that column.

Alter table citylist add index (population) ;

The query now is optimized. Note that the product of the rows values 11. To calculate the significant of the performance gain.

To hint the optimizer to use a particular index you can include the USE INDEX or FORCE INDEX in the EXPLAIN statement:

EXPLAIN SELECT * FROM table_name USE INDEX (INDEX_NAME) WHERE CONDITION

If you want to force MySQL to join table in a particular order, being the query with SELECT STRAIGNT_JOIN rather than SELECT , and then list the tables in the desired order in the FROM clause.

The optimizer does not need to read the data rows, and can return values from the index if those columns where specified in the SELECT statement.