PSA: indexes and high CPU usage

Development-related discussion, including bundled plugins
Masiosare
Bear Rating Trainee
Bear Rating Trainee
Posts: 22
Joined: 23 May 2013, 01:08

PSA: indexes and high CPU usage

Postby Masiosare » 21 Jan 2015, 00:23

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 |
+----+-------------+---------------+-------+---------------+------+---------+------+------+--------------------------+

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: PSA: indexes and high CPU usage

Postby sleeper_service » 21 Jan 2015, 02:04

mine already had an index on guid...

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

User avatar
ZeGuigui
BANNED_USERS
BANNED_USERS
Posts: 19
Joined: 15 Mar 2013, 17:30
Location: In the cloud!
Contact:

Re: PSA: indexes and high CPU usage

Postby ZeGuigui » 21 Jan 2015, 11:43

I also have the index using MySQL

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: PSA: indexes and high CPU usage

Postby fox » 21 Jan 2015, 11:51

why is everything that starts with psa: is inevitably something dumb?

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: PSA: indexes and high CPU usage

Postby sleeper_service » 21 Jan 2015, 17:42

fox wrote:why is everything that starts with psa: is inevitably something dumb?


perhaps it stands for

Pretty Stupid Announcement ?

Masiosare
Bear Rating Trainee
Bear Rating Trainee
Posts: 22
Joined: 23 May 2013, 01:08

Re: PSA: indexes and high CPU usage

Postby Masiosare » 22 Jan 2015, 02:55

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

JustAMacUser
Bear Rating Overlord
Bear Rating Overlord
Posts: 373
Joined: 20 Aug 2013, 23:13

Re: PSA: indexes and high CPU usage

Postby JustAMacUser » 22 Jan 2015, 03:34

Duplicate indexes were dropped. guid is UNIQUE which makes it indexed.

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: PSA: indexes and high CPU usage

Postby sleeper_service » 22 Jan 2015, 04:43

Masiosare wrote:Also, fuck everyone :D


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

did you READ the change you called out?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: PSA: indexes and high CPU usage

Postby fox » 22 Jan 2015, 11:49

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

User avatar
blainemono
Gaping Anus of Eternity
Gaping Anus of Eternity
Posts: 246
Joined: 05 Jun 2009, 18:13

Re: PSA: indexes and high CPU usage

Postby blainemono » 22 Jan 2015, 13:36

also fuck you

User avatar
blainemono
Gaping Anus of Eternity
Gaping Anus of Eternity
Posts: 246
Joined: 05 Jun 2009, 18:13

Re: PSA: indexes and high CPU usage

Postby blainemono » 22 Jan 2015, 13:36

also everyone else I guess, what the hell

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: PSA: indexes and high CPU usage

Postby sleeper_service » 22 Jan 2015, 18:55

blainemono wrote:also everyone else I guess, what the hell


might miss someone deserving otherwise :)


Return to “Development”

Who is online

Users browsing this forum: No registered users and 1 guest