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

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='http://www.bbc.co.uk/news/business-24369542'
SELECT COUNT(*) AS amount FROM items WHERE uid='http://www.bbc.co.uk/news/in-pictures-24329416'
...

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 ('http://www.bbc.co.uk/news/business-24369542', 'http://www.bbc.co.uk/news/in-pictures-24329416', ...)

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.

I fixed it in my github fork (https://github.com/koehn/selfoss) and submitted a pull request (https://github.com/SSilence/selfoss/pull/438).

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.