Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Mysql my.cnf partial parameter analysis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Msyql parameter tuning

Optimize for my.cnf files:

[mysqld]

Skip-locking (remove external locks on the file system)

Skip-name-resolve (no domain name anti-resolution, pay attention to the permissions / authorization problems caused by this)

Key_buffer_size = 256m (the total amount of memory allocated to the MyISAM index cache) this parameter can be set to 256m or 384m for servers with memory around 4GB. Be careful

Excessive setting of this parameter value will reduce the overall efficiency of the server!

Max_allowed_packet = 4m (maximum packet size allowed)

Thread_stack = 256K (size of each thread)

Table_cache = 128K (number of reusable threads in the cache)

Back_log = 384 (how many requests can be stacked within a short period of time before temporarily stopping responding to new requests, and if you need to allow a large number of connections in a short period of time, you can increase this number)

Sort_buffer_size = 2m (assigned to handle sorting in each thread)

Read_buffer_size = 2m (size of index buffer read)

Join_buffer_size = 2m (memory allocated to each thread to process scan table joins and indexes)

Myisam_sort_buffer_size = 64m (size of myisam engine sort buffer)

Table_cache = 512 (number of cached data tables to avoid the overhead of repeatedly opening tables)

Thread_cache_size = 64 (number of reusable threads in the cache, see the overhead of creating new threads)

Query_cache_size = 64m (controls the total amount of memory allocated to the query cache)

Tmp_table_size = 256m (specifies the memory size of the mysql cache)

Max_connections = 768 (maximum number of connections) refers to the total maximum number of connections of the mysql

Max_connect_errors = 10000 (maximum connection error data)

Wait_timeout = 10 (timeout to avoid attacks)

Thread_concurrency = 8 (set according to the number of cpu)

Skip-bdb disables unnecessary engines

Skip-networking (close mysql tcp/ip connection mode)

Log-slow-queries = / var/log/mysqlslowqueries.log

Long_query_time = 4 (set the time for slow query)

Skip-host-cache (for improving mysql speed)

Open_files_limit = 4096 (number of files opened)

Interactive_timeout = 10 (number of seconds the server waits for action on an interactive connection before shutting it down)

Max_user_connections = 500 (maximum number of user connections)

Mysql memory usage formula: actual physical memory calculation formula key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

MAX_QUERIES_PER_HOUR is used to limit the number of queries that users run per hour:

Mysql > grant all on dbname. * to db@localhost identified by "123456" with max_connections_per_hour 5

(db users limit the number of new connections opened by users to 5 per hour on dbname's database)

MAX_USER_CONNECTIONS limits how many users connect to the MYSQL server:

Mysql > grant all on dbname. * to db@localhost identified by "123456" with max_user_connections 2

(the maximum number of simultaneous connections that a db user can connect to a dbname database account at a time is 2)

MAX_UPDATES_PER_HOUR is used to limit the amount of database data that users can modify per hour:

Mysql > grant all on dbname. * to db@localhost identified by "123456" with max_updates_per_hour 5

(db users limit the number of times users modify and update the database to 5 times per hour on the dbname database)

MAX_USER_CONNECTIONS is used to limit the amount of database data that users can modify per hour:

Mysql > grant all on dbname. * to db@localhost identified by "123456"

With MAX_QUERIES_PER_HOUR 20; refers to the maximum number of connections for a single mysql user

(db users limit the number of connections per hour to 20 users on dbname's database)

=

Generally, it is enough to apply my-medium.cnf. Independent hosts can use my-large.cnf if their memory is large, and my-huge.cnf can be used for those with more than 8 GB of memory.

This is the end of the content of "Mysql my.cnf partial Parameter Analysis". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.