Linux – How to config thesql-server for heavy load

configurationdebianlinuxMySQL

Im in the process of setting up a new database server. I have been running a few mysql database servers before and it has been working okay.

But i would like to hear the recommended setup for my server. For example, what should i set the max connection, query_cache_size, table_cache and so on.

I have arround 4-600 per second:

Open tables: 112 Queries per second avg: 430.386.

The server i am setting it up on have the following configuration:

Linux version 2.6.32-5-amd64 (Debian 2.6.32-41squeeze2)

2x Intel Xeon X3440 @ 2.53GHz

4GB Ram

/, /boot, /tmp etc on Software RAID1, 2x 7200RPM SATA

Data location on Software RAID0, 2x7200RPM SATA

Im am going to place the mysql databases on the RAID0.

Am im missing anything? Let me know!

Thanks in advance, im looking forward to hearing from you 🙂

/Rasmus

Best Answer

it's impossible to give any good answer without knowing the type of workload, amount of data and usage patterns you have. honestly even with some knowledge of those at the end you'll have to find out by yourself what works best. that means lot's of benchmarking, tracking what goes on inside, regular checking of the slow query logs etc.

mysql's default settings are not really optimal for the modern hardware, as a reasonable starting point you can use values proposed by percona's mysql configuration wizard or mysqltuner.pl.

most probably inodb as a storage engine will be more optimal in your case. only 4GB of memory is quite little [but again i dont know what's the total size of your data set]. 2x Xeon X3440 is a lot in terms of the cpu power, you'd better have a lot of concurrent traffic and be able to fit all the data in memory, otherwise you'll not make much use of available processors just waiting for the disks to read/write the data.

side note: data on raid0? i hope you do have hot spare server with real-time replication or that the data is not important at all.

Related Solutions

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

No, there isn't (I just checked 1 hour ago). You can comment the bind-address in my.cnf:

Note: « 1 hour ago » is now more than 10 years ago.

#skip-networking
#bind-address                   = 127.0.0.1

If you want only 2 IPs, you will then have to use a firewall.

For MySql version 8.0.13 and above, you can specify a list of comma-separated IP addresses.

bind-address = 10.0.0.1,10.0.1.1,10.0.2.1

Relevant MySql documentation.

Remember to restart your MySQL instance after changing the config file.

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Best Answer

Related Solutions

MySQL Configuration – How to Bind MySQL Server to Multiple IP Addresses

MySQL DB causing high IO running Zabbix server

Related Topic