Found a way to speed up pligg

[tonuonu]

There is a one query which makes pligg slow. Found a way to speed it up a lot.

Was playing around MySQL optimization on pligg. Found one key which
improves things a lot. Query is ran on showing story related news. Let
see, I do it before and after:

mysql> SELECT link_id,digg_links.link_title, digg_links.link_title_url,
COUNT( digg_tags.tag_link_id ) AS relevance, digg_tags.tag_link_id FROM
digg_tags, digg_links WHERE digg_tags.tag_words IN ( 'prygi',
'voistlus', 'oko', 'leedu', 'eesti' ) AND digg_tags.tag_link_id =
digg_links.link_id AND digg_links.link_status != 'discard' AND NOT
digg_links.link_id = 334 GROUP BY digg_tags.tag_link_id,
digg_links.link_title, digg_links.link_title_url ORDER BY relevance DESC
LIMIT 10;
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| link_id | link_title
| link_title_url
| relevance | tag_link_id |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| 16047 | Rumeenlased hakkasid Baltimaade õhuruumi valvama
| Rumeenlased_hakkasid_Baltimaade_ohuruumi_valvama
| 2 | 16047 |
| 39717 | Leedulased tahavad prügikoristamises eestlastele ära teha
| leedulased-tahavad-prygikoristamises-eestlastele-ara-teha
| 2 | 39717 |
| 104 | Soomlased põletavad Eesti lippu
| Soomlased-pletavad-Eesti-lippu
| 1 | 104 |
| 184 | Valimisjaoskonda külastab Esto TV
| Valimisjaoskonda-klastab-Esto-TV
| 1 | 184 |
| 219 | Elu surnukuuri eesruumis
| Elu-surnukuuri-eesruumis
| 1 | 219 |
| 239 | Harju Maakohus kohustab IANA(Internet Assigned Numbers
Authorty).... |
Harju-Maakohus-kohustab-IANAInternet-Assigned-Numbers-Authorty
| 1 | 239 |
| 250 | Eesti mehe munandi suurus ja spermatosoidide hulk on
Euroopas juhtival kohal. |
Eesti_mehe_munandi_suurus_ja_spermatosoidide_hulk_ on_Euroopas_juhtival_kohal- | 1 | 250 |
| 309 | Eesti saite kutsutakse üles ründama
| Eesti-saite-kutsutakse-les-rndama
| 1 | 309 |
| 311 | 70 noort sulges Eesti-Vene piiri pooleks tunniks
| 70-noort-sulges-EestiVene-piiri-pooleks-tunniks
| 1 | 311 |
| 312 | Venemaa nõuab oma kodanike vabastamist
| Venemaa-nuab-oma-kodanike-vabastamist
| 1 | 312 |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
10 rows in set (0.61 sec)

mysql>

Half second! It gets query cached here but anyway it is slow.

Let see why it is slow:

mysql> explain SELECT link_id,digg_links.link_title,
digg_links.link_title_url, COUNT( digg_tags.tag_link_id ) AS relevance,
digg_tags.tag_link_id FROM digg_tags, digg_links WHERE
digg_tags.tag_words IN ( 'prygi', 'voistlus', 'oko', 'leedu', 'eesti' )
AND digg_tags.tag_link_id = digg_links.link_id AND
digg_links.link_status != 'discard' AND NOT digg_links.link_id = 334
GROUP BY digg_tags.tag_link_id, digg_links.link_title,
digg_links.link_title_url ORDER BY relevance DESC LIMIT 10;
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | digg_links | ALL | PRIMARY | NULL |
NULL | NULL | 4703 | Using where; Using temporary;
Using filesort |
| 1 | SIMPLE | digg_tags | ref | tag_link_id | tag_link_id | 4
| digg.digg_links.link_id | 1 | Using where; Using index
|
+----+-------------+------------+------+---------------+-------------+---------+-------------------------+------+----------------------------------------------+
2 rows in set (0.01 sec)

mysql>


Oh fu*k! It goes via 4703 rows to examine them

Adding one index:

mysql> alter table digg_tags add key tag_words (`tag_words`);Query OK,
14394 rows affected (14.24 sec)Records: 14394 Duplicates: 0 Warnings:
0mysql>

mysql> SELECT link_id,digg_links.link_title, digg_links.link_title_url,
COUNT( digg_tags.tag_link_id ) AS relevance, digg_tags.tag_link_id FROM
digg_tags, digg_links WHERE digg_tags.tag_words IN ( 'prygi',
'voistlus', 'oko', 'leedu', 'eesti' ) AND digg_tags.tag_link_id =
digg_links.link_id AND digg_links.link_status != 'discard' AND NOT
digg_links.link_id = 334 GROUP BY digg_tags.tag_link_id,
digg_links.link_title, digg_links.link_title_url ORDER BY relevance DESC
LIMIT 10;
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| link_id | link_title
| link_title_url
| relevance | tag_link_id |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
| 16047 | Rumeenlased hakkasid Baltimaade õhuruumi valvama
| Rumeenlased_hakkasid_Baltimaade_ohuruumi_valvama
| 2 | 16047 |
| 39717 | Leedulased tahavad prügikoristamises eestlastele ära teha
| leedulased-tahavad-prygikoristamises-eestlastele-ara-teha
| 2 | 39717 |
| 104 | Soomlased põletavad Eesti lippu
| Soomlased-pletavad-Eesti-lippu
| 1 | 104 |
| 184 | Valimisjaoskonda külastab Esto TV
| Valimisjaoskonda-klastab-Esto-TV
| 1 | 184 |
| 219 | Elu surnukuuri eesruumis
| Elu-surnukuuri-eesruumis
| 1 | 219 |
| 239 | Harju Maakohus kohustab IANA(Internet Assigned Numbers
Authorty).... |
Harju-Maakohus-kohustab-IANAInternet-Assigned-Numbers-Authorty
| 1 | 239 |
| 250 | Eesti mehe munandi suurus ja spermatosoidide hulk on
Euroopas juhtival kohal. |
Eesti_mehe_munandi_suurus_ja_spermatosoidide_hulk_ on_Euroopas_juhtival_kohal- | 1 | 250 |
| 309 | Eesti saite kutsutakse üles ründama
| Eesti-saite-kutsutakse-les-rndama
| 1 | 309 |
| 311 | 70 noort sulges Eesti-Vene piiri pooleks tunniks
| 70-noort-sulges-EestiVene-piiri-pooleks-tunniks
| 1 | 311 |
| 312 | Venemaa nõuab oma kodanike vabastamist
| Venemaa-nuab-oma-kodanike-vabastamist
| 1 | 312 |
+---------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+-----------+-------------+
10 rows in set (0.08 sec)

mysql>

MUCH BETTER! It can use indexes now:

mysql> explain SELECT link_id,digg_links.link_title,
digg_links.link_title_url, COUNT( digg_tags.tag_link_id ) AS relevance,
digg_tags.tag_link_id FROM digg_tags, digg_links WHERE
digg_tags.tag_words IN ( 'prygi', 'voistlus', 'oko', 'leedu', 'eesti' )
AND digg_tags.tag_link_id = digg_links.link_id AND
digg_links.link_status != 'discard' AND NOT digg_links.link_id = 334
GROUP BY digg_tags.tag_link_id, digg_links.link_title,
digg_links.link_title_url ORDER BY relevance DESC LIMIT 10;
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | digg_tags | range | tag_link_id,tag_words |
tag_words | 66 | NULL | 181 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | digg_links | eq_ref | PRIMARY |
PRIMARY | 4 | digg.digg_tags.tag_link_id | 1 | Using where
|
+----+-------------+------------+--------+-----------------------+-----------+---------+----------------------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

[Rodney]

So how exactly would you apply this index using phpmyadmin?

[xiesi]

how to do we speed up pligg?

[Geoserv]

A write up on how to implement would be great.

Geoserv.

[tonuonu]

Originally Posted by Rodney

So how exactly would you apply this index using phpmyadmin?

Look again:
alter table digg_tags add key tag_words (`tag_words`);

This means go to digg_tags table and add key on field tag_words. Simple as that.

[tonuonu]

One more idea. Table digg_pageviews grows fast and searches there are done to tell when you last time looked on some story. Query looks on fields pv_type, pv_page_id and pv_user_id fields to identify tiny final set of result fields. Only index it can use is defined:

KEY `pv_type` (`pv_type`,`pv_page_id`,`pv_user_id`)

So, first thing in key is pv_type which is defined as enum('story','out','profile') default NULL

Meanwhile
mysql> show keys from digg_pageviews;
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| digg_pageviews | 0 | PRIMARY | 1 | pv_id | A | 1536729 | NULL | NULL | | BTREE | |
| digg_pageviews | 1 | pv_type | 1 | pv_type | A | 18 | NULL | NULL | YES | BTREE | |
| digg_pageviews | 1 | pv_type | 2 | pv_page_id | A | 22598 | NULL | NULL | | BTREE | |
| digg_pageviews | 1 | pv_type | 3 | pv_user_id | A | 59104 | NULL | NULL | | BTREE | |
| digg_pageviews | 1 | pv_page_id | 1 | pv_page_id | A | 15680 | NULL | NULL | | BTREE | |
| digg_pageviews | 1 | pv_user_id | 1 | pv_user_id | A | 328 | NULL | NULL | | BTREE | |
+----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (1.40 sec)

mysql>

We see that cardinality of pv_type is low and query can run much better if it can use pv_user_id or pv_page_id fields. Which on, depends on exact query. For example Google crawler constantly looking on our pages and making new records in this table with pv_user_id=0. Then pv_page_id is better field because it is more unique. Sometime pv_user_id is better. So I added two more indexes to let query optimizer to choose from:

alter table digg_pageviews add key pv_page_id (pv_page_id), add key pv_user_id (pv_user_id);

Warning! Adding those indexes can be really slow and block whole site! I made it 4am at night automatically to avoid problems. After that change I cannot see related queries in mysql processlist anymore. This means, they went MUCH faster.

I wrote down more notes somewhere but cannot find them at moment. Anyway I identify slow queries just looking "show processlist" many times. If you see something there often, then it is probably slow and needs to be optimized.

[xiesi]

i can't understand.

[newsome]

Hello.

What I did was, in phpmyadmin, I entered these 2 statements shown above via the SQL tab. I did them one at a time and checked that it worked before doing the next one.

alter table digg_tags add key tag_words (`tag_words`);

alter table digg_pageviews add key pv_page_id (pv_page_id), add key pv_user_id (pv_user_id);

NOTE: You have to change the 'digg' part to whatever your table's prefix is.

HTH.

[tonuonu]

Originally Posted by xiesi

how to do we speed up pligg?

Adding important indexes to database tables. If you do not know what is MySQL and how to add indexes, you should consult someone who administering your database. This is bit specific stuff.

[tbones]

Originally Posted by newsome

Hello.

What I did was, in phpmyadmin, I entered these 2 statements shown above via the SQL tab. I did them one at a time and checked that it worked before doing the next one.

alter table digg_tags add key tag_words (`tag_words`);

alter table digg_pageviews add key pv_page_id (pv_page_id), add key pv_user_id (pv_user_id);

NOTE: You have to change the 'digg' part to whatever your table's prefix is.

HTH.

Do you perceive an improvement compared to the situation before?