Mysql Server tuning

mrmartin · December 30, 2020, 4:18pm

Hello, I am going to switch my mysql server configuration, and
I’d like to know what do you think about this configuration.

I have 2 servers, one (apache2, php5) and the otherone mysql (mysql-server 5.0.38, last debian version).

The mysqlserver specifications are.

3 disks 72GB SAS 15,000 rpm RAID 5
2 Dual Core Intel® Xeon® (2.66GHz, 1333 FSB)
4GB DIMM PC2-5300 (I know… is too low ( )

All tables are Innodb, and database have arround 90M now, but it will grow exponentially. Now have the “standar” mysql configuration and I ll change for this one.

[client]port = 3306socket = /var/run/mysqld/mysqld.sock[mysqld]port = 3306bind-address = 192.168.1.2socket = /var/run/mysqld/mysqld.sockskip-lockingkey_buffer = 2048Mmax_allowed_packet = 2Mmax_connections = 800table_cache = 5600open_files_limit = 6000sort_buffer_size = 4Mread_buffer_size = 4Mread_rnd_buffer_size = 8Mthread_cache_size = 16query_cache_size = 32Mthread_concurrency = 8skip-name-resolvelog-bin=mysql-bininnodb_data_home_dir = /var/lib/mysql/innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextendinnodb_log_group_home_dir = /var/lib/mysql/innodb_log_arch_dir = /var/lib/mysql/innodb_buffer_pool_size = 2048Minnodb_additional_mem_pool_size = 20Minnodb_log_file_size = 500Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash

Thanks !

Martin

Speeple · December 30, 2020, 4:27pm

Everything looks ok execept:

key_buffer = 2048M

You say it’s a innodb heavy server, therefore lower key_buffer to 16/32Mb

mrmartin · December 30, 2020, 4:27pm

Oks… key_buffer is only for myisam index… so 32mb should be fine.

Thanks for your post!

any other comment?

Speeple · December 30, 2020, 4:27pm

Max connections could be lowered, is there any reason that it is set so high?

Thread level variables would consume a lot of memory with 800 concurrent connections.

Might as well safe guard against that issue by lowering max connections with a more real world value.

mrmartin · December 30, 2020, 4:27pm

oks… I talk with the dev-team and we think 600 should be enough

400 - apache
600 - mysql (there are some batch processes that will take up 50 connections max)

Should I have to change any other variable if I change the max_connections to 600?

Thanks.

mrmartin · December 30, 2020, 4:27pm

Hllo! I try this configuration and I get this errors:

Jun 22 06:58:39 host1 mysqld_safe[9523]: startedJun 22 06:58:39 host1 mysqld[9526]: InnoDB: Error: data file /var/lib/mysql/ibdata1 is of a different size

and

Jun 22 07:00:57 host1 mysqld[9847]: InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytesJun 22 07:00:57 host1 mysqld[9847]: InnoDB: than specified in the .cnf file 0 524288000 bytes!

The solution was remove this lines (leave the default values).

innodb_log_file_size = 500M
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

Any idea how can I increment this values without server crashing?

Thanks !

Speeple · December 30, 2020, 4:27pm

You’re going to have to backup your databases to change the innodb_data_file_path variable. Backup, then change the variable, then import backup.

Altering the innodb_log_file_size can be achieved without backing up. What you need to do is stop the mysql instance, alter the config to the sizes you desire, then you need to remove the old log files:

rm -f ib_logfile*