I haven't been on the server much lately, but the few times I have been it seems to be pretty peppy.

Have the slow downs been fewer and further between lately (if happening at all)? I made a fairly significant change that should have helped and it seems like it has...at least the few times I've been on the site lately.

It has been a lot faster for me lately. Very few slowdowns, if any at all. Thanks for your efforts on this.

out of curiosity, what did you change?

phlipdascrip wrote:out of curiosity, what did you change?

I eliminated a query that was happening on every page load. The data that was being saved wasn't absolutely necessary.

It's good on my end. Matt your doing very well! Thanks for your support! A Few months it got real bad, but slowly it got better, get it... slowly! (Just had to say that for kicks and giggles)

Yep, I noticed that it was a lot faster most of the time. Now I can't get much real work done between posting pictures anymore !

Matt,

I posted several images for a route last evening, which is usually the "dreadfully slow" SP time, based on my experience.

Yesterday evening I experienced no delays - very fast.

Thanks!
Sarah

Everything seems to be running faster for me than it was a few weeks ago.

Thanks everyone for the feedback. I'm glad that things have been faster as of late. Hopefully things run smoothly for a while, but the database is still in need of a major change before too long.

Montana Matt wrote:I eliminated a query that was happening on every page load. The data that was being saved wasn't absolutely necessary.

just as incentives:
* did you check if access logs are being kept? this is an unnecessary disk I/O evil as well.
* since you mention an outdated DB, making sure proper keys and indexes are set on tables can make a huge difference on queries. are you keeping a slow query log?
* tweaking the last ms out of frequently run queries themselves can also make quite the difference.
* is there static (config..) data that you could move from the db to static code (e.g. arrays)? memcache is prolly too much work to set up properly.

if you checked those already, never mind - don't mean to be presumptuous

phlipdascrip wrote:* did you check if access logs are being kept? this is an unnecessary disk I/O evil as well.

Yes access logs are kept and getting rid of them probably isn't going to happen.

phlipdascrip wrote:since you mention an outdated DB, making sure proper keys and indexes are set on tables can make a huge difference on queries. are you keeping a slow query log?

It's not that the database is outdated. It's that it has grown so large that the storage engine we use for the main table isn't a good choice anymore. But yes, I've spent many, many hours analyzing the MySQL slow query log and added a few indices in places they were needed (in nearly all cases we already had indices being used correctly). I also removes unused indices, which helped quite a bit too.

phlipdascrip wrote:tweaking the last ms out of frequently run queries themselves can also make quite the difference.

I don't know what "ms" is in that sentence. But frequently run queries have been optimized quite well at this point.

phlipdascrip wrote:is there static (config..) data that you could move from the db to static code (e.g. arrays)? memcache is prolly too much work to set up properly.

That's a good idea and not one that I had considered before. Doing that could reduce the server load a bit, but the main problem right now is the storage engine. I think the main table is going to need to change from MyISAM to InnoDB because of the way locking occurs when tables are written to.

Montana Matt wrote:Yes access logs are kept and getting rid of them probably isn't going to happen.

why not? aren't error logs sufficient?

Montana Matt wrote:It's not that the database is outdated. It's that it has grown so large that the storage engine we use for the main table isn't a good choice anymore.

I see. is the page content stored in the DB? did you consider writing it to files instead? plain text or xml, for example (e.g. [object ID].xml and then nodes for the different page sections). wouldn't that reduce the table size by what 90%? easily?

Montana Matt wrote:

phlipdascrip wrote:tweaking the last ms out of frequently run queries themselves can also make quite the difference.

I don't know what "ms" is in that sentence. But frequently run queries have been optimized quite well at this point.

milliseconds.

phlipdascrip wrote:why not? aren't error logs sufficient?

The logs aren't heavily used, but we do use them from time to time to investigate abuse and potentially block IP blocks that are abusing the site.

phlipdascrip wrote:I see. is the page content stored in the DB? did you consider writing it to files instead? plain text or xml, for example (e.g. [object ID].xml and then nodes for the different page sections). wouldn't that reduce the table size by what 90%? easily?

Yeah, every page on the site is stored in the database and loaded dynamically upon request. We've not talked about storing page content directly in files. I'll think more on this, but I think reading from a file on the server would be slower than a DB query, right?

We have discussed some sort of caching scheme to reduce the number of queries, but the problem is that there are quite a few things on the pages that frequently change, such as votes/page score, parents, etc. But it's likely that some sort of caching will happen sometime in the future.

File access may be a bit slower than mysql, but for larger amounts of unindexed data my guess is the difference is not much. Of course XML parsing time comes on top of that, but with only few nodes inside each XML file I think this is negligible as well. The overhead put on mysql by storing those large amounts of data inside mysql however may be the bigger evil. To know for sure though you'd probably have to run some kind of benchmark.

Also not to forget the number of times you need to retrieve full page content is much smaller than querying for page properties (title, owner, rank etc.). You'd only need to access an XML file when a page is actually viewed.

Did you look at mysql's cache statistics and config as well (opened_tables, open_tables, table_cache etc.)? I remember we were able to improve performance by tweaking those values too.

Oh forgot, you could also set up memcache for caching just XML data. Frequently opened pages (e.g. featured on front page) would stay in the cache.