Page 1 of 1

PSA: indexes and high CPU usage

Posted: 21 Jan 2015, 00:23
by Masiosare
If you are facing high cpu usage and you have lots of feeds and entries (coff, hoarders, coff), make sure to add an index to the ttrss_entries.guid column.

I went from a 100% cpu usage to a 10% just adding an index

Code: Select all

ALTER TABLE `ttrss_entries` ADD INDEX ( `guid` )


Before:

Code: Select all

mysql> explain sELECT id FROM ttrss_entries                                         WHERE (guid = '2,http://www.php-security.org/MOPB/MOPB-03-2007.html' OR guid = 'SHA1:8fa687a1720fc151af4056b6b43dc159f611f79a');
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ttrss_entries | ALL  | NULL          | NULL | NULL    | NULL | 573847 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+


Also, my update.php went drastically faster. From:

Code: Select all

[20:46:08/31817] Processed 222 feeds in 2693.9792 (sec), 12.1350 (sec/feed avg)


To

Code: Select all

[21:22:39/1878] Processed 50 feeds in 18.5814 (sec), 0.3716 (sec/feed avg)


After:

Code: Select all

;
+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table         | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ttrss_entries | range | guid          | guid | 767     | NULL |    2 | Using where; Using index |
+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+

Re: PSA: indexes and high CPU usage

Posted: 21 Jan 2015, 02:04
by sleeper_service
mine already had an index on guid...

'course I'm running postgres like a good little ttrsser, dunno if that matters.

Re: PSA: indexes and high CPU usage

Posted: 21 Jan 2015, 11:43
by ZeGuigui
I also have the index using MySQL

Re: PSA: indexes and high CPU usage

Posted: 21 Jan 2015, 11:51
by fox
why is everything that starts with psa: is inevitably something dumb?

Re: PSA: indexes and high CPU usage

Posted: 21 Jan 2015, 17:42
by sleeper_service
fox wrote:why is everything that starts with psa: is inevitably something dumb?


perhaps it stands for

Pretty Stupid Announcement ?

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 02:55
by Masiosare
Oh. I see the problem. The change was made on the trunk version, schema version 127.

The guid index was dropped from the create scripts and a drop index was added.

https://github.com/gothfox/Tiny-Tiny-RS ... 122543990a

It was also dropped from the pgsql version, but I don't know if it's duplicated or something.

AFAIK, from a fast grep there's not other index on the guid column.

Code: Select all

/schema# fgrep -R guid *
ttrss_schema_mysql.sql: guid varchar(255) not null unique,
ttrss_schema_pgsql.sql: guid text not null unique,
versions/mysql/127.sql:ALTER TABLE ttrss_entries DROP INDEX ttrss_entries_guid_index;
versions/pgsql/127.sql:DROP INDEX ttrss_entries_guid_index;


Also, fuck everyone :D

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 03:34
by JustAMacUser
Duplicate indexes were dropped. guid is UNIQUE which makes it indexed.

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 04:43
by sleeper_service
Masiosare wrote:Also, fuck everyone :D


I presume you're including yourself in that, eh?

did you READ the change you called out?

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 11:49
by fox
Masiosare wrote:Also, fuck everyone :D


go away

e: that would be 7 days ban i guess maybe you'll learn to post like you aren't 12 years old

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 13:36
by blainemono
also fuck you

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 13:36
by blainemono
also everyone else I guess, what the hell

Re: PSA: indexes and high CPU usage

Posted: 22 Jan 2015, 18:55
by sleeper_service
blainemono wrote:also everyone else I guess, what the hell


might miss someone deserving otherwise :)