Mysql Performance Tuning

PS: This configuration file is designed for Dell R710, dual Xeon E5620, and 16 GB memory hardware. CentOS 5.6 64-bit system, MySQL 5.5.x stable version. Applicable to websites with daily IP addresses of 50-100 w and PV of-w, mainly using the InnoDB Storage engine. For other application environments, Set Optimization Based on actual conditions. # The following options will be read by the MySQL client application. # Note that only client applications attached to MySQL can read this section. # If you want your MySQL application to obtain these values. # You need to specify these options when initializing the MySQL client database. # [Client] # password = [your_password] port = @ MYSQL_TCP_PORT @ socket = @ MYSQL_UNIX_ADDR @ # *** application customization options *** # MySQL Server # [mysqld] # General configuration option port = @ MYSQL_TCP_PORT @ socket = @ MYSQL_UNIX_ADDR @ # back_log is the number of connections that the operating system can maintain in the listening queue, # The queue stores the connection before the MySQL Connection Manager thread processes it. # If you have a very high connection rate and an "connection refused" error occurs, # You should increase the value here. # Check your operating system documentation to obtain the maximum value of this variable. # If you set back_log to a value higher than your operating system limit, back_log = 300 # does not listen on the TCP/IP Port. # If all processes are in Connect the same server to the local mysqld. # This setting will enhance the security. # All mysqld connections are made through Unix sockets or named pipelines. # Note: In windows, if you do not enable the named pipeline option, you only need to use this option # (using the "enable-named-pipe" option), the mysql service will not work! # Skip-networking # maximum number of concurrent sessions allowed by the MySQL service # One of the connections will be retained as Administrator Logon by the SUPER permission. # Even if the maximum number of connections has been reached. max_connections = 3000 # maximum number of errors allowed for each client connection. If this limit is reached. # This client will be blocked by the MySQL service until "flush hosts" is executed or the service is restarted # This value will be added for invalid passwords and other connection errors. # view the status of "Aborted_connects" to obtain the Global Counter. max_connect_errors = 30 # number of tables opened by all threads. # adding this value increases the number of file descriptors required by mysqld # in this way, you need to confirm that the "open-files-limit" variable in [mysqld_safe] allows at least 40 files to be opened. = 4096 # allow external file-level locks. enable the filelock. Can cause negative impact # This option is used only when you run multiple database instances on the same file (note that there are still other constraints !) # Alternatively, you can use other software dependencies on the file layer to lock the MyISAM Table # external-locking # maximum size of the Request package that the service can process and the maximum request size that the service can process (required when working with large BLOB fields) # The size of each connection. dynamically increase the size of max_allowed_packet = 32 M # binlog in a transaction to record the cache size held by the SQL status # If you often use large, multi-statement transactions, you can add this value to obtain greater performance. # All the statuses from the transaction will be buffered in the binlog buffer and then written to the binlog at one time after submission # if the transaction is larger than this value, it will be replaced by temporary files on the disk. # This buffer is created when the first update status of each connected transaction is binlog_cache_size = 4 M # maximum capacity allowed by the independent memory table. # This option is used to prevent accidental creation of an oversized memory table from permanently consuming all memory resources. max_heap_table_siz E = 128 M # The sort buffer is used to process sorting caused by order by and group by queues. # If the sorted data cannot be placed in the SORT buffer, # A disk-based merged category used for replacement will be used # view the "Sort_merge_passes" status variable. # When sorting occurs, sort_buffer_size = 16 M is allocated by each thread # This buffer is used to optimize full Union (full JOINs union without indexes ). # Similar joins have very bad performance in most cases. # setting this value to a large value can reduce the performance impact. # use the "Select_full_join" status variable to view the total number of conjoins # When the full Union occurs, allocate join_buffer_size = 16 M in each thread # The number of threads we keep in the cache for reuse # If a client is disconnected, if the number of threads in the cache is less than thread_cache_size, # The client thread is put into the cache. # This Works To greatly reduce the overhead of thread creation when you need a large number of new connections # (generally, if you have a good thread model, this will not significantly improve the performance .) thread_cache_size = 16 # This allows the application to give the thread system a prompt to give the desired number of threads at the same time. # This value is only valid for systems that support the thread_concurrency () function (for example, Sun Solaris ). # You can try to use [number of CPUs] * (2 .. 4) as the value of thread_concurrency = 8 # The Query Buffer is often used to buffer the SELECT results and will not directly return results during the next same query. # enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables. # Check the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load. # Note: Your table often changes If the original query is different every time, # query buffering may cause performance degradation rather than performance improvement. query_cache_size = 128 M # Only results smaller than the set value are buffered # This setting is used to protect the Query Buffer and prevent a large result set from overwriting all other query results. query_cache_limit = 4 M # The minimum font length of the full-text search index. # You may want to reduce it if you need to search for shorter words. # note that after you modify this value, # You need to recreate your FULLTEXT index ft_min_word_len = 8 # if your system supports the memlock () function, you may want to enable this option to make mysql running in high memory tension, keep data locked in the memory and prevent data from being swapping out # This option is helpful for performance # memlock # use as the default table type when creating a new table, # If the table type is not specially executed during creation, default_tab is used. Le_type = MYISAM # The heap size used by the thread. this capacity is reserved for each connection. # MySQL itself usually does not require 64 KB of memory # If you use a large number of heap UDF functions # Or your operating system requires more heap for some operations, # You may need to set it to a higher level. thread_stack = 512 K # Set the default transaction isolation level. available levels: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLEtransaction_isolation = REPEATABLE-READ # internal (in memory) the maximum size of a temporary table # If a table grows to a greater value, it is automatically converted to a disk-based table. # This restriction applies to a single table, not the sum. tmp_table_size = 128 M # enable the binary log function. # In the replication configuration The TER master server must enable this option # If you need to recover from your last backup based on the time point, you also need binary logs. log-bin = mysql-bin # If you are using the replication mode of the chain slave server structure (A-> B-> C), # You need to open this option on server B. # This option is used to open the update logs that have been reinstalled on the slave thread, # and write the logs to the slave server binary log. # log_slave_updates # enable full query logs. all queries received by the server (or even a query with an incorrect syntax) # are recorded. this is very useful for debugging and is often disabled in the production environment. # log # print the warning to the error log file. if you have any problems with MySQL # You should open the warning log and carefully review the error log to find out the possible causes. # log_warnings # record slow queries. slow query refers to a query that consumes more time than the time defined by long_query_time. # If log_long_forma T is opened, and queries that do not use indexes will also be recorded. # If you often add new queries to existing systems, this is generally a good idea. Log_slow_queries # All queries that use more than this time (in seconds) are considered as slow queries. # Do not use "1" here. Otherwise, all queries or even very fast query pages will be recorded (because the current MySQL time precision can only reach the second level ). long_query_time = 6 # record more information in slow logs. # It is recommended to enable this option. # enabling this option will record the queries that do not use indexes and be appended to the slow log as slow queries. log_long_format # This directory is used by MySQL to save temporary files. for example, # It is used to process disk-based large sorting, which is the same as internal sorting. # And a simple temporary table. # If you do not create a very large temporary file, it may be better to place it on the swapfs/tmpfs file system # You can also place it on an independent disk. # You can use ";" to place multiple paths # They will be used by polling according to the roud-robin method. # tmpdir =/ Tmp # *** settings related to master-slave replication # unique service identification number. The value ranges from 1 to 2 ^ 32-1. # This value must be set on both master and slave. # If "master-host" is not set, the default value is 1. However, if this option is ignored, MySQL will not take effect as the master. server-id = 1 # copy the server Load balancer instance (remove the comment from the master segment to make it take effect) # to configure the server as the server Load balancer instance for replication, you can select either of the following methods: #1) use the change master to command (which is fully described in our manual)-# Syntax: # change master to MASTER_HOST =, MASTER_PORT =, # MASTER_USER =, MASTER_PASSWORD =; # Replace the fields enclosed by Angle brackets and the master port number (3306 by default ). # example Child: # change master to MASTER_HOST = '2017. 56.12.1 ', MASTER_PORT = 3306, # MASTER_USER = 'job', MASTER_PASSWORD = 'secret'; ##or ## 2) set the following variables. in either case, when you choose this method, and then start the replication for the first time (or even if it fails, # For example, if you enter the wrong password in the master-password Field and slave cannot be connected), # slave will create a master.info file, any subsequent changes to parameters contained in this file will be ignored # And overwritten by the content in the master.info file, unless you disable the slave service, delete master.info, and restart the slave service. # For this reason, you may not want TO touch the configuration (commented out) and use change master to (view the above ). Replace ## the required unique ID number is located between 2 and 2 ^ 32-1 # (and different from the master) # If the master-host is set. the default value is 2 #, but if it is omitted, it will not take effect # server-id = 2 ## master in the replication structure-required # master-host = # username used for authentication by slave when connected to the master-required # master- user ### password used by slave for authentication when connected to the master node-required # master-password ### port on which the master node listens. # optional-the default value is 3306 # master-port = # Make slave read-only. only the user has the SUPER permission and the preceding slave thread can modify data. # You can use this item to ensure that no application will accidentally modify the slave instead of the data on the master. # read_only # *** MyISAM related options # keywords Buffer size, which is generally used to Buffer Index blocks of the MyISAM table. # Do not set it to 30% larger than your available memory # because some of the memory is also used by the OS to buffer row data # Even if you do not use the MyISAM table, you also need to set up the 8-64 M memory because it will also be used by the internal temporary disk table. key_buffer_size = 128 M # buffer size used for full table scan in the MyISAM table. # allocate it to the corresponding thread when full table scan is required. read_buffer_size = 8 M # When a row is read from a sorted sequence after sorting, the row data is read from this buffer to prevent disk seek. # If you increase this value, you can improve the performance of many order by statements. # When necessary, each thread allocates read_rnd_buffer_size = 64 M # MyISAM uses a special cache similar to a tree to enable burst inserts # (these inserts are, INSERT... SELECT, INSERT... VALUES (...), (...), ..., And load data # INFILE) faster. this variable limits the number of bytes of the buffer tree in each process. # setting 0 will disable this optimization. # For optimization, do not set this value to be greater than "key_buffer_size ". # When burst inserts are detected, the buffer will be allocated. bulk_insert_buffer_size = 256 M # This buffer is allocated when MySQL needs to re-index a REPAIR, OPTIMIZE, ALTER, and load data infile to an empty table. # This is allocated in each thread. therefore, be careful when setting the token. myisam_sort_buffer_size = 256 M # maximum temporary file size (when REPAIR, alter table, or load data infile) allowed during index reconstruction by MySQL ). # If the file size is greater than this value, the index will be created through the key-value buffer (slower) myisam_max_sort_file_size = 10G # If the temporary file used for faster index creation is greater than the specified value, the key-value buffer method is used. # This is mainly used to force the long string key in a large table to use the slow key-value buffer method to create an index. myisam_max_extra_sort_file_size = 10G # If a table has more than one index, MyISAM can use more than one thread to fix them through parallel sorting. # This is a good choice for users with multiple CPUs and a large amount of memory. myisam_repair_threads = 1 # automatically check and fix the MyISAM table that is not properly closed. myisam_recover # Federated skip-federated # ***** BDB related options *** is disabled by default. If your MySQL Service supports BDB but you are not prepared to use this option, use this option. this saves memory and may accelerate some things. skip-bdb # *** INNODB related options *** # For example If your MySQL Service supports InnoDB but is not intended to be used, # using this option will save memory and disk space, in addition, some parts of the acceleration # skip-innodb # The additional memory pool is used by InnoDB to save metadata information # If InnoDB requires more memory for this purpose, it will start to apply for memory from the OS. # Because this operation is fast enough in most modern operating systems, you generally do not need to modify this value. # The show innodb status command displays the number of instances that are used first. innodb_additional_mem_pool_size = 64 M # InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM. # The larger you set, the less disk I/O you need to access the data in the table. # On an independently used database server, you can set this variable to 80% of the server's physical memory size # Do not set it to too large. Otherwise, the competition in physical memory may lead to page changes in the operating system. # Note: In a 32-bit System Each process may be limited to 2-3.5 GB of user memory. # Do not set it too high. innodb_buffer_pool_size = 6G # InnoDB saves the data in one or more data files as tablespaces. # If you only have a single logic driver to save your data, a single auto-increment file is enough. # In other cases. A file on each device is generally a good choice. # You can also configure InnoDB to use the raw disk partition.-please refer to the Manual for more information about innodb_data_file_path = ibdata1: 10 M: autoextend # Set this option if you want InnoDB tablespace files to be saved in other partitions. # It is stored in MySQL datadir by default. # innodb_data_home_dir = # Number of I/O threads used to synchronize IO operations. # This value is hard coded as 4 in Unix, But Windows disk I/O may perform better in a large value. innodb_file _ Io_threads = 4 # If you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table. # From 1 and add this value to know that you can successfully export the table. # innodb_force_recovery = 1 # Number of threads allowed in the InnoDb core. # The optimal value depends on the scheduling methods of applications, hardware, and operating systems. # A high value may cause mutually exclusive thread bumps. innodb_thread_concurrency = 16 # if it is set to 1, InnoDB will refresh (fsync) the transaction log to the disk after each commit, # This provides a complete ACID behavior. # If you are willing to compromise transaction security and you are running a small food, you can set this value to 0 or 2 to reduce disk I/O #0 caused by transaction logs. It means that logs are only written to log files every second and the log files are refreshed to the disk. #2 indicates that the log file written to the log is refreshed to the disk only every second after each submission. innodb_flush _ Log_at_trx_commit = 2, however, if a fault occurs, data may be lost! The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. This is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. It means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to the hard disk every second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. Even if MySQL fails, the transaction data may be lost. Value 2 may only lose data when the entire operating system is down .) # Accelerate InnoDB shutdown. this will prevent InnoDB from being completely cleared when it is disabled and insert buffer merging. # This may greatly increase the shutdown time, But InnoDB may perform these operations at the next startup. # innodb_fast_shutdown # buffer size used to buffer log data. # When the value is full, InnoDB must refresh the data to the disk. # Because the value is refreshed every second, it is unnecessary to set this value too large (or even for long transactions) innodb_log_buffer_size = 16 M # size of each log file in the log group. # You should set the total size of log files to 25% ~ of the size of your buffer pool ~ 100% # To avoid overwriting unnecessary buffer pool refresh behaviors in log files. # In any case, note that a large log file size will increase the time required to restore the process. innodb_log_file_size = 512 M # Total number of files in the log group. # generally, 2 ~ 3 is better. innodb_log_files_in_group = 3 # location of the InnoDB log file. the default value is MySQL datadir. # You can specify it to an independent hard disk or a RAID1 volume to improve its performance # innodb_log_group_home_dir # maximum allowable proportion of dirty pages in the InnoDB buffer pool. # If the quota is reached, InnoDB will start refreshing them to prevent them from interfering with the clean data page. # This is a soft limit and is not guaranteed to be executed absolutely. innodb_max_dirty_pages_pct = 90 # How InnoDB refreshes logs. # The tablespace always uses the double write refresh method # The default value is "fdatasync", and the other is "O_DSYNC ". # innodb_flush_method = O_DSYNC # Before rollback, how long should an InnoDB Transaction wait for a lock to be approved. # InnoDB automatically detects transaction deadlocks in its lock tables and And roll back the transaction. # If you use the lock tables command or use a transaction-safe storage engine other than InnoDB in the same transaction # A deadlock may occur and InnoDB cannot notice it. # In this case, the timeout value is very helpful for solving this problem. innodb_lock_wait_timeout = 120 [mysqldump] # Do not cache the entire result in the memory before writing it to the disk. when exporting a very large table, you need to use quickmax_allowed_packet = 32 M [mysql] no-auto-rehash # Only UPDATEs and DELETEs with key values are allowed. # safe-updates [isamchk] key_buffer = 2048Msort_buffer_size = 2048Mread_buffer = 32Mwrite_buffer = 32 M [myisamchk] key_buffe R = 2048Msort_buffer_size = 2048Mread_buffer = 32Mwrite_buffer = 32 M [mysqlhotcopy] interactive-timeout [mysqld_safe] # increase the number of files that can be opened by each process. # warning: Make sure you have set high system-wide limits! # To open a large number of tables, set this value to "Big open-files-limit = 8192 ".