Postgres Pro
Downloads
Home   >   mailing lists

Thread: : Create table taking time

: Create table taking time

From
Venkat Balaji
Date:
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

Re: : Create table taking time

From
Josh Berkus
Date:
> 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

Re: : Create table taking time

From
Scott Marlowe
Date:
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?

Re: : Create table taking time

From
Venkat Balaji
Date:
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?

Re: : Create table taking time

From
"Kevin Grittner"
Date:
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

Re: : Create table taking time

From
Merlin Moncure
Date:
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

Re: : Create table taking time

From
Venkat Balaji
Date:
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

Re: : Create table taking time

From
Venkat Balaji
Date:
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