S9Y blocking the server

[Josh]

My webhost told me that very long running queries have blocked the shared MySQL-Server ("Die Überlastung ging durch folgende Ursachen hervor: Sehr langlaufende Queries blockieren den MySQL-Server"). In consequence, the webhost has changed the password to the database. This happened last Tuesday. I have restored access soon afterwards. No problem since.

I have asked the webhost for details regarding the long running queries. The tech support did not tell me which queries caused the problem. They only said that it could have been caused by simultaneous use of the search function, but that can't be the cause since my search function is powered by Google.

The tech support said that I should create an index for the database tables and linked to this page:
http://dev.mysql.com/doc/refman/4.1/en/ ... dexes.html

I don't understand how to create an index. Besides, a one time indexing does not help since the blog is constantly updating the database... Or am I misunderstanding something?

So my questions are:

1.) Shall I create an index for the tables? How can I do that for Serendipity?

2.) Does S9Y indeed lead to long-running queries? I doubt that, since S9Y is a great software. Besides, we do not have soo many readers. On average about 450 users per day. Last Tuesday was actually an even less busy day.
I never had any problems on days with many visitors. A few months ago, we had more than 3000 visitors on one day and no problem.

I am using S9Y version 1.0

http://atlanticreview.org/

I have written 405 entries. Some are relatively long, since I use a lot of long quotes. This does not fill up a database, does it?

There are 12 entries on each page.
In the sidebar I have the freetag plugin (many tags) and the linklist plugin (many links).

Does this result in "long-running queries"?


I do NOT have the plugin "Simple Cached/Pregenerated Pages," because it is called experimental. Should I use it?

There is a link for "Build cached entries" in the Administration Suite. Is that a left-over from the Backup Interface plugin? I have deinstalled that plugin, because it required more resources than my webhost has granted me.

I use the plugin "Extended properties for entries" and I have enabled the function "Allow to cache entries."

I have enabled "Use gzip compressed pages" in the Configuration menu.

I had a Youtube video embeded on the day before the server got an overload and then blocked, but I don't see how an embeded video could cause such trouble. (I hardly ever embed videos.)

Thanks for any help. I appreciate it.

[garvinhicking]

Hi!

First, Serendipity has indices set by default. No need to add manual indices!

If you use phpMyAdmin, you should see indices already for tables like serendipity_entries.

2.) Does S9Y indeed lead to long-running queries?

Undoubtedly, it can. If an s9y is under high traffic, it generates high traffic to the database. Depending on how you're hosted, other sites may also interfer - on shared hosting, you're never alone, and other people always influence the server power available to you. Plus, if your servers HDD might have failed, this also leads to very low performance. But I guess if there were a hardware failure, your hosted would have informed you.

This is a general performance problem which you have with every piece of software. The higher traffic you have, the higher server power you need.

On top of that, Spammers attribute to 70-80% of a webservers performance thanks to comment spam nowadays. At least that's the percentage of my blog.

I have written 405 entries. Some are relatively long, since I use a lot of long quotes. This does not fill up a database, does it?

It does fill up the DB, but very moderately. This should not be related to the real problem of yours.

The freetag plugin is somewhat SQL/Database intensive, depending on the amount of tagged entries.

I do NOT have the plugin "Simple Cached/Pregenerated Pages," because it is called experimental. Should I use it?

This could be a first step you might want to pursue, yes. It should definitely lower DB calls.

BUT if your server trouble is caused by spammers who constantly try to trackback or commentspam your page (even though its blocked by antispam measurements), this might take up the most cycles of your server. So maybe you can try to analyze your spamblocklog and see if there's much spam you're currently blocking? If it is, installing the cache plugin will help you nothing at all.

There is a link for "Build cached entries" in the Administration Suite. Is that a left-over from the Backup Interface plugin? I have deinstalled that plugin, because it required more resources than my webhost has granted me.

No, that's an item of the extended properties of entries plugin which does some lightweight caching to your entries, like you write later.

I have enabled "Use gzip compressed pages" in the Configuration menu.

Disable this. It leads to higher CPU load times. Nowadays, you better pump out more bytes than to allocate more CPU time.

I had a Youtube video embeded on the day before the server got an overload and then blocked, but I don't see how an embeded video could cause such trouble. (I hardly ever embed videos.)

Embedded videos should cause no extra traffic or load time to your blog, you'Re right.

I hope I could at least give you some ideas of how everything is working. The bottom line is that probably only your provider knows what is taking up the resources. If they could enable the 'slow query log' for mysql, this would help you a lot...

Best regards,
Garvin

[Josh]

Thank you, Garvin

This is how the index info for the serendipity_entries table looks like. There was a button for "optimize table", which I clicked and then I took this screenshot.
Does the index look alright?




There are 707.844 entries (Datensätze) in the spamblog log table. (I am running S9Y since March 2005.) I guess I should empty that table. I have already made a change in the spam protector plugin to log everything in a plaintext file rather than the database.
I could not figure out, how many spam entries came the day the servers were overloaded. I did however look into the logfiles and did not find a very large number of trackback attempts.

I have disabled "Use gzip compressed pages" in the Configuration menu and will ask my web provider what was taking up the resources and ask them to enable the 'slow query log' for mysql. Thanks for all these tips, Garvin.

The overload and block of the server happened two weeks ago. No problem since then. Thus, I am somewhat reluctant to use the experimental plugin "Simple Cached/Pregenerated Pages."
Are you using this plugin?

[garvinhicking]

Hi Josh!

Sadly that image is just about statistics; the important box should be to the left of the one you took the screenshot of

There are 707.844 entries (Datensätze) in the spamblog log table. (I am running S9Y since March 2005.) I guess I should empty that table. I have already made a change in the spam protector plugin to log everything in a plaintext file rather than the database.

Yes, you can easily truncate/empty that table. Logging to the file is a bit faster, but you'll need to delete/truncate that file also every now and then.

Thanks for all these tips, Garvin.

You're welcome, I hope they lead to any results!

The overload and block of the server happened two weeks ago. No problem since then. Thus, I am somewhat reluctant to use the experimental plugin "Simple Cached/Pregenerated Pages."
Are you using this plugin?

I was using that plugin before I moved to a new server. It did work and lift some part of the load off the server, but not too much that was worth the drawbacks of the plugin (larger temporary files, will bypass some "cool" plugins that require dynamic updates).

Best regards,
Garvin

[Josh]

Thanks again, Garvin!!!

the important box should be to the left of the one you took the screenshot of

Hm, there is not any box to the left on my screen. Perhaps the one on top? Screenshot:

[garvinhicking]

Hi!

Oh, strange. Seems as if I overlooked the first image you posted. The one that was important is the one which lists

"entry_idx", "date_idx" etc. They are all there, so indices are set properly on your server!

HTH,
Garvin