Need help: PERFORMANCE_SCHEMA suggestion for my VPS server.

[deslocotoco]

Hello guys!

I'm needing some help to better handling my VPS Server on my XenForo site. Unfortunately is a service that, in theory, is managed by the staff, but i don't seeing a very good performance lately, specially with clean tasks, prune users, and that kind of stuff, going to a point to close the board temporarily until all queries finish.

I don't have the proper skills to deal with server side issues, but for what I'm reading, i can configure a PERFORMANCE_SCHEMA on my server (is turned off right now).

So, that's my php info:

PHP version
 7.4.22
MySQL version
 10.4.21 (10.4.21-MariaDB)
Server software
 Apache/2.4.39 (Unix) OpenSSL/1.0.2
PHP memory_limit
 200M
PHP post_max_size
 20M
PHP upload_max_filesize
 20M
PHP max_input_vars
 10000
PHP max_execution_time
 120
cURL version
 7.61.1
cURL SSL version
 OpenSSL/1.1.1g
OpenSSL version
 OpenSSL 1.1.1g FIPS 21 Apr 2020
Suhosin enabled
 No
Imagick support
 Yes
EXIF support
 Yes
GZip support
 Yes
mbstring support
 Yes
gmp support
 Yes
ZipArchive support
 Yes

My server hardware is this:

CPU: AMD Epyc 7351P
Cores: 6
RAM: 8 GB
Disk: 250 GB
2 IPV4
Link: 100Mb/s
Free SSL
Unlimited Band

And, accessing my phpMyAdmin, i have this settings for PERFORMANCE_SCHEMA:

performance_schema OFF
 performance_schema_accounts_size -1
 performance_schema_digests_size -1
 performance_schema_events_stages_history_long_size -1
 performance_schema_events_stages_history_size -1
 performance_schema_events_statements_history_long_size -1
 performance_schema_events_statements_history_size -1
 performance_schema_events_waits_history_long_size -1
 performance_schema_events_waits_history_size -1
 performance_schema_hosts_size -1
 performance_schema_max_cond_classes 90
 performance_schema_max_cond_instances -1
 performance_schema_max_digest_length 1024
 performance_schema_max_file_classes 50
 performance_schema_max_file_handles 32768
 performance_schema_max_file_instances -1
 performance_schema_max_mutex_classes 200
 performance_schema_max_mutex_instances -1
 performance_schema_max_rwlock_classes 40
 performance_schema_max_rwlock_instances -1
 performance_schema_max_socket_classes 10
 performance_schema_max_socket_instances -1
 performance_schema_max_stage_classes 160
 performance_schema_max_statement_classes 202
 performance_schema_max_table_handles -1
 performance_schema_max_table_instances -1
 performance_schema_max_thread_classes 50
 performance_schema_max_thread_instances -1
 performance_schema_session_connect_attrs_size -1
 performance_schema_setup_actors_size 100
 performance_schema_setup_objects_size 100
 performance_schema_users_size -1

My question is, can anybody can 'optimize' this configuration, so that i can contact my server administrator to apply a new configuration?

This situation is really annoying me, since most of the time when running jobs and stuff, the site practically go down, giving CloudFlare GateWay errors, timeouts and that kind of thing.

So, thanks guys. I really appreciate if anybody can help me.

 

[briansol]

if you're good at the command line, install this and make some of the recommendations

GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl

github.com

 

[deslocotoco]

I'm trying to figure out what is happening in phpMyAdmin and see this "Advisor" tab on Monitor, supposedly offering suggestions to improve the performance.

My hosting company (and no way to change, financial costs are to high to set up a busy Forum like mine) is not helping to much.



This information is valid? How i can apply all that?