Features / Usability

Posted by tibi 13 Oct 2006 19:07 GMT-0000 posts: 61

ok i updated my site to 1.9.5 and it posibly is a bit slower but the site is still realy slow.

the new version is:
http://www.ijbrug.nl/ijburg2

Posted by tibi 15 Oct 2006 16:45 GMT-0000 posts: 61

i just will keep on talking to myself 😕

i have followd the $num_queries variable printed in the footer (so you get something like 2760 databse queries used)

first anded up in galexia but that file was not used then went to tikidblib.php in the dir lib.

there is something like:
$debugger->msg($this->num_queries.': '.$query);

but i did not realy follow this can i some how print this to the debugger console.

any why i now print the queries to the screen (all 2760!!)

now i will look at these queries.... pfff 😬😬

Posted by tibi 15 Oct 2006 19:17 GMT-0000 posts: 61

some queries are done many many and many times. i don't understand why.
like this (a form page, don't look at the total line):

42624=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42625=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42626=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14702

42627=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42628=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42629=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14703
42630=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42631=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42632=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14704
42633=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,

42634=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42635=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14705
42636=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42637=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42638=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14706
42639=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42640=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,

42641=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14707
42642=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42643=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42644=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14708
42645=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42646=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42647=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,

TOTAL=14709
42648=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42649=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42650=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14710
42651=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42652=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42653=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14711

42654=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42655=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42656=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14712
42657=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42658=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42659=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14713
42660=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,

42661=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42662=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14714
42663=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42664=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42665=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14715
42666=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42667=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,

42668=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14716
42669=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42670=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42671=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14717
42672=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42673=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42674=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,

TOTAL=14718
42675=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42676=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42677=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14719
42678=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42679=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42680=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14720

42681=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42682=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42683=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14721
42684=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42685=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42686=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14722
42687=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,

42688=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42689=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14723
42690=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42691=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42692=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14724
42693=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42694=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,

42695=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14725
42696=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42697=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42698=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14726
42699=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42700=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42701=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,

TOTAL=14727
42702=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42703=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42704=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,
TOTAL=14728
42705=====>select `posts`, `level` from `tiki_user_postings` where `user`=? | xandra,
42706=====>select `filename`,`filesize`,`attId` from `tiki_forum_attachments` where `threadId`=? | 34911,
42707=====>select count(*) from `tiki_forums_reported` where `threadId`=? | 34911,

Posted by al123 16 Oct 2006 10:31 GMT-0000 posts: 32

> hi,
>
> first tikiwiki is great i use it for about 2 years and i'am very happy with it.
> but last couple of months the website is getting very slow.
>
> 1)
> is there any documentation to solve such a problem or
> 2)
> is there en expert which can help me (for a small amount of mony)?
>
> 3)
> can the problem be a to large database (images go into the db)
>
> this is my website:
> http://www.ijbrug.nl
>
> i will too update the software to the latest version.

Here's a list of some of the things I've done to speed stuff up.

Installed eaccelerator - gave a slight page speed up

Database - assuming mysql here mostly

• Periodically optimize your tables - can be done via gui or command line - eg the open source version of sqlyog has a gui option to do this.
• Add a big query cache - save your server by caching results in ram.
• Reorganize table structure - requires some programming. You say you store your images in the database - one thing to be aware of if you're using MyISAM tables is that if the images/files are stored in a blob, MyISAM reads the blob into memory as it performs a query, even if the field containing the blob isn't part of the query - this can have a big impact on searches that need to work through rows, so I store blob data in a separate table linked to the original table row via a key - makes a big difference especially if you have lots/big images. I think tiki_images and tiki_image_data already do this, but if you've lots of images stored elsewhere in tw, in the database, then this could be something to look into, e.g. I don't *think* this separation happens with tiki_files if you store files in the database there.

Other - turn off non-essential services on your server, or perhaps look to see if you can restart any non responsive/crashed services if there are any.

HTH

A.

Posted by tibi 16 Oct 2006 15:01 GMT-0000 posts: 61

thanks al123 finaly i don't feel so lonly anymore. i will use our tips!

in java i have writen a small program which reads the logged queries from above and runs them agains the database with explain.
this gave me a nice overview of missing indexes.
up to now i found these:

ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `topicId` USING BTREE(`topicId`);
ALTER TABLE `ijbrug`.`users_permissions` ADD INDEX `type` USING BTREE(`type`);
ALTER TABLE `ijbrug`.`tiki_forum_attachments` ADD INDEX `threadId` USING BTREE(`threadId`);
ALTER TABLE `ijbrug`.`users_users` ADD INDEX `login` USING BTREE(`login`);
ALTER TABLE `ijbrug`.`tiki_modules` ADD INDEX `name` USING BTREE(`name`);
ALTER TABLE `ijbrug`.`tiki_user_assigned_modules` ADD INDEX `position` USING BTREE(`position`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `publishDate` USING BTREE(`publishDate`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `expireDate` USING BTREE(`expireDate`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `type` USING BTREE(`type`);
ALTER TABLE `ijbrug`.`tiki_article_types` ADD INDEX `show_pre_publ` USING BTREE(`show_pre_publ`);
ALTER TABLE `ijbrug`.`tiki_article_types` ADD INDEX `show_post_expire` USING BTREE(`show_post_expire`);

Posted by tibi 16 Oct 2006 15:38 GMT-0000 posts: 61

and two more:

it's a real improvement. i will investeged some more.

maybey someone can put this in the distribution.

Posted by tibi 16 Oct 2006 20:57 GMT-0000 posts: 61

after monitoring all queries for 20 min i got 26.000 queries. thousends of which were not optimised. i think with these last indexes they are.

my site is running much faster this should realy be added to the code source.

is there someone out there???

Posted by tibi 16 Oct 2006 21:25 GMT-0000 posts: 61

just found:
http://tikiwiki.org/tiki-index.php?page=DbPerformance

Posted by tibi 17 Oct 2006 17:32 GMT-0000 posts: 61

fumphco thanks.

i hope someone can impllement the indexes on this site.
14,81 sec to load 😬... pfffff

Posted by fumphco 18 Oct 2006 19:34 GMT-0000 posts: 30

>
> i hope someone can impllement the indexes on this site.
> 14,81 sec to load eek... pfffff

Did it take that long for you to receive this page? It displays in my browser in about 3 seconds (bottom bar shows 2.28 seconds, 367 queries). There was a time when pages were loading very slow for me in IE. I investigated and found that my IE was configured to check for new versions of stored pages every time. I changed the setting to automatic and noticed a significant performance improvement. I think the reason is that the style sheets are big, so browser caching speeds up page views considerably.

Posted by tibi 19 Oct 2006 21:34 GMT-0000 posts: 61

yes now it is faster bot bar: 2.33 secs and 449 queries.

a couple off day's ago there where much more queries and it took much longer. is this a new version??

Posted by tibi 21 Oct 2006 19:29 GMT-0000 posts: 61

one more:

i now also tried the tikilibdebug (see http://tikiwiki.org/tiki-index.php?page=DbPerformance) and now i see there are a lot of inserts into the table:
tiki_searchindex
and a delete which took a long time.

what is the tiki_searchindex doing?