Ghan
Network/Server Admin
So, I'm a dumb.
If you recall from this earlier thread, you know that we've been having some issues with site load times and the server has been somewhat struggling of late:
http://www.rpnation.com/threads/about-recent-downtime-and-slowness.62595/
Well, the downtime part was quite straightforward. I was able to verify in the logs why that was happening and have for the most part put a stop to that. The trickier part was figuring out why we were getting slow loading times for a number of users. I've been working on that problem on and off since that other thread, so about 3 weeks, trying to diagnose the source of our loading issues. I think now I have it solved, though. It was quite a simple setting for MySQL (the database engine) that I thought was enabled and working properly in the configuration file, but as it turns out, it wasn't. This is probably because somewhere in a new version of MySQL, they changed the syntax needed to properly set up and turn on this feature, and I didn't realize that I needed to change my configuration file.
The feature is the MySQL Query Cache. What does it do? It takes the result of database queries and places them in a cache in memory. If the same query comes in to the engine later, then it doesn't have to re-execute the query against the database. It just pulls the result from the cache and returns it. This only works on reading - when you post, this has no effect, because that is data being written into the database. When new data is written, then the engine is very smart. It removes any queries in the cache that are now old or "stale" as a result of the newly written data and won't return bad results for later reads.
Now, if you were having issues with load times when posting, this doesn't necessarily mean that will continue, either. The query cache doesn't help inserting data, but it can help with other backend queries. Each time you load a page, there are actually many queries (probably 20-30) that get executed, and even when posting, several of those will be reads. In addition, the cache takes some load off the database engine overall, meaning that instead of having to deal with a ton of reads and a few writes, it can focus more on the writes and hopefully get those done more quickly just due to less overhead.
Now that was a technical jumble. If you're not interested in the details, don't worry about it. What I would like to know now is if anyone actually notices that the site loads faster and generally feels snappier. I know I've noticed it - right away after making the configuration change and restarting the database last night. Let me know if you still get slowness and if so, where and when. I'm more interested in persistent slowness - one page load out of 20 that was a bit slower isn't so much a concern. That kind of thing may happen for any number of reasons.
Thanks all, and have a good one.
Ghan
If you recall from this earlier thread, you know that we've been having some issues with site load times and the server has been somewhat struggling of late:
http://www.rpnation.com/threads/about-recent-downtime-and-slowness.62595/
Well, the downtime part was quite straightforward. I was able to verify in the logs why that was happening and have for the most part put a stop to that. The trickier part was figuring out why we were getting slow loading times for a number of users. I've been working on that problem on and off since that other thread, so about 3 weeks, trying to diagnose the source of our loading issues. I think now I have it solved, though. It was quite a simple setting for MySQL (the database engine) that I thought was enabled and working properly in the configuration file, but as it turns out, it wasn't. This is probably because somewhere in a new version of MySQL, they changed the syntax needed to properly set up and turn on this feature, and I didn't realize that I needed to change my configuration file.
The feature is the MySQL Query Cache. What does it do? It takes the result of database queries and places them in a cache in memory. If the same query comes in to the engine later, then it doesn't have to re-execute the query against the database. It just pulls the result from the cache and returns it. This only works on reading - when you post, this has no effect, because that is data being written into the database. When new data is written, then the engine is very smart. It removes any queries in the cache that are now old or "stale" as a result of the newly written data and won't return bad results for later reads.
Now, if you were having issues with load times when posting, this doesn't necessarily mean that will continue, either. The query cache doesn't help inserting data, but it can help with other backend queries. Each time you load a page, there are actually many queries (probably 20-30) that get executed, and even when posting, several of those will be reads. In addition, the cache takes some load off the database engine overall, meaning that instead of having to deal with a ton of reads and a few writes, it can focus more on the writes and hopefully get those done more quickly just due to less overhead.
Now that was a technical jumble. If you're not interested in the details, don't worry about it. What I would like to know now is if anyone actually notices that the site loads faster and generally feels snappier. I know I've noticed it - right away after making the configuration change and restarting the database last night. Let me know if you still get slowness and if so, where and when. I'm more interested in persistent slowness - one page load out of 20 that was a bit slower isn't so much a concern. That kind of thing may happen for any number of reasons.
Thanks all, and have a good one.
Ghan
