Scheduled Downtime: Database performance optimizations

2 posts / 0 new
Last post
puravida's picture
puravida
Jedi Warrior
Offline
Joined: 09/01/2007
Visit puravida's Website

I have been on a tear to make service improvements, enhance security, optimize code for performance, and fix long-time (obscure) bugs/issues over the past couple of months.

Tonight, I've been monitoring database performance, especially since I've noticed a severe increase in time to perform lookups in the past few days. I have discovered that our ex-server guy did not properly enable query caching and also did not allow enough RAM usage. So, I am going to make an update and restart the DB in 1 hour, at 00:30 (GMT -0500). These changes and restarting the DB may only take 1-2 minutes, but I am also going to add a couple of indexes to our largest table with over 100 million rows, so that could take up to 30 minutes or more (hoping around 15 minutes).

I may make other updates, once I monitor how these changes are performing.

In considering those users to be affected, I noticed that the system had a burst of about 10 million more requests than normal over a 5 minute period about 30 minutes ago. However, that is unusual and has passed. The service is at a low point of usage currently, so this is likely the best time to perform these updates.

I will make a follow-up announcement on our Twitter feed once the service is back online. Thank you all for your patience and understanding!

puravida's picture
puravida
Jedi Warrior
Offline
Joined: 09/01/2007
Visit puravida's Website

Update: Surprisingly, the performance optimizations did not cause the DB to become unresponsive at any point. So, the only downtime was a 45-second restart to activate configuration changes. I am still working on tweaking the optimizations but do not expect any service impact or downtime.

ShrinkTheWeb® (About STW) is another innovation by Neosys Consulting
Contact Us | PagePix Benefits | Learn More | STW Forums | Our Partners | Privacy Policy | Terms of Use

Announcing Javvy, the best crypto exchange and wallet solution (coming soon!)

©2018 ShrinkTheWeb. All rights reserved. ShrinkTheWeb is a registered trademark of ShrinkTheWeb.