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.
We are now starting work on Tikiwiki 1.10 to address some areas of parsing to make Tikiwiki faster.
CREATE TABLE tiki_querystats (
qcount int(11) default NULL,
qtext TEXT default NULL,
qtime float default NULL,
UNIQUE KEY qtext 255
)
select qtime/qcount,qtext from tiki_querystats order by 1
select qcount,qtime,qtext from tiki_querystats order by 1;
select qtime,qcount,qtext from tiki_querystats order by 1;
delete from tiki_querystats;
select sum(qcount) from tiki_querystats;
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
-------
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
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`.`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😂
1) |
18 Jul 2024 14:00 GMT-0000
Tiki Roundtable Meeting |
2) |
15 Aug 2024 14:00 GMT-0000
Tiki Roundtable Meeting |
3) |
19 Sep 2024 14:00 GMT-0000
Tiki Roundtable Meeting |
4) |
Tiki birthday |
5) |
17 Oct 2024 14:00 GMT-0000
Tiki Roundtable Meeting |
6) |
21 Nov 2024 14:00 GMT-0000
Tiki Roundtable Meeting |
7) |
19 Dec 2024 14:00 GMT-0000
Tiki Roundtable Meeting |