pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed: Create table taking time
8+ messages / 5 participants
[nested] [flat]
* : Create table taking time
@ 2011-09-28 17:06 Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 3 replies; 8+ messages in thread
From: Venkat Balaji @ 2011-09-28 17:06 UTC (permalink / raw)
To: pgsql-performance
Hello Everyone,
I am back with an issue (likely).
I am trying to create a table in our production database, and is taking 5
seconds.
We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
TABLE to be faster after ANALYZE finishes ?
Or is there anything serious ?
Please share your thoughts.
Thanks
VB
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-28 18:55 Josh Berkus <josh@agliodbs.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
2 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2011-09-28 18:55 UTC (permalink / raw)
To: pgsql-performance
> We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
> TABLE to be faster after ANALYZE finishes ?
The only way CREATE TABLE would be affected by VACUUM ANALYZE running is
if you're saturating either your CPU or your disk 100%. Which you may
be, on a low-end system.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-28 19:14 Scott Marlowe <scott.marlowe@gmail.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2011-09-28 19:14 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: pgsql-performance
On Wed, Sep 28, 2011 at 11:06 AM, Venkat Balaji <venkat.balaji@verse.in> wrote:
> Hello Everyone,
> I am back with an issue (likely).
> I am trying to create a table in our production database, and is taking 5
> seconds.
> We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
> TABLE to be faster after ANALYZE finishes ?
> Or is there anything serious ?
> Please share your thoughts.
Are your system tables heavily bloated?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-29 08:02 Venkat Balaji <venkat.balaji@verse.in>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Venkat Balaji @ 2011-09-29 08:02 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
We had performed VACUUM FULL and ANALYZE on the whole database.
Yes, the CPU is ticking at 99-100% when i see the top command.
But, we have 8 CPUs with 6 cores each.
Thanks
VB
On Thu, Sep 29, 2011 at 12:44 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Wed, Sep 28, 2011 at 11:06 AM, Venkat Balaji <venkat.balaji@verse.in>
> wrote:
> > Hello Everyone,
> > I am back with an issue (likely).
> > I am trying to create a table in our production database, and is taking 5
> > seconds.
> > We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the
> CREATE
> > TABLE to be faster after ANALYZE finishes ?
> > Or is there anything serious ?
> > Please share your thoughts.
>
> Are your system tables heavily bloated?
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-29 13:48 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2011-09-29 13:48 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance; Josh Berkus <josh@postgresql.org
Venkat Balaji <venkat.balaji@verse.in> wrote:
> We had performed VACUUM FULL and ANALYZE on the whole database.
Since you don't mention REINDEX, it seems likely that you've bloated
your indexes (potentially including the indexes on system tables).
That could lead to the symptoms you describe.
> Yes, the CPU is ticking at 99-100% when i see the top command.
>
> But, we have 8 CPUs with 6 cores each.
If you've pegged 48 CPUs, it might be interesting to get a profile
of where the time is being spent. Are you able to run oprofile or
something similar?
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-29 16:52 Merlin Moncure <mmoncure@gmail.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
2 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2011-09-29 16:52 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: pgsql-performance
On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji <venkat.balaji@verse.in> wrote:
> Hello Everyone,
> I am back with an issue (likely).
> I am trying to create a table in our production database, and is taking 5
> seconds.
> We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
> TABLE to be faster after ANALYZE finishes ?
> Or is there anything serious ?
just ruling out something obvious -- this is vanilla create table, not
CREATE TABLE AS SELECT...?
also, what's i/o wait -- are you sure your not i/o bound and waiting
on transaction commit?
merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-30 05:22 Venkat Balaji <venkat.balaji@verse.in>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Venkat Balaji @ 2011-09-30 05:22 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
I did not calculate the IO behavior of the server.
What i noticed for the logs is that, the checkpoints are occurring too
frequently each checkpoint is taking up to minimum 80 - 200+ seconds to
complete write and checkpoint sync is taking 80 - 200+ seconds to sync,
which is i believe IO intensive.
Thanks
VB
On Thu, Sep 29, 2011 at 10:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji <venkat.balaji@verse.in>
> wrote:
> > Hello Everyone,
> > I am back with an issue (likely).
> > I am trying to create a table in our production database, and is taking 5
> > seconds.
> > We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the
> CREATE
> > TABLE to be faster after ANALYZE finishes ?
> > Or is there anything serious ?
>
> just ruling out something obvious -- this is vanilla create table, not
> CREATE TABLE AS SELECT...?
>
> also, what's i/o wait -- are you sure your not i/o bound and waiting
> on transaction commit?
>
> merlin
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: : Create table taking time
@ 2011-09-30 11:20 Venkat Balaji <venkat.balaji@verse.in>
parent: Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 0 replies; 8+ messages in thread
From: Venkat Balaji @ 2011-09-30 11:20 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
CPU load was hitting 100% constantly with high IOs.
We tuned some queries to decrease the CPU usage and everything is normal
now.
Thanks
VB
On Fri, Sep 30, 2011 at 10:52 AM, Venkat Balaji <venkat.balaji@verse.in>wrote:
> I did not calculate the IO behavior of the server.
>
> What i noticed for the logs is that, the checkpoints are occurring too
> frequently each checkpoint is taking up to minimum 80 - 200+ seconds to
> complete write and checkpoint sync is taking 80 - 200+ seconds to sync,
> which is i believe IO intensive.
>
> Thanks
> VB
>
>
>
> On Thu, Sep 29, 2011 at 10:22 PM, Merlin Moncure <mmoncure@gmail.com>wrote:
>
>> On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji <venkat.balaji@verse.in>
>> wrote:
>> > Hello Everyone,
>> > I am back with an issue (likely).
>> > I am trying to create a table in our production database, and is taking
>> 5
>> > seconds.
>> > We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the
>> CREATE
>> > TABLE to be faster after ANALYZE finishes ?
>> > Or is there anything serious ?
>>
>> just ruling out something obvious -- this is vanilla create table, not
>> CREATE TABLE AS SELECT...?
>>
>> also, what's i/o wait -- are you sure your not i/o bound and waiting
>> on transaction commit?
>>
>> merlin
>>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-09-30 11:20 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-28 17:06 : Create table taking time Venkat Balaji <venkat.balaji@verse.in>
2011-09-28 18:55 ` Josh Berkus <josh@agliodbs.com>
2011-09-28 19:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-09-29 08:02 ` Venkat Balaji <venkat.balaji@verse.in>
2011-09-29 13:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-09-29 16:52 ` Merlin Moncure <mmoncure@gmail.com>
2011-09-30 05:22 ` Venkat Balaji <venkat.balaji@verse.in>
2011-09-30 11:20 ` Venkat Balaji <venkat.balaji@verse.in>
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