The MySQL configuration file is/etc/my.cnf, which is configured by modifying it to configure MySQL.
/etc/my.cnf from the following files:
If your memory is ≤64m, copy/usr/share/doc/mysql/my-small.cnf to/ETC/MY.CNF
# This was for a system with little memory (<= 64M) where MySQL was only used
# from time to time and it's important that the mysqld daemon
# doesn ' t use much resources.
If the memory is 128M, copy/usr/share/doc/mysql/my-medium.cnf to/ETC/MY.CNF
# This was for a system with little memory (32m–64m) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# Other programs (such as a Web server)
If the memory is 512M, copy/usr/share/doc/mysql/my-large.cnf to/ETC/MY.CNF
# large system with memory = 512M where the system runs mainly
# MySQL.
If memory is 1-2g, copy/usr/share/doc/mysql/my-huge.cnf to/ETC/MY.CNF
# Large system with memory of 1G-2G where the system runs mainly
# MySQL.
If the memory is 4G, copy/usr/share/doc/mysql/my-innodb-heavy-4g.cnf to/ETC/MY.CNF
# This was a MySQL example config file for systems with 4GB of memory
# running mostly MySQL using InnoDB only tables and performing complex
# queries with few connections.
But the MySQL parameter is so many, many times we still need to know their concrete meaning to make the concrete adjustment according to the actual problem.
———————————–
We can use show VARIABLES to see the system parameters and to determine the status of the system through show status.
———————————–
Let's take a look at the effect of the Table_cache parameter on performance. Excerpt from the description of MY-INNODB-HEAVY-4G.CNF:
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore sure to set the amount of open files
# allowed to @ least 4096 in the variable ' open-files-limit ' in
# section [Mysqld_safe]
Table_cache = 2048
For example: When the system is busy, we show variables, check the value of Table_cache, then show status, found Open_tables value and Table_cache almost, and opened_tables has been increased , it means that our table_cache is too small to set.
There are a lot of basic commands in MySQL, and the show command is one of them, and it's easy to confuse the use of SHOW commands among many users, and this article brings together the many uses of the show command.
A. Show tables or show tables from database_name; --Displays the names of all tables in the current database.
B. show databases; --Displays the names of all databases in MySQL.
C. Show columns from table_name to database_name; or show columns from Database_name.table_name; --Displays the column names in the table.
D. Show grants for USER_NAME; --Displays a user's permissions and displays results similar to the grant command.
E. Show index from TABLE_NAME; --Displays the index of the table.
F. Show status; --Displays information about some system-specific resources, such as the number of threads that are running.
G. Show variables; --Displays the name and value of the system variable.
H. Show Processlist; --Displays all the processes that are running in the system, that is, the query that is currently executing. Most users can view their own processes, but if they have process permissions, they can see all processes, including passwords.
I. Show table status; --Displays information about each table in the database that is currently being used or specified. Information includes the table type and the last updated time of the table.
J. Show Privileges; -Displays the different permissions supported by the server.
K. Show CREATE DATABASE database_name; --Shows whether the CREATE DATABASE statement can create the specified databases.
L. show CREATE TABLE table_name; --Shows whether the CREATE DATABASE statement can create the specified databases.
M. show Engies; --Displays the storage engine and the default engine that are available after installation.
N. show InnoDB status; --Displays the status of the InnoDB storage engine.
O. Show logs; --Displays the logs for the BDB storage engine.
P. Show warnings; --Displays errors, warnings, and notifications resulting from the last statement executed.
Q. Show errors; --Displays only errors resulting from the last execution statement.
R. Show [storage] engines; --Displays the available storage engine and the default engine after installation.
MySQL is a great open source database engine, and most of the websites and blogs are driven by MySQL. MySQL's default installation takes up more memory resources (compared to a 64MB VPS), and optimizing MySQL can reduce memory consumption and save more memory for other programs.
MySQL configuration file in/etc/mysql/my.cnf (Debian 5), in order to facilitate the configuration, MySQL provides a configuration file called My-small.cnf for the small resource system, which is set to a server less than 32MB memory. We can make a small adjustment on the basis of this configuration file.
Find/usr/share/doc/mysql-server-5.0/examples/my-small.cnf First, then overwrite/etc/mysql/my.cnf (Debian). In the case of CentOS 5, the path is:/usr/share/doc/mysql-server-5.0.45/my-small.cnf, covering/etc/my.cnf.
Parameter descriptionIf you do not use BDB table and InnoDB table, it is necessary to add the following 2 lines to close the unneeded table type, and to close the InnoDB can save a lot of memory, although InnoDB benefits a lot but not on a 64MB VPS, and is very memory.
Skip-bdbskip-innodb
Key_buffer is an important parameter in optimizing performance, used to cache tables keys and indexes, increasing this value to better handle indexes, and both read and write indexes. This setting is set to 16K enough. Table_cache is the number of tables opened by all threads, the added value can increase the number of MySQL file descriptors, avoid the frequent opening of the table, the original my-small.cnf in Table_cache set to 4 a bit small, a WordPress The page usually involves about 10 tables, other programs such as Drupal,mediawiki will involve more, change table_cache to 8.
Key_buffer = 16ktable_cache = 8
Max_connections is the largest number of connections to the database, which can be determined based on the amount of visits to your blog/website. If the blog/website often appears: Too many connections error message stating that you need to increase the value of max_connections. Thread_concurrency is the maximum number of concurrent threads, usually set to the number of CPU cores x2, if the server has 2 physical CPUs on the VPS host, and each physical CPU supports H.T Hyper-threading (two logical processor units on a single processor), the actual value is 4x 2 = 8.
If we were setting up a php.ini with only 2 php-cgi running at the same time, we should only set up 2 MySQL threads to run at the same time.
Max_connections = 16thread_concurrency = 2
For the blog/news site, the most used is the query, so you need to add the query cache settings. Query_cache_size is the buffer size used to execute the query.
Query_cache_limit = 256kquery_cache_size = 4M
Thread_stack is used to store identity information for each thread, such as thread ID, thread runtime environment, and so on, you can set Thread_stack to decide how much memory to allocate for each thread.
Sort_buffer_size is the buffer size allocated for each thread that needs to be sorted, and increasing this value can speed up the operation of order by and group by. Note: This parameter allocates memory for each connection, that is, if there are 16 connections, Sort_buffer_size is 64K, then the actual allocated memory is: 16x64k = 1MB. If you set a cache size that does not meet your needs, MySQL writes the data to disk to complete the sorting. Because disk operations and memory operations are not at an order of magnitude, sort_buffer_size has a significant impact on the performance of sorting.
Read_buffer_size is the buffer size for sequential read data, and as with Sort_buffer_size, the memory allocated by this parameter is in per-connection units. Read_buffer_size is used for full-table scanning, full-index scanning, and so on when the data is to be read sequentially, such as without the use of an index. At such times, MySQL reads the data block sequentially according to the data storage order, each time the data reads fast first will be in the read_buffer_size, when the buffer space is full or after all data reads, then returns the data in the buffer to the upper level caller, in order to improve the efficiency.
Read_rnd_buffer_size is the size of the buffer at random reading data, which corresponds to sequential reads.
The net_buffer_size is used to store connection information for the client connection thread and to return the cache size of the client's result set. When MySQL receives a request, it generates a result set, which is temporarily present in the cache before it is returned to the requesting thread, and then is sent to the client when it accumulates to a certain size to improve network efficiency. However, Net_buffer_size is only setting the initial size, MySQL will request more memory according to the actual need, but the maximum will not exceed max_allowed_packet.
Sort_buffer_size = 64kread_buffer_size = 256kread_rnd_buffer_size = 256knet_buffer_length = 2Kthread_stack = 64K
Skip-locking is used to avoid MySQL external locking, reduce the chance of error, enhance stability.
Optimized post-configurationSkip-locking
After optimization, MY.CNF is configured as follows, top view mysqld remains at 5M.
Memory Calculation formula MySQL memories = Key_buffer + max_connections * (Join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_ Size[Mysqld]port = 3306socket = /var/run/mysqld/mysqld.sockskip-lockingkey_buffer = 16kquery_cache_limit = 256kquery_cache_size = 4mmax_allowed_packet = 1mtable_cache = 8max_connections = 16thread_concurrency = 2sort_buffer_ Size = 64kread_buffer_size = 256kread_rnd_buffer_size = 256knet_buffer_length = 2kthread_stack = 64Kskip-bdbskip-innodb[ Mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash#safe-updates[isamchk]key_buffer = 8Msort_buffer_size = 8m[myisamchk]key_buffer = 8msort_buffer_size = 8m[mysqlhotcopy]interactive-timeout
In the overall system running process, the database server MySQL pressure is the largest, not only occupy a lot of memory and CPU resources, and occupy most of the disk IO resources, even the official PHP is claiming that PHP script 80% of the time is waiting for the results of the MySQL query returned. Thus, it is urgent to improve the load capacity of the system and reduce the resource consumption of MySQL.
1. Page Cache function:
The page caching feature reduces the resource consumption of MySQL (the system itself has been considered, using the generated HTML page, greatly reducing the pressure on the database).
2. Optimization of MySQL server
2.1, modify the whole site search
Modify My.ini (MY.CNF), add a line "Ft_min_word_len=1" after [mysqld], then restart MySQL, and then log in to the Web site (module management, full site search) to rebuild the full-text index.
2.2, record the slow query SQL statement, modify My.ini (MY.CNF), add the following code:
#log-slow-queries
long_query_time = 1 #是指执行超过多久的 sql will be log down
Log-slow-queries = E:/wamp/logs/slow.log #设置把日志写在那里, can be empty, the system will give a default file
#log-slow-queries =/var/youpath/slow.log Linux under Host_name-slow.log
Log-queries-not-using-indexes
2.3. mysql Cache settings
Mysql>show variables like '%query_cache% '; MySQL itself is a mechanism for SQL statement caching, and reasonable setting of our MySQL cache can reduce the database's IO resources.
#query_cache_type = How the cache is queried (default is on)
Query_cache_size If you want to disable query caching, set query_cache_size=0. The query cache is disabled and there is no significant overhead
Query_cache_limit does not cache results that are larger than this value. (Default is 1M)
2.4. Query Cache statistics
mysql> SHOW STATUS like ' qcache% ';
The number of contiguous memory blocks in the Qcache_free_blocks cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.
Qcache_free_memory free memory in the cache.
Qcache_hits is incremented each time a query is hit in the cache.
Qcache_inserts is incremented each time a query is inserted. The number of hits divided by the number of insertions is not the middle ratio; subtracting this value by 1 is the hit rate. In the above example, about 87% of the queries are hit in the cache.
The Qcache_lowmem_prunes cache is running out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The Free_blocks and free_memory above can tell you which is the case).
Qcache_not_cached the number of queries that are not appropriate for caching, usually because these queries are not SELECT statements.
Qcache_queries_in_cache the number of queries (and responses) that are currently cached.
The number of blocks in the qcache_total_blocks cache. Typically, these variables are displayed at intervals of several seconds to see the difference, which can help determine whether the cache is being used effectively. Running FLUSH STATUS resets some counters, which can be helpful if the server has been running for some time.
2.5, My.ini (my.conf) configuration
2.5.1, key_buffer_size = 256M
# KEY_BUFFER_SIZE Specifies the buffer size to use for the index, increasing it to get better index processing performance. The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!
2.5.2,
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 128K
Sort_buffer_size = 6M
The size of the buffer that can be used when the query is sorted. Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
2.5.3,
Read_buffer_size = 4M
The size of the buffer that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection!
2.5.4, join_buffer_size = 8M
The size of the buffer that the Federated query operation can use, as with sort_buffer_size, the allocated memory for that parameter is also exclusive to each connection!
2.5.5,
Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M
Specifies the size of the MySQL query buffer. You can observe this by executing the following commands in the MySQL console:
# > SHOW VARIABLES like '%query_cache% ';
# > SHOW STATUS like ' qcache% ';
# If the value of the qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering;
#如果Qcache_hits的值非常大, it indicates that query buffering is used very frequently, and if the value is smaller it will affect efficiency, then you can consider not using query buffering;
Qcache_free_blocks, if the value is very large, it indicates a lot of fragmentation in the buffer
2.5.6,
Tmp_table_size = 256M
Max_connections = 768
Specifies the maximum number of connection processes allowed by MySQL. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
2.5.7,
Max_connect_errors = 10000000
Wait_timeout = 10
Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.
2.5.8,
Thread_concurrency = 8
This parameter value is the number of server logical CPUs x2, if the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, the actual value is 4x2 = 8
2.5.9,
Skip-networking
Turn on this option to completely turn off MySQL's TCP/IP connection and do not turn on this option if the Web server is accessing the MySQL database server remotely. Otherwise it will not connect properly!
Previous: Linux command Learning Notes (3)--Understanding Suid,sgid,stick
Next: Teach you to use Fetion (fetion) for free texting in Linux
Related Popular articlesMySQL MY.CNF configuration recommendations
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
