Database performance

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

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 TikiBoosting for more information.
• Sometimes some code is badly written and the database performance harms tiki performance. Then read on.

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));
  Then edit tikilib 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
-------

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