My.cnf Suggestions after Mysql 5.7 Upgrade

Idreams

Member
Hello,

Recently i have moved my site which contains Discussions: 200,340 , Messages: 2,898,916 , Members: 10,198 from VPS to a dedicated server
My Dedicated server has 32 Gb memory, 1 TB HD and Xeon E3 1240.

Recently i have upgraded Cpanel to latest version and in that process Mysql also got upgraded to version 5.7

I have the below my.cnf file during 5.6 version of mysql. can someone kindly suggest me edits to the my.cnf file to the latest version of mysql 5.7

I generally get 1000 users during peak time and about 400 to 500 users non peak time

[client]
port=3306

[mysqld_safe]
local-infile=0
#nice = -5

[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
performance-schema=0
port=3306
#myisam_max_sort_file_size=6341787648
#tmpdir=/var/mysqltmpdir
#log-slow-queries = /tmp/mysql-slow.log
#log-queries-not-using-indexes
#key_buffer=64M
max_allowed_packet=268435456
table_open_cache=2048
#sort_buffer=1M
net_buffer_length=8K
myisam_sort_buffer_size=8M
max_connections=1000
max_user_connections=1000
interactive_timeout=1000
wait_timeout=1000
connect_timeout=100
thread_cache_size=8
#thread_cache=8
#thread_concurrency=4
myisam_sort_buffer_size=64M
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
#join_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
#max_connect_errors=10
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=500
innodb_file_per_table=1
#skip-innodb
#skip-name-resolv
#skip-networking
open_files_limit=10000
innodb_buffer_pool_size= 24G
default-storage-engine=MyISAM
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=20M
sort_buffer=20M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout
 

Walter

Well-known member
innodb_buffer_pool_size seems too high, you are using 24 GB out of 32 GB RAM on your server for the database.
max_connections=1000 is too high, too.
 

MySiteGuy

Well-known member
If you're getting many simultaneous users, consider turning off the query cache. Only one thread at a time can access it, and if there are many active threads they can end up waiting on the query cache to become available. It was a great thing years ago, but the more threads your database is using the less useful it is, and can eventually be a bottleneck.

You also need to bind to the local IP (127.0.0.1) to prevent connection attempts from the outside world and make sure the firewall is blocking external 3306 port requests as well.
 

Idreams

Member
Below is my new my.cnf file. does this need any modifications ?

[client]
port=3306

[mysqld_safe]
local-infile=0
#nice = -5

[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
performance-schema=0
port=3306
#myisam_max_sort_file_size=6341787648
#tmpdir=/var/mysqltmpdir
#log-slow-queries = /tmp/mysql-slow.log
#log-queries-not-using-indexes
#key_buffer=64M
max_allowed_packet=268435456
table_open_cache=2048
#sort_buffer=1M
net_buffer_length=8K
myisam_sort_buffer_size=8M
max_connections=1000
max_user_connections=1000
interactive_timeout=1000
wait_timeout=14400
connect_timeout=100
thread_cache_size=100
#thread_cache=8
#thread_concurrency=4
myisam_sort_buffer_size=64M
query_cache_limit=2M
query_cache_size=0
query_cache_type=0
query_cache_type=OFF
#join_buffer=1M
sort_buffer_size=4M
read_rnd_buffer_size=4M
read_buffer_size=2M
#max_connect_errors=10
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=500
innodb_support_xa=ON
innodb_file_per_table=ON
#skip-innodb
#skip-name-resolv
#skip-networking
open_files_limit=50000
innodb_buffer_pool_size=16G
default-storage-engine=MyISAM
join_buffer_size=125M
tmp_table_size=4G
max_heap_table_size=4G
performance_schema = ON
key_buffer_size=2G
innodb_buffer_pool_instances=24
innodb_log_file_size=3G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
thread_cache_size=100
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_pct=75
innodb_flush_neighbors=1
innodb_page_cleaners=8
table_definition_cache=5500
table_open_cache_instances=16
thread_stack=256K
[mysqldump]
quick
max_allowed_packet=64M
[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=20M
sort_buffer=20M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout
 

MySiteGuy

Well-known member
You need to make sure you're firewalling incoming connections from the Internet on port 3306, since you are not binding mysql to the localhost IP address (or bind to 127.0.0.1).

Lots of issues with your my.cnf file, my changes and comments below

Code:
[client]
port=3306

[mysqld_safe]
local-infile=0
#nice = -5

[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
performance-schema=0
port=3306
#myisam_max_sort_file_size=6341787648
#tmpdir=/var/mysqltmpdir 
#log-slow-queries = /tmp/mysql-slow.log
#log-queries-not-using-indexes
#key_buffer=64M
max_allowed_packet=268435456
table_open_cache=2048
#sort_buffer=1M
net_buffer_length=8K
myisam_sort_buffer_size=8M
# max_connections and max_user_connections are very high, they shouldn't be more than a few hundred
max_connections=200
max_user_connections=200
# Too high, an interactive (command line) program shouldn't be waiting 1000 seconds between queries or something is wrong with it
interactive_timeout=300
# The timeouts below are too high, and can cause idle connections to use up the available connection pool
wait_timeout=60
connect_timeout=60
# Too high.   Make it at most number of CPU cores * 2
thread_cache_size=100
#thread_cache=8
#thread_concurrency=4
myisam_sort_buffer_size=64M
query_cache_limit=M
query_cache_size=0
query_cache_type=0
query_cache_type=OFF
#join_buffer=1M
sort_buffer_size=4M
read_rnd_buffer_size=4M
read_buffer_size=2M
#max_connect_errors=10
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=2
# 500 seconds is long time for a table to be locked, I'd lower this to about 60-120 seconds.
innodb_lock_wait_timeout=500
innodb_support_xa=ON
innodb_file_per_table=ON
#skip-innodb
#skip-name-resolv
#skip-networking
# Why such a high open files limit?  No need unless you have thousands of tables
open_files_limit=50000
# For a database your size, the buffer pool is too large.   The memory can be better used 
# by the operating system for other things.   Set it to the total size of all your Innodb tables plus 
# about 20%.   The 70% RAM rule you generally see talked about is when the databases exceed 
# the size of RAM.
innodb_buffer_pool_size=16G
default-storage-engine=MyISAM
# Too high.  Make is 4-8M
join_buffer_size=125M
# This is WAY too high.   Make it about 64M - 96M max
tmp_table_size=4G
# This is WAY too high.   Make it about 64M - 96M max
max_heap_table_size=4G
# Unless you're using tools which need performance_schama data, or know what to query manually
# to find bottlenecks, turn off performance_schema as it ats a little bit of overhead.
performance_schema = ON
# Why such a large key_buffer_size, are you running another app with large MYISAM tables?
key_buffer_size=2G
# You're likely not benefiting at all from 24 buffer pool instances on a VPS.  If it was a
# dedicated server where you had 12-24 cores available, then you'd want one buffer pool
# per every 2-4 cores (depending on load)
innodb_buffer_pool_instances=24
# I'd scale this down to about 1G-2G unless its an SSD VPS.  A large log file size on traditional
# harddrives is going to cause longer start/restart times.
innodb_log_file_size=3G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
# Make this the number of CPUs x 2
thread_cache_size=100
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_pct=75
innodb_flush_neighbors=1
innodb_page_cleaners=8
table_definition_cache=5500
table_open_cache_instances=16
# Don't fool around with thread stack unless you know what you're doing.
#thread_stack=256K
[mysqldump]
quick
max_allowed_packet=64M
[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=20M
sort_buffer=20M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=8M
write_buffer=8M

[mysqlhotcopy]
interactive-timeout

The changes I noted above are educated guesses. They can be fined tuned much better with access to the server and being able to query performance and buffer stats, but they are better than what you have.
 

Idreams

Member
Hello,

thanks a lot for the response.
i am using dedicated server Xeon E3 with 32 GB ram.

My forum will be having around 2000 to 2500 users every 15 mins. This is why i have kept max_user_connections as 1000. do you still want me to change it to 200 ?
 

MySiteGuy

Well-known member
Sorry, I misread the first post scanning it quickly and my mind saw "VPS".

I wouldn't set connections as high as 1000. Maybe 250-300. 1000 connections x buffer memory each uses, and it can quickly eat up memory. Rather than set max connections higher, raise the back_log value. This will allow MySQL to queue up connection requests without using resources full connections take. Set it to 100, and raise as need if you get connection errors. I'm running 3 big boards on a single dedicated server with 100 max connections and 100 back_log.

If your version of Mysql 5.7 is <= 5.7.6, add this line:
innodb_checksum_algorithm = crc32

CRC32 checksums are slightly faster than the default innodb checksums.
 
Here is a baseline configuration I use for MySQL 5.6+ (including MariaDB). This is for forums on dedicated servers with RAID and 16GB of RAM and about 10GB of data in InnoDB tables.

This is by no means an optimal configuration but a starting point. I've used this successfully on forums with more than 10M posts.

From here, I then work on tuning open tables, memory allocation, InnoDB variables, etc to get better performance. I always try to understand where the bottleneck is before moving away from this setup. The slow log is very helpful for this.

I've found many of the long, detailed my.cnf files online are old or overly specific.MySQL's InnoDB performance has become much better out of the box, so I don't change defaults unless I see a clear benefit for changing them. I would rather start with a simple setup and then use performance monitoring tools to make specific changes that I know work.

You mention the high max connections. In many cases this is PHP's fault not MySQL's. In cPanel, make sure you try to use PHP 7.x with PHP-FPM. The default CGI mode of PHP can cause lingering MySQL connections and push up concurrency. At high concurrency, nearly everything is slower.

I recommend using PHP 7.x with opcode cache via PHP-FPM. You can manage these settings in EasyApache and Multi-PHP Manager in cPanel.


MySQL my.cnf Starting Point for MySQL 5.6+ and Forums


Bash:
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB


# DATA STORAGE #
datadir                        = /var/lib/mysql/

#TIMEOUTS#
wait_timeout                    = 30


#NO DNS#

skip-name-resolve

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
max-connections                = 500

# CACHES AND LIMITS #
tmp-table-size                 = 64M
max-heap-table-size            = 64M
query-cache-type               = 0
query-cache-size               = 32M
max-connections                = 500
thread-cache-size              = 32
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 1024

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 10G
innodb_buffer_pool_instances = 4


#LOG QUERIES#
long_query_time                = 3
log_slow_queries               = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log
 
Top