A) connection

The connection usually comes from a Web server, and here are some parameters related to the connection and how to set them.

1, max_connections

This is the maximum number of connections allowed by the Web server, and remember that each connection uses session memory (more on session memory later in this article).

2, max_packet_allowed

The maximum mobile game packet size is usually equal to the size of the maximum data set you need to return in a large block, or larger if you are using remote mysqldump.

3, aborted_connects

Check the system state counter to make sure it is not growing, if it is, the client is experiencing an error while connecting.

4, thread_cache_size

Inbound connections create a new thread in MySQL. Because MySQL is cheap and fast to open and close connections, it doesn’t have as many persistent connections as other databases like Oracle, but thread creation doesn’t save time, which is why MySQL thread caching is used.

Keep an eye on the threads being created if they are growing, so that your thread cache is larger, and for thread_cache_size 2550 or 100, memory footprint is small.

(2) Query cache

(3) temporary table

Memory speed is quite fast, so we want all sorting to be done in memory, we can adjust the query to make the result set smaller to achieve memory sorting, or we can set variables to be larger.

tmp_table_size

max_heap_table_size

Whenever a temporary table is created in MySQL, it uses the minimum of these two variables as the threshold. In addition to building temporary tables on disk, many sessions are created that grab limited resources, so it is better to adjust the query rather than set these parameters higher. In the meantime, Note that tables with BLOB or TEXT field types will be written directly to disk.

(4) Session memory

Each session in MySQL has its own memory, which is the memory allocated for SQL queries, so you want to make it as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time. What’s a little bit of black art here is that MySQL allocates caches on demand, so you can’t just add them and multiply by the number of sessions that are estimated to be much larger than MySQL’s typical usage.

The best practice is to start MySQL, connect all sessions, and then continue to focus on the VIRT column of the top-level session. The number of mysqld rows usually remains relatively constant, which is the actual total memory usage, minus all static

1, read_buffer_size

Caches continuously scanned blocks across storage engines, not just MyISAM tables.

2, sort_buffer_size

The size of the perform sort cache is best set to 1m-2m and then set in session to a higher value for a particular query.

3, join_buffer_size

Perform the federated query to allocate the cache size, set it to 1m-2m, and then set it separately for each session as needed.

4, read_rnd_buffer_size

For sorting and order by operations, it is best to set it to 1M, and then it can be set to a larger value as a session variable in the session.

(5) Slowly query logs

Slow query logs are a useful feature of MySQL.

1, the log_slow_queries

The log_slow_queries parameter in the MySQL parameter is set in the my.cnf file. Set it to ON. By default, MySQL will place the file in the data directory and name the file “hostname-slow.log”, but you can specify a name for it when setting this option. If relative electron has more intuitive understanding, it can also refer to its format as follows:

www.diuxie.com

2, long_query_time

The default is 10 seconds, you can set it dynamically from 1 to set it to ON, if the database is started, by default, logging will be turned off. As of 5.1.21 and with Google patches installed, this option can be set in microseconds, which is a great feature, because once you eliminate all queries that take longer than 1 second, the adjustment is very successful, and this can help you eliminate problematic SQL before it gets bigger.

3, log_queries_not_using_indexes

It’s a good idea to turn on this option, which actually records queries that return all rows.