Slow server performance: too many static pages?

[T]

After I added quite a lot of static pages, I began to notice a much slower server performance; static pages now sometimes take half a minute to load. Can I counter this by changing anything Serendipity-related or do I just have a crappy server?

Best regards,

[garvinhicking]

Hi!

Basically the performance should only decrease with a few THOUSAND staticpages.

Could it be you installed other serendipity plugins? That can be more of a performance reason...or maybe you now have more visitors that lead to more server load?

Regards,
Garvin

After I added quite a lot of static pages, I began to notice a much slower server performance; static pages now sometimes take half a minute to load. Can I counter this by changing anything Serendipity-related or do I just have a crappy server?

Best regards,

[T]

Basically the performance should only decrease with a few THOUSAND staticpages.

Could it be you installed other serendipity plugins? That can be more of a performance reason...or maybe you now have more visitors that lead to more server load?

Thanks for the reply. It's just a few hundred static pages. Visitors have been basically just myself, as the site is not in use yet. I have installed just some basic plugins, and the Wiki links plugin (as to interlink the SP's)... So, crappy server-problem?

Best regards,

[garvinhicking]

Hi!

Hm, if you say "a few hundred static pages" that might change the situation. If you have a sidebar plugin that displays those pages, it might take some time to cycle as many.

Please tell more about your site, the plugins you're using (sidebar and event).

Regards,
Garvin

[T]

Please tell more about your site, the plugins you're using (sidebar and event).

Sidebar: I have removed all unneeded plugins; now there is nothing SP-related in the sidebar anymore.
Event: just everything you automatically get on first installation, and the Wikilinks-plugin.

I hope this is helpful, thanks for your help.

[garvinhicking]

Hi!

Okay. Is the time it takes to open your blog entry overview roughly the same time it takes to display a single blog entry? And is this time roughly the same like when viewing a single static page?

If the performance differes, where is it slowest?

Regards,
Garvin

[T]

Okay. Is the time it takes to open your blog entry overview roughly the same time it takes to display a single blog entry? And is this time roughly the same like when viewing a single static page?

If the performance differes, where is it slowest?

Overview and blog entries about the same, and quite fast. A single static page: takes everywhere between 5 and 15 seconds, so quite slow. SP overviews take even longer.

[garvinhicking]

Hi!

Okay, that should give us a clue. Do you have access to phpmyadmin and can check if there are indices set for the serendipity_staticpage DB table? And howlarge that table is?

Are you using MySQL? If so, we could enable some debugging routine that could give us a clue which query is creating to much performance drop.

To do that you can edit the file include/db/mysql.inc.php and search for:

Code: Select all

static $benchmark = false;

and change that to:

Code: Select all

static $benchmark = true;

Then you can use phpmyadmin to create those two tables:

Code: Select all

CREATE TABLE `BLOGLOG` (
  `request` varchar(255) NOT NULL default '',
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `sql` text NOT NULL,
  `exec_time` varchar(50) NOT NULL default '0',
  `ip` varchar(20) NOT NULL default ''
);

CREATE TABLE `BLOGLOG_TOTAL` (
  `sql` text NOT NULL,
  `counter` int(11) NOT NULL default '0'
);

Now you can fetch the staticpage that take so long to load, and check the BLOGLOG database table, so that you can see how long which SQL query took. The one that took longest, you could show/post here?

Regards,
Garvin

[T]

Now you can fetch the staticpage that take so long to load, and check the BLOGLOG database table, so that you can see how long which SQL query took. The one that took longest, you could show/post here?

I did exactly as you said, but I cannot check the BLOGLOG table, as it states it has no index defined. Also, my weblog is now filled with PHP messages (BENCH: etc.).

What am I doing wrong?

[garvinhicking]

Hi!

The "bench" messages are meant to appear as long as the debugging is enabled.

But what do you mean with "it states it has no index defined"? You must browse the table with phpmyadmin, there should be rows in it?

Regards,
Garvin

[T]

But what do you mean with "it states it has no index defined"? You must browse the table with phpmyadmin, there should be rows in it?

When I try to browse the table, MySQL returns an 'empty set of results' and states 'no index defined' (translating, I'm using Dutch phpmyadmin). I have already navigated the blog and opened several static pages, so it should be filled.

However, overviews and pages that link to lots of different pages (using Wikilinks) have an enormous amount of BENCH-messages on them, so we may be on to something here.

[garvinhicking]

Hi!

Ah, okay. In that case it seems that serendipity is not filling this table.That might be because in your mysql version some keys are reserved. Please edit include/db/mysql.inc.php again and replace this:

Code: Select all

        mysql_query("INSERT INTO BLOGLOG (request, timestamp, sql, exec_time, ip) VALUES
        ('" . serendipity_db_escape_string($_SERVER['REQUEST_URI']) . "', NOW(),
        '" . serendipity_db_escape_string($sql) . "', '" . (number_format($end-$start, 10)) . "',
        '" . serendipity_db_escape_string($_SERVER['REMOTE_ADDR']) . "')");

(I added some newlines for better readibility, actually this code is in a single row:

with this:

Code: Select all

        mysql_query("INSERT INTO BLOGLOG (`request`, `timestamp`, `sql`, `exec_time`, `ip`) VALUES
        ('" . serendipity_db_escape_string($_SERVER['REQUEST_URI']) . "', NOW(),
        '" . serendipity_db_escape_string($sql) . "', '" . (number_format($end-$start, 10)) . "',
        '" . serendipity_db_escape_string($_SERVER['REMOTE_ADDR']) . "')");
echo mysql_error();

I've added that echo mysql_error() line, and added backticks to the tablenames.

You mention you're intensely using the wikilinks plugin. How many wikilinks are on a single page? Each wikiling has one database lookup, so when you use many, this definitely impacts your databse, there's no real way to circumvent that (apart from not using wikilinks )

HTH,
Garvin

[T]

You mention you're intensely using the wikilinks plugin. How many wikilinks are on a single page? Each wikiling has one database lookup, so when you use many, this definitely impacts your databse, there's no real way to circumvent that (apart from not using wikilinks )

I can't seem to find the code you mentioned, there is no mention of 'bloglog' whatsoever in my mysql.inc.php file.

As for the Wikilinks, this would not account for the overviews loading slowly (as they just list their subpages, and not by Wikilinking) and as for the normal SP's, they have maybe 5 to 10 Wikilinks max on them.

[garvinhicking]

Hi!

I can't seem to find the code you mentioned, there is no mention of 'bloglog' whatsoever in my mysql.inc.php file.

Which s9y version are you using? This code is part of at least Serendipity 1.2.1

As for the Wikilinks, this would not account for the overviews loading slowly (as they just list their subpages, and not by Wikilinking) and as for the normal SP's, they have maybe 5 to 10 Wikilinks max on them.

Okay, that's true!

Regards,
Garvin

[T]

Which s9y version are you using? This code is part of at least Serendipity 1.2.1

Hmmm, I'm still using 1.1. If there is any way of avoiding upgrading now and do this another way, that would be a lot easier. I want to upgrade eventually, but now it would just take a lot of time and extra work.