From: Karl Wright <kwright@metacarta.com>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Shaun Thomas <sthomas@leapfrogonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Thu, 21 Jun 2007 12:29:49 -0400
Message-ID: <467AA77D.3030204@metacarta.com> (raw)
In-Reply-To: <467A9CE5.508@g2switchworks.com>
References: <4676DD57.7070304@metacarta.com>
<20070620175300.GN30369@alvh.no-ip.org>
<46796A08.8080601@metacarta.com>
<200706201425.28557.sthomas@leapfrogonline.com>
<46799C45.7080805@metacarta.com>
<4679AE24.9070005@g2switchworks.com>
<4679B6C7.3080207@metacarta.com>
<467A9CE5.508@g2switchworks.com>
Scott Marlowe wrote:
> Karl Wright wrote:
>> Scott Marlowe wrote:
>>> Karl Wright wrote:
>>>
>>>> Shaun Thomas wrote:
>>>>
>>>>> On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote:
>>>>>
>>>>>
>>>>>> I am afraid that I did answer this. My largest tables
>>>>>> are the ones continually being updated. The smaller
>>>>>> ones are updated only infrequently.
>>>>>
>>>>>
>>>>>
>>>>> You know, it actually sounds like you're getting whacked by the
>>>>> same problem that got us a while back. It sounds like you weren't
>>>>> vacuuming frequently enough initially, and then tried vacuuming
>>>>> later, only after you noticed performance degrade.
>>>>>
>>>>> Unfortunately what that means, is for several weeks or months,
>>>>> Postgres has not been reusing rows on your (admittedly) active and
>>>>> large tables; it just appends at the end, and lets old rows slowly
>>>>> bloat that table larger and larger. Indexes too, will suffer from
>>>>> dead pages. As frightening/sickening as this sounds, you may need
>>>>> to dump/restore the really huge table, or vacuum-full to put it on
>>>>> a crash diet, and then maintain a strict daily or bi-daily vacuum
>>>>> schedule to keep it under control.
>>>>>
>>>>
>>>> A nice try, but I had just completed a VACUUM on this database three
>>>> hours prior to starting the VACUUM that I gave up on after 27
>>>> hours. So I don't see how much more frequently I could do it. (The
>>>> one I did earlier finished in six hours - but to accomplish that I
>>>> had to shut down EVERYTHING else that machine was doing.)
>>>
>>>
>>> So, have you ever run vacuum full or reindex on this database?
>>>
>>
>> No. However, this database has only existed since last Thursday
>> afternoon.
> Well, a couple of dozen update statements with no where clause on large
> tables could bloat it right up.
>
> It's not about age so much as update / delete patterns.
>>
>>> You are aware of the difference between how vacuum and vacuum full
>>> work, right?
>>>
>>> vacuum := mark deleted tuples as available, leave in table
>>> vacuum full := compact tables to remove deleted tuples.
>>>
>>> While you should generally avoid vacuum full, if you've let your
>>> database get so bloated that the majority of space in your tables is
>>> now empty / deleted tuples, you likely need to vacuuum full / reindex
>>> it.
>>>
>> If the database is continually growing, should VACUUM FULL be necessary?
> If it's only growing, with no deletes or updates, then no. Generally,
> on a properly vacuumed database, vacuum full should never be needed.
>>> For instance, on my tiny little 31 Gigabyte reporting database, the
>>> main table takes up about 17 Gigs. This query gives you some idea
>>> how many bytes each row is taking on average:
>>>
>>> select relname, relpages::float*8192 as size, reltuples,
>>> (relpages::double precision*8192)/reltuples::double precision as
>>> bytes_per_row from pg_class where relname = 'businessrequestsummary';
>>> relname | size | reltuples | bytes_per_row
>>> ------------------------+-------------+-------------+-----------------
>>> businessrequestsummary | 17560944640 | 5.49438e+07 | 319.61656229454
>>>
>>> Note that these numbers are updated by running analyze...
>>>
>>> What does it say about your DB?
>>>
>>
>> I wish I could tell you. Like I said, I had to abandon this project
>> to test out an upgrade procedure involving pg_dump and pg_restore.
>> (The upgrade also seems to take a very long time - over 6 hours so
>> far.) When it is back online I can provide further information.
>
> Well, let us know. I would definitely recommend getting more / faster
> disks. Right now I've got a simple 4 disk RAID10 on the way to replace
> the single SATA drive I'm running on right now. I can't wait.
>
I checked the disk picture - this is a RAID disk array with 6 drives,
with a bit more than 1Tbyte total storage. 15,000 RPM. It would be
hard to get more/faster disk than that.
Karl
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kwright@metacarta.com, smarlowe@g2switchworks.com, sthomas@leapfrogonline.com
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <467AA77D.3030204@metacarta.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox