Monitoring Performance

[judebert]

Hi, everybody. First off, let me apologize for being absent for so long; unfortunately, I'm really busy with many projects, and I don't think I can really come back and participate quite yet. Eventually, I shall return!

Of course, a problem drives me back: I need some advice on monitoring tools.

My site is slow, slow, slow. My web provider is POWWeb, and I've got one of their dinky accounts, which uses a LAMP shared server (Linux, Apache, MySQL, PHP/Perl). I believe the problem is the MySQL database, because static pages load almost instantaneously: in particular, my redirect page at http://judebert.com is displayed quite quickly, but then the blog takes forever to come up. Retrieving individual articles and doing a quicksearch can take a long time, too.

I tried removing the newsbox plugins, since they make extra queries, but that didn't help. I checked my MySQL indexes with phpMyAdmin, and everything looks fine to me, although I'm not a database expert. I'm willing to become one, if necessary.

This problem is even more frustrating because it's intermittent. That leads me to suspect that the server load plays a big part. For instance, early in the morning, it generally responds satisfactorily. Any other time, it's excrutiatingly slow.

I don't believe this is Serendipity's fault. I want to find out where the problem lies. I need tools that will tell me how long my web server and SQL server queries are taking, perhaps even give me an idea of how much memory and CPU I'm using. Is it possible that there's some PHP, Apache, or MySQL switch that will do the job? Or do I need to install some sort of script?

Thanks in advance.

[garvinhicking]

Hi!

Which event and sidebar plugins do you have installed? Often a RSS feed or other plugins that have network interaction can cause this.

Generally, if you really want to get your hands dirty, have a look at the PHP xdebug module. It allows to spew out cachegrind files that you can introspect with tools like kcachegrind to do introspection.

You can also edit the mysql.inc.php file and enable the debugging option there to write SQL logfiles that benchmarks the queries issued to your server.

Regards,
Garvin

[judebert]

Thanks, Garvin. I'm looking into the xdebug module; I'm not sure PowWeb supports it, but I'll find out.

I updated the benchmark variable to true in mysql.inc.php. I also looked at my database. I deleted the spam log, which was up to 75MB. That seems to have gained me a little speed. But several of the tables had fields in multiple indexes, and a few had no index at all! Weirdest of all, some of my tables were collated as UTF8, while the rest were latin_swedish!

I also checked my error log, and found that s9y is getting called multiple times. I'm often warned about headers already being sent and sessions already being started. I updated my .htaccess to remove my custom redirections, which seems to have helped, but did not entirely fix the problem.

Can these anomalies be causing my slowdowns?

[judebert]

Hmmm...

The SQL seems to be served VERY quickly. Thousandths or hundredths of seconds per query. I had to add a couple of database tables, but what they hey. (Incidentally, BLOGLOG and BLOGLOG_TOTAL aren't defined. I took my best shot at them.)

I also found that somebody besides me is using my site. I thought I had no visitors. On the other hand, it's probably a bot.

I wonder if we could somehow report the query time on each page.

I'll keep looking, and let you know what shows up. Thanks for the help.

[dom97]

My problem was some of the templates that are in the template area of S9Y are a little slow. I finally found a template that works real well with my site and it's called FRESHY. I heard that the menuing system at the top of some templates can give problems.

[judebert]

Holy carp! I'm getting hammered by spammers! 770 rows in 24 hours, generating 15,000 SQL queries.

If I sort the BLOGLOG table by sql query, then by time, I find the exact same query taking between 1.5 and .00039 sec. That's a factor of 5000! And this is after I made all my collations utf8_unicode_ci, and cleared the spamblocklog. I don't think that's an SQL problem, and it's certainly not a Serendipity problem.

Lots of the requests are "comment.php?type=trackback". And they make SQL like "CREATE TABLE serendipity_spamblock_htaccess" or "INSERT INTO serendipity_spamblocklog".

How about that? I can't use my own website, which I'm paying for, because spammers are trying to use it, overloading my server.

On the plus side, our spam blocking plugin is doing an outstanding job for me. Thanks, Garvin! Blocking API-generated comments is keeping them out.

Checking the BLOGLOG_TOTAL, I see that 1700 queries are for "SELECT name, value from serendipity_config WHERE authorid = 0". An identical number are for "SELECT * from serendipity_plugins WHERE placement = 'event' ORDER BY placement, sort_order". (Sounds like startup to me.) The next most popular query is 779 calls to serendipity_spamblock_htaccess.

Anybody know if there's some way I can block them sooner, without straining my server? If I rename comment.php, and change the template so users can still make comments, do you think that'll do it, or do their scripts adjust to the trackback link? Could I block trackbacks at the .htaccess file somehow? Will killing no-referrer requests prevent Google from finding me? I want my blog back!

[garvinhicking]

Hi!

Lots of the requests are "comment.php?type=trackback". And they make SQL like "CREATE TABLE serendipity_spamblock_htaccess" or "INSERT INTO serendipity_spamblocklog".

That might be because of a nasty bug in the spamblock plugin (which I fixed in SVN some days ago), taht it tries to recreate the spamblock table. And the logging is being written because you configured the spamblock to log it. Sadly it can't ditint between spammer or real person, so you need to take a choice.

You might want to disable trackbacks using the method outlined in the FAQ, by patching the comment.php file. Also, if you can use custom htaccess rules, you could try to lock out people. Babelfish and http://blog.brockha.us/index.php?/archi ... ehren.html might get you started on that?

Checking the BLOGLOG_TOTAL, I see that 1700 queries are for "SELECT name, value from serendipity_config WHERE authorid = 0". An identical number are for "SELECT * from serendipity_plugins WHERE placement = 'event' ORDER BY placement, sort_order". (Sounds like startup to me.) The next most popular query is 779 calls to serendipity_spamblock_htaccess.

Yeah, the first two are the queries that are required by s9y to initialilze the s9y plugin framework. That's needed to initialize the spamblock plugin. And yeah, that sucks, but I see no way to circumvent that

Anybody know if there's some way I can block them sooner, without straining my server? If I rename comment.php, and change the template so users can still make comments, do you think that'll do it, or do their scripts adjust to the trackback link? Could I block trackbacks at the .htaccess file somehow? Will killing no-referrer requests prevent Google from finding me? I want my blog back!

Yes, blocking some via .htaccess will work. Renaming comment.php will sadly only help you a few days, spammers are likely to catch up quickly.

Best regards,
Garvin

[blog.brockha.us]

I added a short english version to my article. My English is not the best practiced, but it should be better than babelfish.. (I hope!)

[stm999999999]

You can also edit the mysql.inc.php file and enable the debugging option there to write SQL logfiles that benchmarks the queries issued to your server.

Can you give a sql-create-statement for BLOGLOG?

[judebert]

I added a short english version to my article. My English is not the best practiced, but it should be better than babelfish.. (I hope!) ;-)

How do I get to the English version?

[blog.brockha.us]

Below the topic of the link, Garvin gave you, you'll find a language chooser. Default is German, but you can switch to English there.