No time to talk about how to create tables optimized to give better results. I remember only that the fields indexed improve much the execution time. Talk instead of some little trick to make it more performant that MySQL is usually installed on most distro without too many parameters for tuning. The info refer a Linux distro but can be easily adapted to other OS.
The configuration file is usually located in / etc / my.ini.
Eempio of my.ini:
[mysqld]
datadir = / var / lib / mysql
socket = / var / lib / mysql / mysql.sock
set-variable = key_buffer = 64M
set-variable = max_user_connections = 100
set-variable = query_cache_size = 64M
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package).
old_passwords = 1
[mysql.server]
user = mysql
basedir = / var / lib
[mysqld_safe]
err-log = / var / log / mysqld.log
pid-file = / var / run / mysqld / mysqld.pid
buffers: the buffer of any incremeto will get a database much faster. Then set the buffers based on the amount of memory available, taking into account the consumption made by the operating system and other applications and services in use. If you allocate too much memory while not having a provision may run into overall performance of the system.
Wait_timeout: represents the time that elapses before it is closed automatically connect to MySQL due to inactivity. Lower number is better performance. In this case you must also play the kind of connections and their alleged time to calibrate the best parameter.
back_log: Controls how many simultaneous connections it can sustain while Mysql creates new thread. The lower the number, the higher the speed. Beware, it's easy to not degrade the performance by setting this parameter correctly, use this only where absolutely necessary.
Max_user_connections: Sets the maximum number of connections allowed to a user. It is very useful in systems where the same MySQL server has access to multiple users simultaneously.
Delayed_queue_size: number of rows along with INSERT DELAYED. Will be less along the lines, the faster Mysql.
Sort_buffer_size: sets the size of the buffer allocated to the implementation of the clauses ORDER BY, and GROUP BY. To make queries more efficient it set this parameter to a value greater than 2M.
Table_cache: controls the number of open tables for all threads, increasing this variable will increase the tables loaded simultaneously and the execution of Mysql will be much faster. Attention to this parameter, the operating systems have a limit of pointers to files opened at once, if you exceed this limit you may incur in any system malfunction.
Thread_cache_size: Sets the number of threads to keep in cache. This feature makes the use of threads very fast.
The SHOW STATUS command helps us monitor in real time those variables, which more than others to help us understand what are the most significant improvements we can make:
- Handler_read_first: Indicates how many times the first record was read from a table. If this happens frequently, it means that the table was not indexed properly.
- Innodb_buffer_pool_wait_free: indicates the number of times that MySQL has to wait for the flushing of pages of memory. If the return value is high then the buffer pool size has not been configured correctly for the amount of write operations undergone by the server.
- Key_reads: indicates the number of times that MySQL accesses the file system for database indexes. This slows the speed of executing the query, so if this value is high then it means that the key buffer is too small and should be increased.
- Max_used_connections: Indicates the maximum number of connections that MySQL had to open during the last boot. On the one hand this represents a true benchmark (it helps to decide the maximum number of connections that the server can support) and the other is presented as a convenient traffic analyzer.
- Open_tables: Indicates the current number of tables open. This value should be analyzed in combination with the cache size of the table. Normally the size of table_cache should be set higher for this variable. If the value of open_tables is much lower than table_cache to avoid performance problems, it is therefore also reduce the size of the cache. If the value of open_tables is very high and is approaching the size of the cache, then raise it table_cache.
- Select_full_join: The number of full join MySQL has performed to meet the demands of clients. A high value indicates that instead of using indexes, MySQL has been forced to perform full join. This suggests the need for a strong index of these tables.
- Slow_queries: indicates the number of queries that took longer than usual to be done. A high value should lead us to analyze the Slow Query Log to identify what are the slow query to optimize.
- Threads_connected: Indicates the total number of clients that are connected to the server in such a moment.
- Created_tmp_disk_tables: indicates the number of temporary tables, which were created on disk instead of memory. Given that access the tables on a disk is usually slower than memory, the contents of the variable is inversely proportional to the speed with which queries are running the command CREATE TEMPORARY TABLE.
- Uptime: Displays how many seconds is on the service.