|
At the same time, the online access volume continues to increase. For servers with 1 GB of memory, the server even crashes every day or gets stuck from time to time. This problem has plagued me for using MySQL for more than half a month. scalable algorithms, therefore, you can usually run with a small amount of memory or store MySQL more for better performance. After installing MySQL, the preparation file should be in the/usr/local/MySQL/share/MySQL directory, the preparation file has a few, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, websites with different traffic and server environments with different configurations must have different configuration files. Under normal circumstances, the preparation of the my-medium.cnf file can meet most of our needs; general we will copy the configuration file to/etc/My. CNF only needs to modify this configuration file. You can use mysqladmin variables extended-status-u root-P to see the current parameters. Three configuration parameters are the most important, that is, key_buffer_size, query_cache_size, and table_cache. Key_buffer_size only applies to the MyISAM table, Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Generally, we set the value to 16 m. In fact, the number of sites that are slightly larger is far from enough. By checking the status values key_read_requests and key_reads, we can check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above status values can be obtained using show status like 'key _ read % ). Or if you have installed phpMyAdmin, you can see it through the server running status. I recommend you use phpMyAdmin to manage mysql. The following status values are all my instance analysis obtained through phpMyAdmin: This server has been running for 20 days
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
|
Ratio close to 1: 8000 healthy Another way to estimate the key_buffer_size is to add up the size of the index space of each table in your website database. Take this server as an example: the number of large table indexes is about 125 MB, which will increase as the table grows. MySQL provides a query buffer mechanism starting from 4.0.1. Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%. You can check whether query_cache_size is set properly by adjusting the following parameters.
Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks
|
If the qcache_lowmem_prunes value is very large, the table clearly shows that the buffer is insufficient, and the qcache_hits value is very large, it indicates that the query buffer is frequently used, in this case, you need to increase the buffer size qcache_hits value is not big, it indicates that your query repetition rate is very low. In this case, using the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _no_cache to the SELECT statement explicitly indicates that no Query Buffer is used. Qcache_free_blocks. If this value is very large, it indicates that many fragments in the buffer zone query_cache_type specify whether to use the query buffer. My settings: Query_cache_size = 32 m Query_cache_type = 1 Obtain the following status values: Qcache queries in cache 12737 indicates the number of items currently cached Qcache inserts 20649006 Qcache hits 79060095 seems that the repeat query rate is quite high Qcache lowmem prunes 617913 has so many times that the cache is too low Qcache not cached 189896 Qcache free memory 18573912 Qcache free blocks 5328 seems to be a little big. Qcache total blocks 30953 If the memory allows 32 MB, you should add some more Table_cache specifies the table cache size. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values open_tables and opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding Status values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure. For machines with 1 GB memory, the recommended value is 128-256. I set table_cache = 256 The following status is displayed: Opening tables 256 Opened tables 9046 Although open_tables is already equal to table_cache, opened_tables has a very low value after 20 days of running compared to the server running time. Therefore, increasing the value of table_cache should be of little use. If the preceding value appears after six hours, you need to increase the value of table_cache. If you do not need to record binary logs, you can disable this function. After you disable it, you cannot recover the data before the problem occurs. You need to manually back up the data, the binary log contains all the statements for updating data. It is used to restore the data to the final State whenever possible when the database is restored. In addition, if you perform replication, you also need to use binary logs to transmit modifications. Log_bin specifies the log file. If no file name is provided, MySQL generates its own default file name. MySQL automatically adds a numeric reference after the file name. A new binary file is generated every time the service is started. In addition, you can use Log-bin-index to specify the index file, BINLOG-do-dB to specify the database for the record, and BINLOG-ignore-dB to specify a database without record. Note: BINLOG-do-DB and BINLOG-ignore-DB specify only one database at a time and multiple statements are required for multiple databases. In addition, MySQL will change all database names to lower-case ones. All Database names must be in lower-case when specifying the data library; otherwise, the database will not take effect. To disable this function, you only need to add the # sign before it. # Log-bin Enable slow query log) Slow query logs are useful for queries with tracing problems. It records all long_query_time queries. If needed, you can also record records that do not use indexes. The following is an example of slow log query: To enable slow query logs, you must set the log_slow_queries, long_query_times, and log-queries-not-using-indexes parameters. Log_slow_queries specifies the log file. If no file name is provided, MySQL generates its own default file name. Long_query_times specifies the threshold for slow queries. The default value is 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0. It indicates that the record does not use an index for queries. The author sets long_query_time = 10 Settings:
sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4
|
Parameter description: Back_log The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. The Unix listen (2) System Call manual page should have more details. Check your OS document to find the maximum value of this variable. Trying to set back_log to be higher than your operating system limit will be invalid. Max_connections The maximum number of concurrent connections. If 120 exceeds this value, it will be automatically restored. If a problem occurs, it can be automatically solved. Thread_cache No specific instructions are found, but it is useful to create more than 400 threads 20 days after 32, and thousands of threads have been created one day before. Thread_concurrency # Set your cpu count x2. For example, if there is only one cpu, then thread_concurrency = 2 # With 2 CPUs, thread_concurrency = 4 Skip-InnoDB # Remove InnoDB support
(Times of reading:
1115) |
