Help on DB optimization

[macthai]

Dear all,

I am a new member but a lot of years torturing the great guys of cPanel support.

I bought a new server after a mess with the previous one that lasted for.. 1 month.. every db crashed and lost all sites (hopefully had the backups of the previous so saved with 1 month loss of new users).

I have a dedicated with the following specs:
Intel® Xeon® E-2176G Hexa-Core
incl. Hyper-Threading Technology
64 GB DDR4 ECC RAM

and I am looking to optimize the DB.

Now the DB my.cnf is :


[mysqld]
log-error=/var/lib/mysql/yindee.plmservers.com.err
performance-schema=0
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
innodb_buffer_pool_size=134217728


I am thinking to add the following config, only by researching on the internet:

default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
max_allowed_packet=268435456
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 4M
join_buffer_size = 128Μ
tmp_table_size = 24M
table_open_cache = 3000
innodb_buffer_pool_size = 2G
innodb_log_file_size = 32M
innodb_buffer_pool_instances = 1
open_files_limit=15000
thread_cache_size = 16K
low_priority_updates=1
concurrent_insert=ALWAYS
tmp_table_size = 128M
max_heap_table_size = 128M
key_buffer=64M
long_query_time = 5
join_buffer_size = 4M

I have no idea of most of them; but i am trying to make the sites much faster.

I have several dating sites (oxwall) and wordpress sites on the server and there are many sql queries.

What is your suggestion?? Can you please kindly help?

I understand that there are tools to run but i'm not sure how and what.. so at least let's try to improve it.

Usually I have around 40 users online together from all sites but I would prefer to make it as faster as possible keeping 10000%% SAFE that will NOT corrupt any db (as proven its happening some times..

Many thanks!
Mac

 

[rackaid]

Tuning MySQL is very complicated. So before you start applying changes, I recommend you collect some data.

Try logging slow queries:

slow_query_log = 1
long_query_time = 5
slow_query_log_file = /var/log/mysql/slow-query.log

MariaDB even has a tool to help summarize the data.

mysqldumpslow

Display data from the slow query log.

mariadb.com

Start with a 5s time and work down to 3 and then just 1s. If you are not seeing any or few queries, tuning your DB will likely not yield significant improvements.

If you do see slow queries, then set the index buffer for the table type in use.

If you are indeed using MySIAM, then see:

Optimizing key_buffer_size

Optimizing index buffers with key_buffer_size

mariadb.com

Also, if using MyISAM and your app is mostly read (as are most websites), then you actually want to enable the Query Cache.

query_cache_size = 32M
query_cache_type = 1
query_cache_limit = 4M

These are just estimates.

I cannot recall how many server optimization jobs I've had where the server was crashing because someone set insane values in MySQL. Often not realizing that some memory settings are per-connection. I once had a case, where the total possible RAM that MySQL could use was in the Terabytes.

When I tune MySQL, I start with some baselines and then work from there. Tools like mysqltuner, mysqlreporter, etc don't understand your workloads. They are best used to collect stats and then use these stats to tune the right variables.

You may be surprised to find that MySQL is not the bottleneck. Historically, slow SATA disks meant poor disk IO, especially at high concurrency. With modern SSD/NVMe, disk IO is far less of a problem. Combine this with the current versions of MySQL/MariaDB, the likelihood that the database is responsible for your website's performance woes is low.

 

[cPanelLauren]

Hello,

Database optimization is primarily a System Administration task, but I can suggest the following tool major/MySQLTuner-perl

Others like @rackaid may also have their own suggestions that they're free to add here as well.

 

[macthai]

Hi to all and many thanks for answering!
The sites are dating sites, so there is much read AND write! (messages, configs of each users, etc, plus the much read )...

Can you help me to start from somewhere please???? What i need to do to gather this info you asked me? Just type
mysqldumpslow ?

What are the parameters I need to gather the info you requested?

Last but not least, the sites some time have too much people, sometimes zero. It's not stable in users.

Many thanks!!