Causes for high load

L

last1

I need some help troubleshooting random high load on some servers.

I have 4 MySQL servers - one source, 3 replicas, that seemingly @ random all generate very high load 80+ for a few minutes.
Now, I know that load is a very imprecise term and usually it just shows the amount of wait required to access a resource.

I'm just trying to figure out where the bottleneck is.
When the load is at 80 for example, CPUs are 99% idle, drives are not used at all ( checked with iostat -c 20 , gstat ), ram has 20Gb free.

The mysql process itself gets sort of hung and connections accumulate but I can't determine why.

What else can I check besides CPU/disk/ram that might be causing this ?
 
Anything in the MySQL slow logs or other logs?

Any patterns before this e,g, lots of writes or lots of traffic?

What is the hardware setup especially storage? I’m thinking of caches being written out and everything has to wait for that.

Going to be a question of turning up logging and watching closely to see if you can spot the workload that triggers this.

What version of MySQL and FreeBSD? amd64?
 
richardtoohey2 said:
Good point and I think you can see that sort of information in the replication status screens so another thing to look at.
Click to expand...

These are pretty beefy servers with 64Gb of RAM, I was doing show full processlist while it was happening but I didn't see any table lock or something to that effect. The connections were however maxed out at 900/900 but I couldn't figure out what they were doing because show full processlist was showing just 200 processes.

I will run a show slave status next time it happens to see if that gives any clue
 
richardtoohey2 said:
Good point and I think you can see that sort of information in the replication status screens so another thing to look at.
Click to expand...
I might add that this happens daily around 3AM. I thought it might be the periodic scripts but I disabled all that I could think of

Code: 
cat /etc/periodic.conf
daily_backup_aliases_enable="NO"
daily_backup_pkg_enable="NO"
daily_backup_pkgdb_enable="NO"
daily_clean_preserve_enable="NO"
daily_clean_rwho_enable="NO"
daily_status_pkg_changes_enable="NO"
daily_status_security_chkmounts_enable="NO"
daily_status_security_chkportsum_enable="NO"
daily_status_security_chksetuid_enable="NO"
daily_status_security_enable="NO"
daily_status_security_ipfdenied_enable="NO"
daily_status_security_ipfwdenied_enable="NO"
daily_status_security_neggrpperm_enable="NO"
daily_status_security_pfdenied_enable="NO"
daily_status_security_pkg_checksum_enable="NO"
daily_status_security_pkgaudit_enable="NO"
monthly_accounting_enable="NO"
monthly_statistics_enable="NO"
monthly_statistics_report_devices="NO"
monthly_status_security_enable="NO"
weekly_locate_enable="NO"
weekly_noid_enable="NO"
weekly_status_pkg_enable="NO"
weekly_status_pkg_enable="NO"
weekly_status_security_enable="NO"
weekly_whatis_enable="NO"
 
last1 said:
I might add that this happens daily around 3AM.
Click to expand...
That should be very useful - definitely points at something happening at 3 a.m.

When I was trying to track down swap/RAM issues I had something like this running in my root cron:

@every_minute date >> /tmp/swap_usage.txt && top -n 100 >> /tmp/swap_usage.txt && pstat -sh >> /tmp/swap_usage.txt

So something like that on primary/secondary servers (but more MySQL-orientated) to see if anything is running between say 2 a.m. and 3.30 a.m.

Any mysqldump backup running on the primary around that time? Any summary reporting?

Network connections table around this time?

Could you make a cron job to turn off replication briefly around 3 a.m.? Not sure that's doable/feasible but if you could try it and it shifts to happening at 3.05 a.m. then replication a suspect.
 
You might want to limit ARC and recheck the memory (buffer pools and related) MySQL uses (databases/mysqltuner could be useful). Both MySQL and ZFS' ARC could be fighting over memory, which typically tanks the overall performance.
 
SirDice said:
You might want to limit ARC and recheck the memory (buffer pools and related) MySQL uses (databases/mysqltuner could be useful). Both MySQL and ZFS' ARC could be fighting over memory, which typically tanks the overall performance.
Click to expand...

I believe this was what was happening. The ZFS SQL Replica was getting hit with performance due to ARC. ProxySQL was taking it offline and the remaining servers couldn't handle the load so they were all going crazy.

I limited ARC to 1GB ( vs no limit before ) and the issue appears to have gone away.

Thank you all.
 
last1 said:
Is it not recommended to run MySQL on ZFS ?
Click to expand...
Should work just fine, but as you found out you need to limit ARC so ZFS and MySQL don't start fighting each other for the same memory.
 
There's a lot more than that. When you introduce a new cache at filesystem level to a SQL which is also trying to cache it's data or when your db engine page size doesn't match or it's lower than the record size of the file system you are starting to see performance degradations. It's similar when you are using hardware based raid and optimizing the stripe size of the raid with the page size of the database. For example InnoDB has page size of 16384 which is ok when you are using it on normal 4k disk but if you have 128K record size at the storage level then you will see a big lost of performance. Search for official MySQL Innodb ZFS Best Practices and read more about this topic.

Oracle Solaris 11.4 Tunable Parameters Reference Manual

docs.oracle.com docs.oracle.com

Planet MySQL :: Planet MySQL - Archives - MySQL Innodb ZFS Best Practices

planet.mysql.com planet.mysql.com
 
You must log in or register to reply here.
Back
Top