So database administration is not a strong point of mine, so I have a few questions.
This all relates to the performance of a website I'm looking at, and have spent a fair bit of time researching/diagnosing.
- There are two sites, a test site and a live site.
I have noticed page load times are much longer on the live site as opposed to the test site.
The specs of the test site are really low - VPS 2 Virtual processors with 14GB RAM and SQL + website running on the same VPS
The specs of the live website are 4 dedicated servers all with 8 virtual processors and 16GB RAM
The live site uses a separate dedicated server for SQL with 16 virtual processors and 112GB RAM
The website averages 120 concurrent users average at any one time but can fluctuate from 70 to 150 and when we send out an email campaign it can hit 400. When we hit 400 the cpus as you would expect can hit 100% depending on what actions users are doing on the website, but they hold up fairly well same with the RAM.
I would expect the live website to actually perform faster than the test website, but this is not the case. I have looked through the code but I strongly believe it's due to the database. The database size of the live website is over 50GB when backed up, and the test site is 4GB.
Both databases are quite fragmented, and I'm wondering how likely the fragmentation will affect performance?
Also because the live website is many times larger than the test site, will high fragmentation affect the performance of the live site much more than the test site?
Because I have never dealt with this before what are some tips around rebuilding/refreshing indexes.
Should I put the website into maintenance mode so no-one can interact with tables while I'm rebuilding/refreshing indexes?
How long would it take on a table with 12 indexes and 40000 products?