Database performance


This is a Instruction how to measure database performance. This page shall also help to enhance database performance.

Note: This site is outdated. Last update in 2006.

Why is my site so slow?

  • Parsing tikilib and others takes a huge amount of time. The average time to display a page is mainly due to parsing. So php accelerators like turck mmcache greatly reduce load and display time. See PerformanceTuning for more information.
  • Sometimes some code is badly written and the database performance harms tiki performance. Then read on.


We are now starting work on Tikiwiki 1.10 to address some areas of parsing to make Tikiwiki faster.

Detecting database performance bottlenecks

  • First you can look at the counter at the bottom of the page. If it says > 1000 Database queries, then you actually have bad code. But this counter is erraneous. Since every object in tiki inherits from tikilib, the query() function exists in every object and every object has its own counter. Only the tikilib counter is displayed.
  • So we need a better way. Look at lib/tikidblib-debug.php. Follow the instructions in the comments and create a table:
    create table tiki_querystats(qcount number,qtext varchar(255),qtime float,UNIQUE KEY qtext (qtext));
because queries sometimes are larger than 255 chars i would use:

CREATE TABLE tiki_querystats (
qcount int(11) default NULL,
qtext TEXT default NULL,
qtime float default NULL,
UNIQUE KEY qtext 255
)

+ Then edit lib/tikilib.php and comment the require_once ('lib/tikidblib.php'); and uncomment the require_once ('lib/tikidblib-debug.php'); + With that you can perform query stats collecting.

Using tiki_querystats

  • long running queries

select qtime/qcount,qtext from tiki_querystats order by 1

  • most executed queries:

select qcount,qtime,qtext from tiki_querystats order by 1;

  • most time spent in:

select qtime,qcount,qtext from tiki_querystats order by 1;

  • resetting querystats

delete from tiki_querystats;

  • number of queries per click: first reset, then do a click, then

select sum(qcount) from tiki_querystats;

  • time spent in db: like above, but:

select sum(qtime) from tiki_querystats;

Example: today (11/20/2003), i added a item in tiki's trackers and got these stats:

select qtime,qcount,qtext from tiki_querystats order by 1;

| 0.070599 | 1 | delete from `tiki_searchindex` where `location`=? and `page`=? |
| 0.10163 | 35 | select `tran` from `tiki_language` where `source`=? and `lang`=? |
| 5.32297 | 2026 | select `includeGroup` from `tiki_group_inclusion` where `groupName`=? |
| 6.52072 | 2004 | select `userId` from `users_users` where `login`=? |
| 6.84396 | 2004 | select `groupName` from `users_usergroups` where `userId`=? |


select sum(qcount),sum(qtime) from tiki_querystats;
| 6159 | 19.166826486588 |


You see the probem?

Flo
-------

From the mailing list John Thomspon suggests:

Mysql has an option to log all slow queries and all queries that don't use indexes (i.e. queries
that sequentially search an entire table)
To enable this logging in /etc/my.cnf in the mysqld section add: 

    log-long-format
    set-variable = long_query_time=2
    log-slow-queries=/var/log/mysql_slow_query.log


note: Be aware that this will slow down your mysql while logging is on. After finding some long queries you should turn logging off again.

restart mysql

the email is attached in the comment area

safe time

to safe you some time here is what i found:
i logged 20 minutes of queries (on my site thats 26.000) and anelised them. i found the following indexes missing from 1.8.6 (see my
discussion:

ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `topicId` (`topicId`);

ALTER TABLE `ijbrug`.`users_permissions` ADD INDEX `type` (`type`);
ALTER TABLE `ijbrug`.`tiki_forum_attachments` ADD INDEX `threadId` (`threadId`);
ALTER TABLE `ijbrug`.`users_users` ADD INDEX `login` (`login`);
ALTER TABLE `ijbrug`.`tiki_modules` ADD INDEX `name` (`name`);
ALTER TABLE `ijbrug`.`tiki_user_assigned_modules` ADD INDEX `position` (`position`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `publishDate` (`publishDate`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `expireDate` (`expireDate`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `type` (`type`);
ALTER TABLE `ijbrug`.`tiki_article_types` ADD INDEX `show_pre_publ` (`show_pre_publ`);
ALTER TABLE `ijbrug`.`tiki_article_types` ADD INDEX `show_post_expire` (`show_post_expire`);
ALTER TABLE `ijbrug`.`tiki_comments` ADD INDEX `objectType`(`objectType`);
ALTER TABLE `ijbrug`.`tiki_comments` ADD INDEX `commentDate`(`commentDate`);
ALTER TABLE `ijbrug`.`tiki_modules` ADD INDEX `position`(`position`);
ALTER TABLE `ijbrug`.`tiki_modules` ADD INDEX `type`(`type`);
ALTER TABLE `ijbrug`.`tiki_link_cache` ADD INDEX `url`(`url`);
ALTER TABLE `ijbrug`.`tiki_articles` ADD INDEX `author`(`author`);
ALTER TABLE `ijbrug`.`tiki_sessions` ADD INDEX `user`(`user`);
ALTER TABLE `ijbrug`.`tiki_galleries` ADD INDEX `visible`(`visible`);
ALTER TABLE `ijbrug`.`tiki_galleries` ADD INDEX `user`(`user`);
ALTER TABLE `ijbrug`.`tiki_galleries` ADD INDEX `public`(`public`);
ALTER TABLE `ijbrug`.`messu_messages` ADD INDEX `user`(`user`);
ALTER TABLE `ijbrug`.`messu_messages` ADD INDEX `isRead`(`isRead`);


made my site a lot faster😂