Seeker-Smith
Well-known member
Recently I've noticed a slow down on my site. Should I be averaging such high load on mysql?
Issues with high load
- Thread starter Seeker-Smith
- Start date
bzcomputers
Well-known member
Going to need to expand on that...
1) Is the XF installation the only site using MariaDB on the server?
2) How many concurrent users are you showing on the site when your are seeing this load (30%+)?
It does seem high. Have you tried restarting the Database Server? Something could be hanging causing a load increase.
Another thing to check is 3rd party add-ons, if you have a bad query or two in a poorly written add-on you can see loads jump quickly especially on a busy site. You can try disabling all add-ons, or add-ons one-by-one to find the possible issue.
1) Is the XF installation the only site using MariaDB on the server?
2) How many concurrent users are you showing on the site when your are seeing this load (30%+)?
It does seem high. Have you tried restarting the Database Server? Something could be hanging causing a load increase.
Another thing to check is 3rd party add-ons, if you have a bad query or two in a poorly written add-on you can see loads jump quickly especially on a busy site. You can try disabling all add-ons, or add-ons one-by-one to find the possible issue.
MySiteGuy
Well-known member
With the decimal based load numbers like 0.94 that's not 94% load like you'd expect.
1 CPU core system: 1.0 = 100% load
8 CPU core system: 8.0 = 100% load
So a 0.94 load is actually 11.25% load and the CPU has plenty more capacity.
If the site(s) seems slow, with a load number that low, I'd look at disk I/O (I/O wait). If you're seeing more than about 5% there, that's probably the issue, and the CPU is spending too much time waiting on the drive.
Then narrow it down from there. Is most of the I/O from MySQL, a script/program reading/writing, or PHP opcache loads. And watch vmstat to see if swap file usage is causing it.
1 CPU core system: 1.0 = 100% load
8 CPU core system: 8.0 = 100% load
So a 0.94 load is actually 11.25% load and the CPU has plenty more capacity.
If the site(s) seems slow, with a load number that low, I'd look at disk I/O (I/O wait). If you're seeing more than about 5% there, that's probably the issue, and the CPU is spending too much time waiting on the drive.
Then narrow it down from there. Is most of the I/O from MySQL, a script/program reading/writing, or PHP opcache loads. And watch vmstat to see if swap file usage is causing it.
Last edited:
Seeker-Smith
Well-known member
I had two sites running but I have just shut down one which was used very little. Users 10 to 100 depending on time of day. Heavy with images and videos.
I did a system reboot after unloading the unused site and mysql is back to normal at 14%. So its likely that there is an addon running crazy and I think I know which one.
Problem two I have not been able to resolve in horrible looking time for my XFMG. This has only occurred since updating to 2.3.2 or .3. It loadss fine as a guest but only two small galleries are loaded. I'm experiencing 1 to 2 minutes. I'm mentioned this in another thread.
Seeker-Smith
Well-known member
I've sent you a DM about doing an update and having a look at what's going on.
bzcomputers
Well-known member
I believe he was just concerned with the MariaDB load (33%), not the CPU load (.94 / 8 = 12%). The MariaDB load is high for the amount of activity the server was seeing.
14% is better but does still seem a little high. I think part of the "high" load you are still seeing on the database is due to that model INTEL 5620 CPU you're using which was released way back in 2010, but at 14% you've definitely still got room to grow and for spikes in traffic.
I'd concentrate on the XFMG issue for now, get that fixed then revisit your server loads and make sure they are still within normal limits then.
Most likely not if the bottelneck are slow queries / bad query excution plans.
Really, before trying to fix a problem you need to investigate and find out the cause.
@Seeker-Smith stated that guest performance is fine, so that most likely wouldn't help either (for the XFMG issue) - beside the fact that might not be able to use Litespeed / Open Lighespeed anyway.
MentaL
Well-known member
Then maybe enable; SET GLOBAL slow_query_log = 'ON';.
I would still recommend releem. It has done wonders for me.
They also have slow query log management.
MentaL
Well-known member
we so smart, lets toast.Already suggested (basically)
MentaL
Well-known member
Also, i should state, is the Xeons in a VM environment, if so , what? Some have custom firmware depending on the build. I noticed a huge performance boost using the appropriate vm image for my servers, especially with proxmox, HUGE margins of improvement. Maybe op can share his my.cnf file.
Mine by example;
and releem automated configs
fairly good stable levels.
I've got it virtualised though , so the webserver has its own vm and sql on its own vm. It works well, for me.
Mine by example;
Code:
[root@mental~]# nano /etc/my.cnf
GNU nano 5.6.1 /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
mysqlx=0
#Custom tweaks
sql_mode=STRICT_ALL_TABLES
event_scheduler=DISABLED
max_heap_table_size=33554432
bind_address = 192.168.0.3,127.0.0.1
!includedir /etc/mysql/releem.conf.dand releem automated configs
Code:
[mysqld]
innodb_change_buffering = changes ### Previous value : changes
innodb_change_buffer_max_size = 20 ### Previous value : 20
innodb_adaptive_flushing_lwm = 25 ### Previous value : 25
innodb_max_dirty_pages_pct = 70.000000 ### Previous value : 70.000000
innodb_autoextend_increment = 48 ### Previous value : 48
thread_stack = 524288 ### Previous value : 524288
thread_cache_size = 48 ### Previous value : 48
max_connections = 217 ### Previous value : 181
key_buffer_size = 3812622336 ### Previous value : 3812622336
max_heap_table_size = 33554432 ### Previous value : 33554432
tmp_table_size = 33554432 ### Previous value : 33554432
innodb_buffer_pool_instances = 20 ### Previous value : 20
innodb_buffer_pool_size = 21474836480 ### Previous value : 21474836480
innodb_redo_log_capacity = 348127232 ### Previous value : 301989888
myisam_sort_buffer_size = 369098665 ### Previous value : 369098665
innodb_page_cleaners = 20 ### Previous value : 20
innodb_buffer_pool_chunk_size = 134217728 ### Previous value : 134217728
join_buffer_size = 8388608 ### Previous value : 8388608
table_open_cache = 4096 ### Previous value : 4096
table_definition_cache = 4096 ### Previous value : 4096
innodb_flush_method = O_DIRECT ### Previous value : O_DIRECT
optimizer_search_depth = 0 ### Previous value : 0
innodb_flush_log_at_trx_commit = 0 ### Previous value : 0
long_query_time = 30.000000 ### Previous value : 30.000000
max_digest_length = 1024 ### Previous value : 1024
performance_schema_max_digest_length = 1024 ### Previous value : 1024
performance_schema_max_sql_text_length = 1024 ### Previous value : 1024fairly good stable levels.
I've got it virtualised though , so the webserver has its own vm and sql on its own vm. It works well, for me.
Seeker-Smith
Well-known member
Yes it's running on a VM. VMware EXi with a NFS share on a Synology RS2416RP+
Attachments
Similar threads
- Question
