db2 slowness

[Toshio Kuratomi]

Matt Domsch wrote:
> On Sun, May 11, 2008 at 08:40:41AM -0500, Matt Domsch wrote:
>> On Sun, May 11, 2008 at 08:28:29AM -0500, Matt Domsch wrote:
>>> Anyone else seeing db2 be pretty slow?  Like >20 minutes for the MM
>>> publiclist pages to generate (per page!) and the inability to log into
>>> the MM admin web interface it's taking so long...  This has caused the
>>> publiclist and mirrorlists to not be regenerated in about 24 hours.
>>>
>>> db2 load average is ~9 right now, which doesn't seem nuts...
>> It's local to app4. I restarted MM there and things seem to be back to
>> normal.
> 
> Or not really; at 20 minutes into the publiclist pages generation,
> it's gotten through 2 of the pages...
> 
> https://fedorahosted.org/fedora-infrastructure/ticket/539
> filed.
> 
The db tables look really dirty:

mirrormanager=# select * from pgstattuple('host_category_dir');
-[ RECORD 1 ]------+----------
table_len          | 733134848
tuple_count        | 200905
tuple_len          | 17617190
tuple_percent      | 2.4
dead_tuple_count   | 7408720
dead_tuple_len     | 649852556
dead_tuple_percent | 88.64
free_space         | 2734892
free_percent       | 0.37

The hourly cron job that's vacuuming these tables has to go through 
three that are very large.  And it's still chugging away at this one 
after most of an hour.

I think that the db needs a vacuum full to trim off the 600MB of dead 
tuples.  I'm shutting down the mirrormanager admin interface and doing 
that to deal with this.  Holler if something besides the admin interface 
breaks because of this.

-Toshio