Configuring MySQL server for large amount of queries

user_6526142 0
Viewed 268

I have deployed 64 core 300 GB+ RAM Amazon server and installed virtualmin on it. This parent server is to be used as a database server. It stores a Laravel jobs queue with more than 2,000,000 jobs.

I am having trouble configuring MySql server in the parent server . Currently the parent server is connected to AWS Auto Scaling Group with many child servers. The child servers in Scaling Group read data from parent server, process the data and store the result back in parent server. Each server, on average, completes 3 jobs from the parent server.

I want to connect about 1000 child server. The problem arises when there are more than 500 server connected. At that time, the MYSql server in parent server becomes very slow. The child server receive data very slowly. It doesnt crash or give connection limit error.

I have tried various variable settings and increased limits, but so far I am unable to solve the issue, My current /etc/my.cnf config is as below:

symbolic-links=0
innodb_file_per_table = 1
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 32M
net_buffer_length = 12M
read_buffer_size = 128M
sort_buffer_size = 128M
table_open_cache = 64
max_allowed_packet = 5M
key_buffer_size = 512M
max_connections = 100000
innodb_buffer_pool_size=64G
tmp_table_size= 4095M
max_heap_table_size = 20G

Rest of the settings are default.

mysql database
1 Answers
myisam_sort_buffer_size = 64M
key_buffer_size = 512M

These settings are for MyISAM only. You shouldn't be using MyISAM. It's not the '90s any more.

table_open_cache = 64

This is absurdly low. It is almost certainly why everything grinds to a halt when you have many machines connected.

tmp_table_size= 4095M
max_heap_table_size = 20G

Are you really going to have temporary tables of anywhere near that size?

innodb_buffer_pool_size=64G

How big is your data? If you are anywhere near justifying 300GB of RAM, I presume you must have about a terabyte of data. Unless you have much less data than the server size would imply, this should probably be more like 250GB (and tmp and heap table sizes should be much smaller).

sort_buffer_size = 128M

This is absurdly high. sort_buffer_size is one of many settings that you almost certainly shouldn't be touching.

If you aren't 100% sure you know what each setting does, you should leave it at defaults. What you have here is guaranteed to completely cripple your immensely powerful server.

user_18426770
Related