550451 - database slaves can't keep up with affiliates processing, lack of index on temporary table

Status: RESOLVED FIXED

Description

[Dave Miller [:justdave] ([e-mail])]

Created attachment 430591 [details] [diff] [review]
Patch

The affiliates processing code that deals with the new_points_tally table takes sufficiently long enough to run that it takes the database slaves several hours to replicate it.  I tracked this down to a lack of indexes on the new_points_tally temporary table.  When I added an index to this table in the database earlier tonight, it completely caught up within minutes instead of continuing to lag for several more hours.

Unfortunately, as this is a temporary table, this problem will repeat itself every time the processing code runs, since the index I added will go away when it resets the table.

The attached patch causes the index to get added when the table is recreated.

Comment 1

[Alex Buchanan [:abuchanan]]

Comment on attachment 430591 [details] [diff] [review]
Patch

r63771

Comment 2

[Alex Buchanan [:abuchanan]]

r63772 on production.

Justdave, want to svn up this one?

Comment 3

[Alex Buchanan [:abuchanan]]

filed bug 551246 for updating production