MySQL Performance

by bekoehn, Friday, September 06, 2013, 05:55 (3745 days ago)

Is anybody else encountering extremely heavy MySQL loads during updates? My CPU is completely pegged for several minutes while mysqld uses over half of all available CPU. Is it possible I'm missing some indices? Any suggestions? I have roughly 100 feeds total.

MySQL Performance

by bekoehn, Wednesday, September 11, 2013, 17:26 (3739 days ago) @ bekoehn

I was asked to provide some context for my issue, so here:

1.7 GB RAM (Amazon EC2 Small Instance)
Mysql 5.5.32
PHP 5.4.9
Apache 2.2.22
mod-fcgid 2.3.7
SO 2.8

I stripped out a bunch of inactive feeds, but SO is still using all CPU between PHP and MySQL for about 30 seconds during updates. I'm not sure how to diagnose the issue; I'm a very experienced developer but not with PHP or MySQL.

I'm running the update via cron:
*/5 * * * * php /var/www/selfoss/update.php

MySQL Performance

by bekoehn, Thursday, October 03, 2013, 20:28 (3717 days ago) @ bekoehn

OK, I think I have this figured out.

When update runs, it apparently fetches each feed and compares the items in the feed to the items in the 'items' table in the database. Which is fine, in concept, except that it is doing this one item at a time:

SELECT COUNT(*) AS amount FROM items WHERE uid=''
SELECT COUNT(*) AS amount FROM items WHERE uid=''

This is extremely inefficient. I have over 10,000 rows in the items table; selfoss is running over 10,000 queries for every update, when almost every update simply returns a 1.

You could increase speed my an order of magnitude by querying n items at a time (where n is the number of items that can be put into an IN clause):

select uid from items where uid in ('', '', ...)

Put the results into a set. If any item from the feed isn't in the set you know you need to fetch that item.

The API for the Items DAO needs to change, as does the query it runs. But it should be massively faster. I'll see what I can do.

MySQL Performance

by bekoehn, Friday, October 04, 2013, 00:51 (3717 days ago) @ bekoehn

I fixed it in my github fork ( and submitted a pull request (

I went from 20,000 mysql queries per update down to 100, and from 100% CPU for 1.5 minutes down to 1% cpu for 8 seconds.

MySQL Performance

by MidSpeck, Friday, October 04, 2013, 02:43 (3717 days ago) @ bekoehn

Nice! I hope it's committed.

RSS Feed of thread
powered by my little forum