Thinkup 0.5 Beta Update causing mysql to max out CPU

[zuhaib]

So early this morning I notice major performance slow down on my EC2
instance and look in to the server I saw mysql pegged at 100%. After
I killed it and removed the cron job I ran a manually update and go
the same result. Checking mytop I see this query taking up CPUs

IN SELECT user_id AS followee_id, follower_id FROM tu_follows AS f
WHERE network='twitter' AND active = 1 ORDER BY f.last_seen ASC LIMIT
1:

*** row 1 ***
table: f
type: range
possible_keys: active
key: active
key_len: 4
ref: NULL
rows: 1251282
Extra: Using where; Using filesort

This is a new issue as I have been running Thinkup since 0.3 Beta with
no issue but just after upgrading last night to 0.5 I am starting to
see this. I have not digged deeper yet but any idea where I should
start or is this something others are seeing?

Thanks,
Zuhaib

[Amy]

I have a guess about what might be going on. I've noticed (prior to 0.5 also) that the FollowMySQLDAO->countTotalFriends query:

$q = "SELECT count(f.user_id) AS count FROM #prefix#follows AS f ";
$q .= "WHERE f.follower_id = :userid AND f.network=:network ";

is very slow to run on my db, taking e.g. 10 mins (though in my case it doesn't use much cpu).
This query is on the tu_follows table also.

I'm thinking that tu_follows needs one or maybe two more indexes:
ALTER TABLE `tu_follows` ADD INDEX ( `network`, `active` );
and maybe also
ALTER TABLE `tu_follows` ADD INDEX ( `network` );
though I think the first of the two is the most useful.

I added these today, and early indications are that this has fixed my slowdown issue.
I don't believe there should be any harm in adding these indexes even if they don't fix your particular issue. It might take a bit of time to add them if you have a lot of data.

Gina, what do you think?

[Amy]

Actually, just to further muddy the waters :), the 'countTotalFriends' query below could probably benefit from a tu_follows index on (user_id, network), though the index on just 'network' alone does seem to help in my case.

[I guess the more indexes you add, the longer the write time... but the faster the query time]

Also-- should the query in countTotalFriends query be checking for 'active = 1' too?

[Sam Rose]

"[I guess the more indexes you add, the longer the write time... but the faster the query time]"

Yes, this is correct :)

There is also a quirk in MySQL where indexes on two columns can also act as an index on a single column (this only works in order from left to right (or first to last, whichever you prefer)). This extends with the more columns you have.

Example:

Index on columns "name" and "id" (in that order) also acts as an index on just "name".

Index on columns "name", "date" and "id" (in that order) also acts as an index on "name" and "date" (as a two column index) and for the single column "name".

The MySQL documentation explains it really well: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

--
You received this message because you are subscribed to the Google
Groups "ThinkUp App" group.
http://groups.google.com/group/thinkupapp?hl=en

Find out more about ThinkUp:
http://thinkupapp.com

[Amy]

On 26 Nov, 2010, at 5:43 PM, Sam Rose wrote:

> There is also a quirk in MySQL where indexes on two columns can also act as
> an index on a single column (this only works in order from left to right (or
> first to last, whichever you prefer)).

I didn't realize that!

In that case we can be smarter about adding indexes to tu_follows.
There is already a unique key: (user_id,follower_id,network)

So sounds like we should redefine this as (network, user_id, follower_id).
I'll give that a try in my installation.

[Sam Rose]

You can also use the "EXPLAIN SELECT" SQL command to see what indexes your queries are using.

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Near the bottom of that link gives an okay explanation of what the EXPLAIN syntax does :) It's a little bit hard to understand exactly what the output means but the "key" column should be fairly self explanatory and should help with identifying whether or not your query is going through the right index :)

--

[Amy]

On 26 Nov, 2010, at 7:04 PM, Sam Rose wrote:

> You can also use the "EXPLAIN SELECT" SQL command to see what indexes your
> queries are using.

That's really useful. It appears that both my problematic query:
SELECT count(f.user_id) AS count FROM temp_tu_follows AS f WHERE f.follower_id = 123456 AND f.network='twitter';
and Zuhaib's:
SELECT user_id AS followee_id, follower_id FROM temp_tu_follows AS f WHERE network='twitter' AND active = 1 ORDER BY f.last_seen ASC LIMIT 1;
were not using indexes.

For Zuhaib's query, indexing 'last_seen' produced dramatic speedups on my database.

I think all our queries will constrain on 'network', so perhaps 'network' should be listed first in all the indexes for speed. At any rate, the
following set of indexes works better for me. But, I haven't looked over all the tu_follows queries (which is what we would want to do to generate an optimal set of indexes).

CREATE TABLE tu_follows (
user_id bigint(11) NOT NULL,
follower_id bigint(11) NOT NULL,
last_seen timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
active int(11) NOT NULL DEFAULT '1',
network varchar(20) NOT NULL DEFAULT 'twitter',
debug_api_call varchar(255) NOT NULL,
UNIQUE KEY user_id (network, follower_id, user_id),
KEY last_seen (network, last_seen),
KEY active (network, active)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--Amy

[zuhaib]

Amy,

So I made the changes to tu_follows as you mentioned in the last post
and the performance issues with mysql are gone.

Thanks,
Zuhaib

[Gina Trapani]

On Sun, Dec 19, 2010 at 4:45 PM, zuhaib <zsid...@gmail.com> wrote:
> So I made the changes to tu_follows as you mentioned in the last post
> and the performance issues with mysql are gone.

Looks like these index updates are worth doing, then--thanks Amy. I've
created an issue:

https://github.com/ginatrapani/ThinkUp/issues/issue/517

At some point I'd like to set up a general SQL optimization test suite
which fills the tables with a few million rows and then runs all the
DAO queries against them a few hundred times and times the results so
we can systematically adjust queries and indexes and surface problems
like this. Anyone have any experience with this sort of thing?

--
http://ginatrapani.org
http://twitter.com/ginatrapani