Shulou(Shulou.com)06/01 Report--

This article is a MySQL profile scheme for 4G memory systems (mainly running MySQL with only InnoDB tables and executing complex queries with several connections)

# start configuration information

# description: 4GB memory, only InnoDB, ACID, several connections, heavy query

# Type: system

# end configuration information

# you can copy the file to / etc/my.cnf to set global options, copy to mysql-data-dir/my.cnf to set server-specific options (in this installation this directory is C:mysqldata), and copy to ~ / .my.cnf to set user-specific options.

#

In this file, you can use all the permanent options supported by a program.

# if you want to know which options are supported by the program, use the "--help" option when running the program.

#

# more details on individual options can also be found in the manual.

The options below will be read by the MySQL client application.

# Note that only MySQL standard client applications are guaranteed to read this chapter.

# if you want your own MySQL client program to accept these values, you need to specify it as an option in the MySQL client library initialization.

#

[client]

# password = [your_password]

Port = 3306

Socket = / tmp/mysql.sock

# * Application-specific options are below *

# MySQL server.

[mysqld]

# Common configuration options

Port = 3306

Socket = / tmp/mysql.sock

# back_log refers to the number of connections held in the operating system listening queue, that is, the number of connections before the MySQL connection manager thread processes them.

# if you have a very high connection rate and have seen a "reject connection" error, you may need to increase this value.

# check the maximum value of this parameter in your system documentation.

# trying to set the back_log higher than your operating system limit will not help.

Back_log = 50

# there is no need to listen to a TCP/IP port at all.

# this may be a security enhancement if all processes running on the same host need to connect to the mysqld.

# all interactions with mysqld must be done through Unix sockets (sockets) or named pipes.

# Note that using this option on Windows without enabling named pipes (through the "enable-named-pipe" option) makes mysqld useless.

#

# skip-networking

# the maximum number of concurrent sessions allowed by MySQL.

# one of the connections will be reserved for users with SUPER privileges, allowing administrators to log in even if the connection limit has been reached.

Max_connections = 100

# maximum number of errors allowed per host.

# if this limit has been reached, the host will block connections to the MySQL server until "FLUSH HOSTS" is run or the server is restarted.

# invalid passwords and other errors during the connection phase will cause this value to be increased.

# see the "Aborted_connects" status variable of the global counter.

Max_connect_errors = 10

# the number of tables opened by all threads.

# increasing this value will increase the number of file descriptors required by mysqld.

# therefore, you must determine the number of open files to set, which is allowed to at least 4096 in the "open-file-limit" variable in the "mysqld Security" section.

Table_open_cache = 2048

# enable external file-level locking.

# enabling file locking will have a negative performance impact, so use it only if you have multiple database instances running on the same file (note that some restrictions are still applied) or if you use other software to rely on locking MyISAM tables at the file level.

# external-locking

# the maximum capacity of a query packet that the server can handle, and the maximum query size that the server can handle (important when working in large BLOB fields).

# expand dynamically, for each connection.

Max_allowed_packet = 16m

# the cache size that can be maintained for binary log SQL statements in a transaction.

# if you often use large, multi-statement transactions, you can increase this value for better performance.

# all statements from the transaction are buffered to the binary log cache and written to the binary log immediately after COMMIT.

# if the transaction is greater than this value, the temporary files on disk will be used instead.

The buffer is allocated to each connection when the first update statement is in the transaction.

Binlog_cache_size = 1m

# the maximum allowable size of a single HEAP (in memory) table.

# this option protects a very large HEAP table that is accidentally created, otherwise it will run out of memory resources.

Max_heap_table_size = 64m

The # sort buffer is used to perform the sorting of some ORDER BY and GROUP BY queries.

# if the sorted data does not enter the sort buffer, a disk-based merge sort will be used instead-see the "Sort_merge_passes" state variable.

# if sorting is needed, it will be assigned to each thread.

Sort_buffer_size = 8m

# this buffer is used to optimize FULL JOIN (JOIN without indexes).

In any case, the JOIN is very bad for performance in most cases, but setting this variable to a large value will reduce the impact on performance.

# see the "Select_full_join" state variable for a certain number of FULL JOIN.

# if FULL JOIN is found, it will be assigned to each thread.

Join_buffer_size = 8m

# there are many reusable threads that we keep in a cache.

# when a client disconnects, if the previous thread does not exceed the thread_cache_size, the client thread will be placed in the cache.

If you have a lot of new connections, this will greatly reduce the number of threads required to create.

# (if you have a good threaded implementation, this usually doesn't give a significant performance improvement. )

Thread_cache_size = 8

This allows the application to give the threaded system a hint of the number of threads running at the same time.

# this value is meaningful only on systems that support thread_concurrency () function calls (for example, Sun Solaris).

# you should try the number of CPU for thread_concurrency 2 + 4 + + 6 + +. Times.

Thread_concurrency = 8

The query cache is used to cache SELECT results and return them later, without actually executing the same query again.

# if you have a lot of the same queries and rarely change the table, enabling query caching will result in a significant speed improvement.

# look at the "Qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load.

# Note: if your table changes frequently, or if your query is different from the original text each time, the query cache will lead to slower, alternative performance improvements.

Query_cache_size = 64m

# only the cached result set is less than this limit.

This protects the query cache of a very large result set to overwrite all other query results.

Query_cache_limit = 2m

# the minimum word length compiled to the full-text retrieval index.

# if you need to retrieve a shorter word, you may want to reduce it.

# Note that after you change the value, you need to rebuild your FULLINDEX index.

Ft_min_word_len = 4

# if your system supports memlock () function calls, you may want to enable this option (run MySQL to keep it locked to memory and avoid potential swapping output in the event of high memory pressure).

# this is good for performance.

# memlock

If you do not specify a different table during the CREATE TABLE statement, the default table type to use when creating a new table.

Default-storage-engine = MYISAM

# the thread stack size used.

# this amount of memory is always reserved at connection time.

# MySQL itself usually requires no more than 64K of memory, however, if you are using your own stack UDF function or if your system needs more stacks for certain operations, you may need to set this value to a higher value.

Thread_stack = 19K

# set the default transaction isolation level.

# available levels are: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

Transaction_isolation = REPEATABLE-READ

# the maximum capacity of the internal (in-memory) temporary table.

# if the growth of a table exceeds this value, it is automatically converted to a disk-based table.

# this restriction is for a single table, but there can be many such tables.

Tmp_table_size = 64m

# enable binary logging.

# this is necessary for those who act as MASTER in a replication configuration.

# if you need the ability to recover from your last backup point in time, you also need binary logs.

Log-bin = mysql-bin

# the recommended binary log format-mixed.

Binlog_format = mixed

# if you are using chain slave server (A-> B-> C) to copy, you need to enable this option on server B.

# it allows log updates by logging from the server thread to the slave server's binary log.

# log_slave_updates

# enable the complete query log. Every query (even the wrong syntax) received by the server will be logged.

# this is useful for debugging and is usually disabled when the product is in use.

# log

# print warnings to error log files.

# if you have any MySQL problems, you should enable the warning log and check the error log for possible explanations.

# log_warnings

# record slow query.

# slow queries are queries that take longer than the total time defined in "long_query_time" or that do not use indexes if log_short_format is not enabled.

If you frequently add new queries to the system, it is a good idea to open this.

Slow_query_log

# all queries that take longer than the total time will be considered slow.

# do not use the "1" value here, as this will cause even very fast queries to be recorded from time to time (MySQL's current measurement time is only accurate to seconds).

Long_query_time = 2

# the directory used by MySQL to store temporary files.

For example, it is used to perform large disk-based sorting, as well as internal and explicit temporary tables.

# if you can't create a very large temporary file, it's good to put it in a swapfs/tmpfs file system.

# in addition, you can put it on a dedicated disk.

# you can specify multiple paths separated by ";"-they will be used in a loop later.

# tmpdir = / tmp

# * * Settings related to replication

A unique server identification number between # 1 and 2 ^ 32-1.

# this value is required for both master and slave servers.

# if "master-host" is not set, the default is 1, but if ignored, MySQL will not serve as a function of a master server.

Server-id = 1

# copy the slave server (comment out the master server section to use this).

#

# to configure the host as a replication slave server, you can choose the following two methods:

#

# 1) use the CHANGE MASTER TO command (which is fully described in our manual)-the syntax is:

#

# CHANGE MASTER TO MASTER_HOST = < host >, MASTER_PORT = < port >, MASTER_USER = < user >, MASTER_PASSWORD = < password >

#

# replace < host >, < user >, < password > with quoted strings, and < port > is the port number of the primary server (default is 3306).

#

# example:

#

# CHANGE MASTER TO MASTER_HOST = '125.564.12.1, MASTER_PORT = 3306, MASTER_USER =' joe', MASTER_PASSWORD = 'secret'

#

# or

#

# 2) set the following variables. However, if you choose this method, start replication within the first time (even if it is not successful, for example, if you do not type a password in MASTER_PASSWORD and the connection from the server fails), a master.info file will be created from the server, and any changes to the values of the following variables later in this file will be ignored and overwritten by the connection in the master.info file Unless you shut down the slave server, delete the master.info and restart the slave server.

# based on this factor, you may want to leave the untouched lines below (annotated) and use CHANGE MASTER TO instead (see above).

#

# requires a unique id between 2 and 2 ^ 32-1 (different from the primary server).

# if "master-host" is set, the default setting is 2.

# but if ignored, it will not be used as a slave server function.

# server-id = 2

#

# replication master server for this slave server-required.

# master-host = < hostname >

#

# user name, which will be used by the slave server for authentication when connecting to the master server-required.

# master-user = < username >

#

# password, which will be used by the slave server for authentication when connecting to the master server-required.

# master-password = < password >

#

# port, which the primary server is listening to.

# optional-default is 3306.

# master-port = < port >

# Let the slave server be read-only.

# only users with SUPER privileges and replication from the server thread can modify its data.

# you can use this to ensure that no application inadvertently replaces the master server to modify the data on the slave server.

# read_only

# * * options specific to MyISAM

The size of the # key buffer to cache the index block for the MyISAM table.

# Don't set it to more than 30% of your available memory, because the operating system also needs some memory to cache lines.

# even if you don't use the MyISAM table, you should still set it to 8-64m because it is also used for internal temporary disk tables.

Key_buffer_size = 32m

# buffer size for full table scans of MyISAM tables.

# if a full table scan is required, it will be assigned to each thread.

Read_buffer_size = 2m

# when rows are read in an ordered sort, rows can be read through this buffer to avoid looking up disks.

# if you set this value to a very high value, you can greatly improve the performance of ORDER BY.

# assign it to each thread when needed.

Read_rnd_buffer_size = 16m

# MyISAM uses a special tree-like cache for mass inserts (that is, INSERT. SELECT 、 INSERT... VALUES (...) And LOAD DATA INFILE) operations become faster.

# this variable limits the byte size of each thread's cache tree.

# setting it to 0 disables the optimization.

# to optimize performance, do not set it larger than "key_buffer_size".

# the buffer is allocated when a large number of inserts are detected.

Bulk_insert_buffer_size = 64m

# this buffer is allocated when MySQL needs to rebuild the index through REPAIR, OPTIMIZE, ALTER table statements, and LOAD DATA INFILE to an empty table.

# it is assigned to each thread, so be careful with larger settings.

Myisam_sort_buffer_size = 128m

# the maximum size of temporary files allowed by MySQL when re-indexing (during REPAIR, ALTER TABLE, or LOAD DATA INFILE).

# if "file-size" is larger than this value, the index will be created through the key cache (more slowly).

Myisam_max_sort_file_size = 10G

# if a table has more than one index, MyISAM can repair them with more than one thread in parallel when sorting.

If you have multiple CPU and enough memory, this makes a lot of sense.

Myisam_repair_threads = 1

# automatically check and repair MyISAM tables that are not properly closed.

Myisam_recover

# * INNODB specific options *

# if you have a MySQL server that supports InnoDB and you don't plan to use it, use this option.

# this saves some memory and disk space and improves speed.

# skip-innodb

# additional memory pool, InnoDB is used to store metadata information.

# if InnoDB needs more memory for this purpose, it will start allocating it from the operating system.

# since this is fast enough on most recent operating systems, you usually don't need to change this value.

# SHOW INNODB STATUS will display the current total usage.

Innodb_additional_mem_pool_size = 16m

# InnoDB, unlike MyISAM, uses a buffer pool to cache index and row data.

# the higher you set this value, the less disk Imax O will be when accessing the required data in the table.

# on a dedicated database server, you can set this parameter to 80% of the machine's physical memory size.

# Don't set it too large, as competition for physical memory may lead to paging in the operating system.

# Note that on 32-bit systems, you may be limited to 2-3.5 gigabytes of user-level memory per processor, so don't set it too high.

Innodb_buffer_pool_size = 2G

# InnoDB stores data to one or more data files to form a tablespace.

# if you have a single physical device for your data, then a single automatic extension file is sufficient.

# in other cases, a single file per device is a very good choice.

# you can also configure InnoDB to use the original disk partition-please refer to the manual for more information on this.

Innodb_data_file_path = ibdata1:10M:autoextend

# set this option if you want InnoDB tablespace files to be stored somewhere else.

# the default is the MySQL data directory.

# innodb_data_home_dir = < directory >

# the number of IO threads used in asynchronous IO operations.

# this value is hard-coded as 4 on Unix systems, but on Windows, disk Imax O may benefit from a larger number.

Innodb_file_io_threads = 4

# if you encounter InnoDB tablespace corruption, setting this value to a non-zero value will easily help you export your table.

# start with a value of 1 and increase it until you can successfully export the table.

# innodb_force_recovery=1

# the number of threads allowed in the InnoDB kernel.

The best value is highly dependent on the scheduling properties of the application, hardware, and operating system.

A value that is too high may cause the thread to jolt.

Innodb_thread_concurrency = 16

# if it is set to 1InnoDB to refresh the transaction log to disk each time it commits (provides complete ACID behavior).

# if you want to make a safe compromise and you are running a small transaction, you can reduce the log disk IbinO for 0 or 2.

A value of 0 means that the log is only written to the log file, and the log file is flushed to disk about once per second.

A value of 2 indicates that the log is written to the log file each time it is committed, but the log file is only flushed to disk about once a second.

Innodb_flush_log_at_trx_commit = 1

# accelerate the shutdown of InnoDB.

This will disable InnoDB to do a full purge and insert buffer merge when closed.

# it may increase the shutdown time a lot, but instead, InnoDB will finish it the next time it starts.

# innodb_fast_shutdown

# the buffer size used by InnoDB to buffer log data.

# once it is full, InnoDB will refresh it to disk.

# because it is refreshed every second anyway, there is no need to make it very large (or even a long transaction).

Innodb_log_buffer_size = 8m

The size of each log file in a log group.

# you can set the joint size of log files to 25% of your buffer pool size, 100% of your buffer pool size, to avoid dynamic refresh and rewrite of the buffer pool that is unnecessary for log files.

# Note, however, that a larger log file size will increase the time required for recovery processing.

Innodb_log_file_size = 256m

# the total number of files in the log group.

A value of 2-3 is usually sufficient.

Innodb_log_files_in_group = 3

# location of the InnoDB log file.

# defaults to the data directory of MySQL.

# you may want to specify it to a dedicated hard drive or a RAID1 volume label to improve performance.

# innodb_log_group_home_dir

The maximum percentage of dirty pages allowed in the # InnoDB buffer pool.

# if it arrives, InnoDB will begin to actively clean them up to avoid consuming all clean pages.

# this is a soft limit and there is no guarantee that it can be maintained forever.

Innodb_max_dirty_pages_pct = 90

# the refresh method used by InnoDB for logs.

# tablespaces always use double write refresh logic.

# the default value is "fdatasync" and the other options are "O_DSYNC".

# innodb_flush_method = O_DSYNC

# the amount of time an InnoDB transaction should wait to be authorized to lock before rolling back.

# InnoDB automatically detects transaction deadlocks in its own locking table and rolls back transactions.

# if you use the LOCK TABLES command in the same transaction, or other storage engine that is more transactional secure than InnoDB, then there will be a deadlock that InnoDB cannot prompt later.

# if it's like this, timeouts are useful for solving problems.

Innodb_lock_wait_timeout = 120

[mysqldump]

# do not buffer the entire result set before writing to a file.

# it is necessary to export very large tables.

Quick

Max_allowed_packet = 16m

[mysql]

No-auto-rehash

# only allow UPDATE and DELETE to use keys.

# safe-updates

[myisamchk]

Key_buffer_size = 512m

Sort_buffer_size = 512m

Read_buffer = 8m

Write_buffer = 8m

[mysqlhotcopy]

Interactive-timeout

[mysqld_safe]

# increase the number of files allowed to be opened per processing.

# warning: make sure you have set the global system limit high enough!

For a large number of open tables, a high value is required.

Open-files-limit = 8192

You can refer to the above parameter settings according to your own implementation.

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.