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.
Monitoring Performance
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: Monitoring Performance
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
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
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
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?
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?
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.
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.
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!
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!
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hi!
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?
Best regards,
Garvin
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.Lots of the requests are "comment.php?type=trackback". And they make SQL like "CREATE TABLE serendipity_spamblock_htaccess" or "INSERT INTO serendipity_spamblocklog".
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?
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 thatChecking 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.
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.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!
Best regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
-
- Regular
- Posts: 695
- Joined: Tue Jul 03, 2007 3:34 am
- Location: Berlin, Germany
- Contact:
-
- Regular
- Posts: 1531
- Joined: Tue Mar 07, 2006 11:25 pm
- Location: Berlin, Germany
- Contact:
Re: Monitoring Performance
Can you give a sql-create-statement for BLOGLOG?garvinhicking wrote:
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.
Ciao, Stephan
-
- Regular
- Posts: 695
- Joined: Tue Jul 03, 2007 3:34 am
- Location: Berlin, Germany
- Contact: