OpenFire Server MySQL Optimization

OpenFire Server MySQL Optimization:

[Email protected] ~]# mysql-u root-p XXXXX

Mysql> show processlist;

+----------+-----------------+---------------------+-------+---------+-------+-----------------------------+--- ---------------+

| Id | User | Host | db | Command | Time | State | Info |

+----------+-----------------+---------------------+-------+---------+-------+-----------------------------+--- ---------------+

| 1 | Event_scheduler | localhost | NULL | Daemon | 51497 | Waiting for next activation | NULL |

| 16069885 | Xx_xxx_user | 10.164.13.209:48184 | XXXXX |     Sleep |                             0 | | NULL |

| 16069886 | Xx_xxx_user | 10.164.13.209:48185 | XXXXX |     Sleep |                             0 | | NULL |

| 16069887 | Xx_xxx_user | 10.164.13.209:48186 | XXXXX |     Sleep |                             0 | | NULL |

| 16069888 | Xx_xxx_user | 10.164.13.209:48187 | XXXXX |     Sleep |                             0 | | NULL |

| 16069889 | Xx_xxx_user | 10.164.13.209:48188 | XXXXX |     Sleep |                             0 | | NULL |

......

| 16071110 | Xx_xxx_user | 10.164.13.209:48520 | XXXXX |     Sleep |                             3 | | NULL |

| 16358232 | Root | localhost | XXXXX |     Query | 0 | NULL | Show Processlist |

+----------+-----------------+---------------------+-------+---------+-------+-----------------------------+--- ---------------+

118 Rows in Set (0.00 sec)

Mysql> Show variables like '%timeout ';

+----------------------------+----------+

| variable_name | Value |

+----------------------------+----------+

| Connect_timeout | 10 |

| Delayed_insert_timeout | 300 |

| Innodb_lock_wait_timeout | 50 |

| Innodb_rollback_on_timeout | OFF |

| Interactive_timeout | 100 |

| Lock_wait_timeout | 31536000 |

| Net_read_timeout | 30 |

| Net_write_timeout | 60 |

| Slave_net_timeout | 3600 |

| Wait_timeout | 100 |

+----------------------------+----------+

Rows in Set (0.00 sec)

Mysql> show global variables like '%timeout ';

+----------------------------+----------+

| variable_name | Value |

+----------------------------+----------+

| Connect_timeout | 10 |

| Delayed_insert_timeout | 300 |

| Innodb_lock_wait_timeout | 50 |

| Innodb_rollback_on_timeout | OFF |

| Interactive_timeout | 100 |

| Lock_wait_timeout | 31536000 |

| Net_read_timeout | 30 |

| Net_write_timeout | 60 |

| Slave_net_timeout | 3600 |

| Wait_timeout | 100 |

+----------------------------+----------+

Rows in Set (0.00 sec)

Mysql> set global interactive_timeout=100;

mysql> set global wait_timeout=100;

If you modify Interactive_timeout wait_timeout will also change, and only modify wait_timeout is not effective.

[Email protected] ~]# vim/etc/my.cnf

[Client]

Port = 3306

Socket =/data/mysql/mysql.sock

[Mysqld_safe]

Socket =/data/mysql/mysql.sock

Nice = 0

[Mysqld]

#datadir =/data/mysql/data/mysql

Socket=/data/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks symbolic-links=0

# Default Port

Port = 3306

DataDir =/data/mysql/data/mysql

Tmpdir =/tmp

Lc-messages-dir =/usr/share/mysql

# avoid MySQL external lock, reduce the chance of error increase stability

Skip-external-locking

# set up the MySQL server's character set

Character-set-server = UTF8

# Default Storage Engine

Default-storage-engine = INNODB

# Instead of skip-networking The default is now-listen only on

# localhost which are more compatible and are not less secure.

# bind-address = 0.0.0.0

Skip-name-resolve

# The UDF custom function is useful if it needs to be opened. Subsequent memcached will use the UDF

Log_bin_trust_function_creators = 1

# using the event, you should open the

Event_scheduler = 1

# Error Log-should be very few entries.

# log_error =/data/mysql/log/error.log

Key_buffer = 16M

# MyISAM the size of the index buffer of the storage engine

Key_buffer_size = 16M

# buffer size that can be used by the union query operation

Join_buffer_size = 4M

# to control the maximum length of its communication buffer

Max_allowed_packet = 16M

# Specifies the maximum number of connection processes allowed by MySQL. If the too many connections error is frequently present, you need to increase the parameter value. The default value is 151, which is set to 500. Adjust according to the actual situation.

Max_connections = 500

# Maximum number of connection errors

Max_connect_errors = 10000

# Number of reusable threads to cache

Table_open_cache = 4096

# buffer size that can be used when querying the sort

Sort_buffer_size = 4M

# Each thread that makes a sequential scan allocates a buffer of this size for each table it scans, and increments the value if it does a lot of sequential scanning.

Read_buffer_size = 4M

# random Read cache, MySQL sort query, first scan this buffer to avoid disk search

Read_rnd_buffer_size = 8M

# Open Query Cache

Query_cache_type = 1

# Specify the size of the MySQL query buffer

Query_cache_size = 256M

# Single query can use buffer size

Query_cache_limit = 2M

# Specify the smallest unit of allocation buffer space, default is 4K

Query_cache_min_res_unit = 4K

# Memory table size, which is used to calculate the maximum number of rows in a memory table

Max_heap_table_size = 32M

# Specify the memory size of the MySQL cache, default 16M

Tmp_table_size = 32M

# How many requests can be piled up in a short time before a temporary stop in response to a new request, that is, if MySQL's connection data reaches Max_connections, the new request will be present in the stack waiting for a connection to release the resource.

# The number of the stack is Back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted. The Back_log value cannot exceed the size of the listening queue for TCP/IP connections. If the excess is not valid,

# View the size of the listening queue for the current system's TCP/IP connection command: Cat/proc/sys/net/ipv4/tcp_max_syn_backlog The current system is 1024. For Linux systems, the recommended setting is an integer less than 512.

# 256kb per connection, occupied: 125M

Back_log = 500

# Specifies the maximum connection time for a request, the time-out period, to avoid attacks. A server with about 4GB of memory can be set to 5-10

Wait_timeout = 604800

# Number of seconds the server waits for action on an interactive connection before shutting it down

Interactive_timeout = 604800

# This parameter value is the number of server logical CPUs x2, default 8

# This parameter was discarded after version 5.6.1

Thread_concurrency = 16

Thread_stack = 512K

Thread_cache_size = 64

Myisam-recover = BACKUP

# Record Slow query statements, paths based on actual

Log_slow_queries =/data/mysql/log/mysql-slow.log

# Set the time for slow queries

Long_query_time = 2

# Slow queries that record unused indexes

Log-queries-not-using-indexes

# Turn on binary log earcs-bin for log file prefix basename

Log_bin =/data/mysql/log/earcs-bin.log

# The Set log is valid for 10 days.

Expire_logs_days = 2

Max_binlog_size = 100M

# Number of open files

Open_files_limit = 10240

# Control the time when the log is flushed to the hard disk

Innodb_flush_log_at_trx_commit = 0

# on means to enable single-table space, reduce shared tablespace maintenance costs, and reduce the pressure on free disk space release

innodb_file_per_table = 1

# SET TRANSACTION ISOLATION LEVEL

Transaction-isolation = read-committed

# Binary Log format

Binlog-format = MIXED

# official recommendation in a 32-bit system, to set less than 2G.

Innodb_buffer_pool_size = 10G

# Control the time when the log is flushed to the hard disk

Innodb_flush_log_at_trx_commit = 0

# Dynamic adjustment is required based on the frequency of the write load and the number of large transactions.

Innodb_log_buffer_size = 8M

# The size of each log file in the log group

Innodb_log_file_size = 512M

# This parameter will be deprecated after the 5.6.3 version.

Innodb_additional_mem_pool_size = 100M

# Increase MySQL speed, prohibit DNS caching

Skip-host-cache

[Mysqld_safe]

Log-error=/data/mysql/log/mysqld.log

Pid-file=/data/mysqld/mysqld.pid

OpenFire Server MySQL Optimization