Sudden random and giant sql reads that cause system freeze?
Any idea what would cause random sudden giant sql reads on the hdd? We'll be plugging along with as few as 1 register and occasionally 2 registers and a back office admin terminal and the system will suddenly freeze for 30 - 90 seconds for any and all users. The HDD status light on the server will be solid lit like a beacon. As soon as the HDD light goes back out everything returns to normal. It doesn't seem to be any one action causing it. I went into Resource Monitor and any time it does the freeze the mysql.exe "Read (B/sec)" will be spiked up to 40-60G.
Our database is somewhat large as we have probably 20k items with images for each stored. Backup exports result in a 2.7G export file. When the system is not frozen it is very responsive. It's running on a 6 month old Dell PowerEdge T30 with 32G of ram dedicated to just PHPPOS. CPU, ram, network, and drive usage is barely registering most of the time.
I have a Samsung 883 DCT Series SSD with MLC V-NAND that is the best I can find for heavy simultaneous read/write performance and will be changing over the drive this week but not sure if it will be any help for the giant read spikes I'm seeing. Any idea?
.
Comments
8 comments
PS I'm currently on build 17.1 and will be updating it to 17.4 when I do the new drive change.
I would recommend posting to dba.stackexchange.com and post your server specs and my.ini file
you might want to consider Linux also
I went ahead and changed out the hard drive first for the new Samsung 883 DCT and have not seen a freeze yet in several days so far. fingers crossed.
I think I found issue. It was garbage collector on app files. It gets triggered once out of 1000 queries. In latest 17.4 it has been fixed (phppos.com/downloads.php)
Awesome! so just replacing that file in my 17.4 build from the current download build should fix it?
yes
Thank you! I'll let you know
Wanted to give a good long test period. We have not seen the freezes since updating. Thanks!
Please sign in to leave a comment.