Serendipity Forums

User and developer community
https://board.s9y.org/

Slow server performance: too many static pages?

https://board.s9y.org/viewtopic.php?t=12108

Page 2 of 3

Re: Slow server performance: too many static pages?

Posted: Mon Jan 21, 2008 3:03 pm
by garvinhicking
Hi!

Ah, okay. Upgrading now would be the best option, because 1.2 also improves performance in several places.

If you did not modify any PHP files of your isntallation, you can update by simply uploading all files to your directory, that's a matter of maybe 3-4 minutes. MUCH faster than patching only your mysql.inc.php file to work properly.

Regards,
Garvin

Re: Slow server performance: too many static pages?

Posted: Wed Jan 23, 2008 2:14 pm
by T
garvinhicking wrote:Ah, okay. Upgrading now would be the best option, because 1.2 also improves performance in several places.

If you did not modify any PHP files of your isntallation, you can update by simply uploading all files to your directory, that's a matter of maybe 3-4 minutes. MUCH faster than patching only your mysql.inc.php file to work properly.
I know, it's easy, but in fact I did modify some files... Anyway I'll try to upgrade asap, however I think the problem is indeed the database lookups.

Overviews take really long to load, as I have overviews with 50+ subpages. I can solve this by breaking them up into smaller overviews, however what I don't get is why a single SP takes so long to load, and a single entry doesn't. The only difference is that I have a lot of SP's and but a few entries. Does loading a SP require a full lookup of all SP's, maybe?

Re: Slow server performance: too many static pages?

Posted: Wed Jan 23, 2008 3:09 pm
by garvinhicking
Hi!

Sadly I have long not looked at the SQL of the static pages, so it would help a lot if I could see the actual SQL queries saved/benchmarked in the BLOGLOG table to inspect the code starting witht hose queries...

Regards,
Garvin

Re: Slow server performance: too many static pages?

Posted: Wed Jan 23, 2008 6:01 pm
by T
garvinhicking wrote:so it would help a lot if I could see the actual SQL queries saved/benchmarked in the BLOGLOG table to inspect the code starting witht hose queries...
Working on the upgrade. Would it be of any help if I copy/pasted some of the BENCH-messages that show up?

Re: Slow server performance: too many static pages?

Posted: Wed Jan 23, 2008 8:09 pm
by garvinhicking
Hi!

The messages themself are sadly not so helpfull, the DB only would be :( :)

Regards,
Garvin

Re: Slow server performance: too many static pages?

Posted: Thu Jan 24, 2008 12:15 am
by T
Done it. Highest exec time is 0.4314880371, for an overview. A single static page takes 0.0781450272. If you need any more data, please let me know.

Re: Slow server performance: too many static pages?

Posted: Thu Jan 24, 2008 10:34 am
by garvinhicking
Hi!

Yes, can you show a SQL dump of your BLOGLOG DB table? Each query should be listed there.

To only dump what you need, please empty (NOT drop) your BLOGLOG table first, then open the page in your browser that's being slow. After that, directly make a SQL ump of the bloglog DB table, because then it only holds the queries performed for that page call you just did.

Regards,
Garvin

Posted: Thu Jan 24, 2008 1:48 pm
by T
Here you are, sorry for the lack of proper line breaks:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generatie Tijd: 24 Jan 2008 om 13:33
-- Server versie: 4.1.22
-- PHP Versie: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `manicsn_db`
--

-- --------------------------------------------------------

--
-- Tabel structuur voor tabel `BLOGLOG`
--

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 ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Gegevens worden uitgevoerd voor tabel `BLOGLOG`
--

INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:53', 'SELECT name, value\n                                        FROM site_config\n                                        WHERE authorid = 0', '0.0471091270', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT * from site_plugins  WHERE  placement =  ''event''  ORDER BY placement, sort_order', '0.0012269020', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT name, value FROM site_config WHERE name LIKE ''serendipity_event_contentrewrite:9d2e5815e0cf421aa9bf678c8747a2c7/%'' ORDER BY name', '0.0011529922', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT * FROM site_options WHERE okey = ''l_1984dd0f98370251143a655f7c6f39b9'' LIMIT 1', '0.0138309002', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT DISTINCT\n                    email, realname, authorid, userlevel, right_publish\n                  FROM\n                    site_authors\n                  WHERE\n                    username   = ''Thijs''\n                  AND password = ''f5d2871c0d7cc5eb30e203220d3c73a6''', '0.0157141685', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT name,value\n                                        FROM site_config\n                                        WHERE authorid = ''1''', '0.0006139278', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT *\n                FROM site_staticpages\n               WHERE pagetitle = ''/site/persons/patrickbateman.htm''\n                  OR permalink = ''/site/persons/patrickbateman.htm''\n               LIMIT 1', '0.1046869755', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT COUNT(placement) AS count from site_plugins WHERE placement=''left'' ', '0.0004999638', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT COUNT(placement) AS count from site_plugins WHERE placement=''right'' ', '0.0003681183', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT \n                    ep_sticky.value AS orderkey,\n\n                    e.id,\n                    e.title,\n                    e.timestamp,\n                    e.comments,\n                    e.exflag,\n                    e.authorid,\n                    e.trackbacks,\n                    e.isdraft,\n                    e.allow_comments,\n                    e.last_modified,\n\n                    a.realname AS author,\n                    a.username AS loginname,\n                    a.email\n                     , e.body, e.extended\n                     \n                FROM\n                    site_entries AS e\n                    LEFT JOIN site_authors a\n                        ON e.authorid = a.authorid\n                    LEFT JOIN site_entrycat ec\n                        ON e.id = ec.entryid\n                    LEFT JOIN site_category c\n                        ON ec.categoryid = c.categoryid\n                     LEFT OUTER JOIN site_entryproperties ep_no_frontpage\n                                                  ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = ''ep_no_frontpage'')\n LEFT OUTER JOIN site_entryproperties ep_access\n                                              ON (e.id = ep_access.entryid AND ep_access.property = ''ep_access'')\n LEFT JOIN site_entryproperties ep_sticky\n                                            ON (e.id = ep_sticky.entryid AND ep_sticky.property = ''ep_is_sticky'') LEFT JOIN site_authorgroups AS acl_a\n                                   ON acl_a.authorid = 1\n                            LEFT JOIN site_access AS acl_acc\n                                   ON (    acl_acc.artifact_mode = ''read''\n                                       AND acl_acc.artifact_type = ''category''\n                                       AND acl_acc.artifact_id   = c.categoryid\n                                      )\n                    WHERE isdraft = ''false'' AND e.timestamp <= 1201178100 AND  (ep_access.property IS NULL OR ep_access.value = ''member'' OR ep_access.value = ''public'' OR (ep_access.value = ''private'' AND e.authorid = 1))  AND  (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != ''true'')   AND     (\n                                 c.categoryid IS NULL\n                                 OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)\n                                 OR ( acl_acc.artifact_id IS NULL\n                                      \n                                    )\n                               )\n                     GROUP BY e.id\n            ORDER BY orderkey DESC, timestamp DESC\n                      LIMIT 2', '0.0670650005', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT entryid, property, value FROM site_entryproperties WHERE entryid IN (9, 14) ', '0.0006718636', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:54', 'SELECT\n                     ec.entryid,\n                     c.categoryid,\n                     c.category_name,\n                     c.category_description,\n                     c.category_icon,\n                     c.parentid\n                FROM site_category AS c\n           LEFT JOIN site_entrycat AS ec\n                  ON ec.categoryid = c.categoryid\n               WHERE ec.entryid IN (9, 14)\n            ORDER BY c.category_name ASC', '0.0003991127', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT content\n                    FROM site_staticpages\n                   WHERE id = 56', '0.0004539490', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT template\n                FROM site_staticpages_types\n               WHERE id = ''2''', '0.0194799900', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''PATRICK BATEMAN [song]'' ORDER BY timestamp DESC LIMIT 1', '0.0024139881', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''PATRICK BATEMAN [song]'' ORDER BY timestamp DESC LIMIT 1', '0.0404789448', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''bret easton ellis'' ORDER BY timestamp DESC LIMIT 1', '0.0004909039', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''bret easton ellis'' ORDER BY timestamp DESC LIMIT 1', '0.0106670856', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''American Psycho [book]'' ORDER BY timestamp DESC LIMIT 1', '0.0007369518', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''American Psycho [book]'' ORDER BY timestamp DESC LIMIT 1', '0.0072669983', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''american psycho [book]'' ORDER BY timestamp DESC LIMIT 1', '0.0004839897', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''american psycho [book]'' ORDER BY timestamp DESC LIMIT 1', '0.0063748360', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''glamorama'' ORDER BY timestamp DESC LIMIT 1', '0.0005331039', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''glamorama'' ORDER BY timestamp DESC LIMIT 1', '0.0063121319', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_entries WHERE title = ''american psycho [movie]'' ORDER BY timestamp DESC LIMIT 1', '0.0005180836', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id, permalink FROM site_staticpages WHERE headline = ''american psycho [movie]'' ORDER BY timestamp DESC LIMIT 1', '0.0064730644', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT id\n                FROM site_staticpages\n               WHERE parent_id = 56\n                 AND publishstatus = 1\n            ORDER BY pagetitle', '0.0059940815', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT pagetitle, permalink\n                FROM site_staticpages\n               WHERE parent_id = 56\n                 AND publishstatus = 1\n            ORDER BY pageorder', '0.0060088634', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT \n                               a.authorid,\n                               a.realname,\n                               a.username,\n                               a.password,\n                               a.mail_comments,\n                               a.mail_trackbacks,\n                               a.email,\n                               a.userlevel,\n                               a.right_publish\n                               \n                          FROM site_authors AS a\n                               \n                               WHERE a.authorid = ''1''\n                               \n                      ORDER BY a.realname ASC', '0.0003829002', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:32:55', 'SELECT * FROM site_staticpages WHERE publishstatus = 1 ORDER BY pagetitle, pageorder', '0.0844979286', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:33:01', 'SELECT \n                               a.authorid,\n                               a.realname,\n                               a.username,\n                               a.password,\n                               a.mail_comments,\n                               a.mail_trackbacks,\n                               a.email,\n                               a.userlevel,\n                               a.right_publish\n                               \n                          FROM site_authors AS a\n                               \n                               \n                               \n                      ORDER BY a.realname ASC', '0.0213458538', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:33:01', 'SELECT * from site_plugins  WHERE  placement =  ''left''  ORDER BY placement, sort_order', '0.0005688667', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/persons/patrickbateman.htm', '2008-01-24 13:33:01', 'SELECT * from site_plugins  WHERE  placement =  ''right''  ORDER BY placement, sort_order', '0.0005509853', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/serendipity.css', '2008-01-24 13:33:02', 'SELECT name, value\n                                        FROM site_config\n                                        WHERE authorid = 0', '0.0014088154', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/serendipity.css', '2008-01-24 13:33:02', 'SELECT * from site_plugins  WHERE  placement =  ''event''  ORDER BY placement, sort_order', '0.0008649826', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/serendipity.css', '2008-01-24 13:33:02', 'SELECT name, value FROM site_config WHERE name LIKE ''serendipity_event_contentrewrite:9d2e5815e0cf421aa9bf678c8747a2c7/%'' ORDER BY name', '0.0011639595', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/serendipity.css', '2008-01-24 13:33:02', 'SELECT DISTINCT\n                    email, realname, authorid, userlevel, right_publish\n                  FROM\n                    site_authors\n                  WHERE\n                    username   = ''Thijs''\n                  AND password = ''f5d2871c0d7cc5eb30e203220d3c73a6''', '0.0004839897', '82.73.141.103');
INSERT INTO `BLOGLOG` VALUES('/site/serendipity.css', '2008-01-24 13:33:02', 'SELECT name,value\n

Posted: Thu Jan 24, 2008 1:50 pm
by garvinhicking
Hi!

That dump is incomplete, it stops at some point without being complete. Can you upload the dump file on your host, or on a site like http://nopaste.php-q.net ?

Regards,
Garvin

Posted: Thu Jan 24, 2008 1:57 pm
by T
Here you are:

http://www.wumswebsite.nl/temp/testdump.sql.zip

Posted: Thu Jan 24, 2008 3:11 pm
by garvinhicking
Hi!

Hm, analyzing the queries, I'm quite afraid why the performance should be slow - the queries are totalling below a second.

What's interesting is that after the last queriy on 13:40:14 it takes a full 7 seconds to perform the next query.

This happens after this query:

Code: Select all

SELECT * FROM site_staticpages WHERE publishstatus = 1 ORDER BY pagetitle, pageorder
However, in the current version of the staticage plugin (3.60) I do not find such a SQL query. Could it be you'Re using an older version of the staticpage plugin?

Regards,
Garvin

Posted: Fri Jan 25, 2008 12:50 am
by T
garvinhicking wrote:in the current version of the staticage plugin (3.60) I do not find such a SQL query. Could it be you'Re using an older version of the staticpage plugin?
I'm using the latest version, however I have edited this part. It lists the SP's in an alphabetical, rather than a chronological order.

Posted: Fri Jan 25, 2008 11:21 am
by garvinhicking
Hi!

Which part exactly did you edit, at which line?

Regards,
Garvin

Posted: Thu Jan 31, 2008 1:13 pm
by T
garvinhicking wrote:Which part exactly did you edit, at which line?
Sorry, was away for a time. I generally just changed 'pageorder' to 'pagetitle' wherever necessary, as to list them alphabetically. Can this be the problem?

Posted: Thu Jan 31, 2008 2:58 pm
by garvinhicking
Hi!

Actually, yes. Changing this actually modifies which SQL indices the database query can use, and the queries are only optimized to include 'pageorder' in their queries.

So you might need to set an index on the 'pagetitle' columns as well...

Regards,
Garvin
All times are UTC+02:00
Page 2 of 3

Powered by phpBB® Forum Software © phpBB Limited