Mysql Performance Optimization Configuration my. cnf file, performance optimization my. cnf

Mysql Performance Optimization Configuration my. cnf file, performance optimization my. cnf

[Client]
# Password = your_password
Port = 3306
Socket =/opt/mysql/dbdata/mysql. sock

# The MySQL server
[Mysqld]
Port = 3306
Socket =/opt/mysql/dbdata/mysql. sock
Skip-external-locking
Key_buffer_size = 384 M
Max_allowed_packet = 1 M
Table_open_cache = 512

# Sort_buffer_size = 2 M
Sort_buffer_size = 256 M

# Read_buffer_size = 2 M
Read_buffer_size = 32 M

# Read_rnd_buffer_size = 8 M
Read_rnd_buffer_size = 32 M

Myisam_sort_buffer_size = 64 M

Thread_cache_size = 8
Query_cache_size = 32 M

# Enable slow query logs
Log_slow_admin_statements = ON
Log_slow_slave_statements = ON
Slow_query_log = 1

# Slow query log file storage path
Slow_query_log_file =/opt/mysql/mysqllog/logfile/slow-query.log

# How many seconds of SQL statements are executed to record slow query logs
Long_query_time = 2

# Searches without indexes are recorded in files
Log_queries_not_using_indexes = 1

# Writing log records to log files
Log_output = 'file'

# Data connection wait time, in seconds
Interactive_timeout = 1800
Wait_timeout = 1800

Basedir =/opt/mysql/product
Datadir =/opt/mysql/dbdata
Max_binlog_size = 100 M
Log_bin =/opt/mysql/mysqllog/binlog. bin
Log-error =/opt/mysql/mysqllog/logfile/mysql-err.log

Binlog_format = mixed
Expire_logs_days = 7
Binlog_cache_size = 4 MB

Pid-file =/opt/mysql/dbdata/mysql. pid
Default-storage-engine = MyISAM

User = mysql

Group_concat_max_len= 10240
Max_connections = 3000

#2 times the number of (cpu + disk)
# Thread_concurrency = 8
Thread_concurrency = 24

# Point the following paths to a dedicated disk
# Tmpdir =/tmp/
# Skip-networking

Log-bin = mysql-bin

Server-id = 1

# Server-id = 2
#
# The replication master for this slave-required
# Master-host = #
# The username the slave will use for authentication when connecting
# To the master-required
# Master-user = <username>
#
# The password the slave will authenticate with when connecting
# The master-required
# Master-password = <password>
#
# The port the master is listening on.
# Optional-defaults to 3306
# Master-port = <port>
#
# Binary logging-not required for slaves, but recommended
# Log-bin = mysql-bin
#
# Binary logging format-mixed recommended
Binlog_format = mixed

# Use the InnoDB engine to enable the following configuration items
# Uncomment the following if you are using InnoDB tables
# Innodb_data_home_dir =/opt/mysql/dbdata
# Innodb_data_file_path = ibdata1: 10 M: autoextend
# Innodb_log_group_home_dir =/opt/mysql/dbdata

# Data and index buffer size
# Innodb_buffer_pool_size = 256 M
# Innodb_buffer_pool_size = 10240 M

# Size of data fields and other data structures
# Innodb_additional_mem_pool_size = 20 M
# Innodb_additional_mem_pool_size = 20 M

# Set .. _ log_file_size to 25% of buffer pool size
# Redo log size
# Innodb_log_file_size = 64 M
# Innodb_log_file_size = 2560 M

# Log buffer size
# Innodb_log_buffer_size = 8 M
# Innodb_log_buffer_size = 16 M

#0 commit transactions do not write logs, log file writes per second and flush Disk
# Log files are written to the flush disk every second or every time a transaction is committed
#2 when each transaction is committed, log files are written to the flush disk per second
# Innodb_flush_log_at_trx_commit = 1
# Innodb_flush_log_at_trx_commit = 1

# Innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16 M

[Mysql]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-updates

[Myisamchk]
# Key_buffer_size = 128 M
# Sort_buffer_size = 128 M
# Read_buffer = 2 M
# Write_buffer = 2 M

Key_buffer_size = 256 M
Sort_buffer_size = 256 M
Read_buffer = 32 M
Write_buffer = 32 M

[Mysqlhotcopy]
Interactive-timeout

[Mysqlhotcopy]
Interactive-timeout