Problems with mysql when migrating a domain to CWP

[JosePP]

Hello!

I have migrated a domain to a CWP server, the case is that it is a "rather old and badly built" client website.

Right now the client does not want us to rebuild the website. This is because on their current server the web works quite well, but at the moment it is moved to CWP, the web is very slow and I think it gets stuck in the database queries. The queries are not done very well and the tables between all those that are usually consulted have a total of approximately 50 million.

The fact is that on your current server it works normally. So we have had to go back again.

Do you know where the problem could be? Could it be the version of the database? On your current server you have this:

https://i.gyazo.com/2448a37ccaad8db31de78da906e3d835.png

Could I install CWP with myql version 5.5 for testing purposes? How should I do it?

BD: https://i.gyazo.com/c9ed7a77d48156c7ef9527c476c2e48b.png

[sandeep]

@josepp you need to optimize mysql config and try to use mariadb 10.2

try optimizing this value this is for 1 gb ram you can increase it according to your needs.

[mysqld]
symbolic-links=0
skip-external-locking
key_buffer_size = 32K
max_allowed_packet = 4M
table_open_cache = 8
sort_buffer_size = 128K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
thread_stack = 480K
innodb_file_per_table
max_connections=100
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5

[JosePP]

@sandeep Perfect. The server is 16GB of ram, how would it be suitable for that amount then?

Greetings!

[sandeep]

@josepp okay i'll provide the config is there any swap available ?

[JosePP]

@sandeep No, this particular server has no swap memory assigned.

[sandeep]

@josepp here you go :

[mysqld]
max_connections = 6000
max_user_connections=300
innodb_lock_wait_timeout=120
max_heap_table_size = 1G
tmp_table_size = 1024M
thread_cache_size = 128
innodb_buffer_pool_size = 1G
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 4
table_open_cache = 3000
wait_timeout=110
interactive_timeout=110
long_query_time=25
max_allowed_packet=128M

[JosePP]

@sandeep Hello!

I'm back with news!

After trying all these days, it seems that the web is working properly.

Thank you very much from your fans! P hahahaha

[JosePP]

Hello again @Sandeep!

For a server with these features, what configuration do you recommend for mysql?

8GB Ram
2GB Swap Memory

Greetings!

[sandeep]

@josepp you can use the same config and it will work like charm