5th April, 2017
Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want your system to become, the more you must know about it. 14.1 Optimization Overview The most important factor in making a system fast is its basic design. You must also know what kinds of processing your system is doing, and what its bottlenecks are. In most cases, system bottlenecks arise from these sources: Disk seeks: It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk. Disk reading and writing: When the disk is at the correct position, we need to read the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks. CPU cycles: When we have the data in main memory, we need to process it to get our result. Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem. 14.2 Obtaining Query Execution Plan Information 14.2.1 Optimizing Queries with EXPLAIN The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a SELECT statement or as a synonym for DESCRIBE.
If you have a problem with indexes not being used when you believe that they should be, you should run ANALYZE TABLE to update table statistics such as cardinality of keys, that can affect the choices the optimizer makes. EXPLAIN Output Format: EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table. Example: mysql> EXPLAIN SELECT * FROM tbl_a a, tbl_b b where a.id = b.id; +----+---------------+-------+-----------+-------------------+--------------+------------+--------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+---------------+-------+-----------+-------------------+--------------+------------+--------+------+-------+ | 1 | SIMPLE | b | system | PRIMARY | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 8 | const | 1 | | +----+---------------+-------+-----------+-------------------+--------------+------------+---------+------+-------+ 14.2.2 Optimizing SELECT Statements First, one factor affects all statements: The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you do not grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv and columns_priv tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high statement-processing load, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead. If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is Syntax: mysql > BENCHMARK(loop_count,expression); The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example: mysql> SELECT BENCHMARK(1000000,1+1); +---------------------------------------+ | BENCHMARK(1000000,1+1) | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.32 sec) It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system. Speed of SELECT Statements: In general, when you want to make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. You can use the EXPLAIN statement to determine which indexes are used for a SELECT. Some general tips for speeding up queries on MyISAM tables:
WHERE Clause Optimization: This section discusses optimizations that can be made for processing WHERE clauses. The examples use SELECT statements, but the same optimizations apply for WHERE clauses in DELETE and UPDATE statements.
((a AND b) AND c OR (((a AND b) AND (c AND d)))) => (a AND b AND c) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5 => b>5 AND b=c AND a=5 In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.
14.3. Tuning Server Parameters For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement: mysql> SHOW VARIABLES; You can also see some statistical and status indicators for a running server by issuing this statement: mysql> SHOW STATUS; System variable and status information also can be obtained using mysqladmin: shell> mysqladmin variables shell> mysqladmin extended-status When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_ open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables. If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations. Temp variables: You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables. Server variable : tmp_table_size (Need to be increased depending upon the below values) Status variables : Created_tmp_disk_tables, Created_tmp_tables. Query Cache: The cache is not used for queries of the following types:
A query cannot be cached if it contains any of the functions like CURDATE(), NOW(), CONVERT_TZ(), CURTIME(), DATABASE(), RAND(), UUID() etc. A query also is not cached under these conditions:
Two query cache-related options may be specified in SELECT statements: SQL_CACHE The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND. SQL_NO_CACHE The query result is not cached. Examples: SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;
The have_query_cache server system variable indicates whether the query cache is available: mysql> SHOW VARIABLES LIKE 'have_query_cache'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | have_query_cache | YES | +--------------------------+--------+ To set the size of the query cache, set the query_cache_size system variable. Setting it to 0 disables the query cache. The default size is 0, so the query cache is disabled by default. If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:
Individual clients can control cache behavior for their own connection by setting the SESSION query_cache_type value. For example, a client can disable use of the query cache for its own queries like this: mysql> SET SESSION query_cache_type = 0; When a query is to be cached, its result (the data sent to the client) is stored in the query cache during result retrieval. Therefore the data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable. When a query is executed, the last result block is trimmed to the actual data size so that unused memory is freed. Depending on the types of queries your server executes, you might find it helpful to tune the value of query_cache_min_res_unit:
The RESET QUERY CACHE statement, to removes all query results from the query cache execute the FLUSH TABLES statement. Status variables: Qcache_free_blocks The number of free memory blocks in the query cache. qcache_hits Whenever MySQL performs a SELECT operation, it either increments com_select or the qcache_hits status variables. com_selects thus show us the cache misses. So get the hit ratio by this formula: qcache_hits / (qcache_hits + com_select) which gives us .9999 or 99.99%. Qcache_inserts The number of queries added to the query cache. Qcache_lowmem_prunes The number of queries that were deleted from the query cache because of low memory. Qcache_not_cached The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting). The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache. Other Optimizations: sort_buffer_size If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing. The entire buffer is allocated even if it is not all needed, so setting it larger than required globally will slow down most queries that sort. myisam_sort_buffer_size The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. thread_cache_size How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. wait_timeout The number of seconds the server waits for activity on a noninteractive connection before closing it.
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .
Write For Us