pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres is using 100% CPU
17+ messages / 7 participants
[nested] [flat]
* Postgres is using 100% CPU
@ 2015-05-29 16:40 Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:17 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-29 17:18 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
0 siblings, 3 replies; 17+ messages in thread
From: Ashik S L @ 2015-05-29 16:40 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
Hi All,
I am using postgresDB on redhat machine which is having 4GB RAM
machine. As soon as it starts to update the postgres DB it will reach
100%cpu. It will comedown to normal after 40 minutes. I tried perform
some tuning on the postgres DB, But result was same.I am not postgres
DB expert. Even we are not seeing in all machine. Only few machines we
are seeing this issue. Any help on this would be appreciated.
Thanks,
Ashik
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 17:17 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 0 replies; 17+ messages in thread
From: Tomas Vondra @ 2015-05-29 17:17 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
Hi,
On 05/29/15 18:40, Ashik S L wrote:
> Hi All,
>
> I am using postgresDB on redhat machine which is having 4GB RAM
> machine. As soon as it starts to update the postgres DB it will reach
> 100%cpu. It will comedown to normal after 40 minutes. I tried perform
> some tuning on the postgres DB, But result was same.I am not postgres
> DB expert. Even we are not seeing in all machine. Only few machines we
> are seeing this issue. Any help on this would be appreciated.
We need to know more about what you mean by "update the postgres DB",
and basic information like database size, PostgreSQL version etc.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 17:18 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2 siblings, 0 replies; 17+ messages in thread
From: Tomas Vondra @ 2015-05-29 17:18 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
On 05/29/15 18:40, Ashik S L wrote:
> Hi All,
>
> I am using postgresDB on redhat machine which is having 4GB RAM
> machine. As soon as it starts to update the postgres DB it will reach
> 100%cpu. It will comedown to normal after 40 minutes. I tried perform
> some tuning on the postgres DB, But result was same.I am not postgres
> DB expert. Even we are not seeing in all machine. Only few machines we
> are seeing this issue. Any help on this would be appreciated.
... also, this is not a bug. This is clearly a performance question, so
please post it to pgsql-performance list.
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 17+ messages in thread
* Fwd: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 17:57 ` Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2 siblings, 1 reply; 17+ messages in thread
From: Ashik S L @ 2015-05-29 17:57 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I am using postgresDB on redhat machine which is having 4GB RAM
machine. As soon as it starts to Inserting rows into the postgres DB it
will reach
100%cpu. It will comedown to normal after 40 minutes. I tried perform
some tuning on the postgres DB, But result was same.I am not postgres
DB expert. Even we are not seeing in all machine. Only few machines we
are seeing this issue. Any help on this would be appreciated.
Thanks,
Ashik
^ permalink raw reply [nested|flat] 17+ messages in thread
* Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 18:10 ` Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:30 ` Re: Postgres is using 100% CPU Yves Dorfsman <yves@zioup.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 17+ messages in thread
From: Ashik S L @ 2015-05-29 18:10 UTC (permalink / raw)
To: pgsql-performance
Hi All,
I am using postgresDB on redhat machine which is having 4GB RAM
machine. As soon as it starts to Inserting rows into the postgres DB it
will reach 100%cpu. It will comedown to normal after 40 minutes. I tried perform
some tuning on the postgres DB, But result was same.I am not postgres
DB expert. Even we are not seeing in all machine. Only few machines we
are seeing this issue. Any help on this would be appreciated.
Thanks,
Ashik
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 18:30 ` Yves Dorfsman <yves@zioup.com>
1 sibling, 0 replies; 17+ messages in thread
From: Yves Dorfsman @ 2015-05-29 18:30 UTC (permalink / raw)
To: pgsql-performance
> machine. As soon as it starts to Inserting rows into the postgres DB it
> will reach 100%cpu. It will comedown to normal after 40 minutes. I tried perform
How many rows are you inserting at once? How (sql insert? copy? \copy? using a
temp or unlogged table?)?
--
http://yves.zioup.com
gpg: 4096R/32B0F416
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-29 19:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Tomas Vondra @ 2015-05-29 19:20 UTC (permalink / raw)
To: pgsql-performance
On 05/29/15 20:10, Ashik S L wrote:
> Hi All,
>
> I am using postgresDB on redhat machine which is having 4GB RAM
> machine. As soon as it starts to Inserting rows into the postgres DB
> it will reach 100%cpu. It will comedown to normal after 40 minutes. I
> tried perform some tuning on the postgres DB, But result was same.I
> am not postgres DB expert. Even we are not seeing in all machine.
> Only few machines we are seeing this issue. Any help on this would
> be appreciated.
Ashik, before pointing you to this list, I asked for some basic
information that are needed when diagnosing issues like this - database
size, postgres version etc. We can't really help you without this info,
because right now we only know you're doing some inserts (while before
you mentioned updates), and it's slow.
Also, can you please provide info about the configuration and what
changes have you done when tuning it?
Have you seen this?
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2015-05-30 13:46 ` Ashik S L <ashiksl178@gmail.com>
2015-05-30 14:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-31 13:04 ` Re: Postgres is using 100% CPU Jean-David Beyer <jeandavid8@verizon.net>
0 siblings, 2 replies; 17+ messages in thread
From: Ashik S L @ 2015-05-30 13:46 UTC (permalink / raw)
To: pgsql-performance
We are using postgres SQL version 8.4.17..
Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
each row is of 60 bytes.Every time we insert 16380 bytes of data.
I tried to make some config changes using above link. But I did not
see any improvement.
I made following changes in postgres.conf file:
shared_buffers = 512MB // It was 32MB
work_mem = 30MB
effective_cache_size = 512MB // I tried with 128MB 256MB also
Please let me know any config changes that I can try out.
Thanks,
Ashik
On 5/30/15, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>
>
> On 05/29/15 20:10, Ashik S L wrote:
>> Hi All,
>>
>> I am using postgresDB on redhat machine which is having 4GB RAM
>> machine. As soon as it starts to Inserting rows into the postgres DB
>> it will reach 100%cpu. It will comedown to normal after 40 minutes. I
>> tried perform some tuning on the postgres DB, But result was same.I
>> am not postgres DB expert. Even we are not seeing in all machine.
>> Only few machines we are seeing this issue. Any help on this would
>> be appreciated.
>
> Ashik, before pointing you to this list, I asked for some basic
> information that are needed when diagnosing issues like this - database
> size, postgres version etc. We can't really help you without this info,
> because right now we only know you're doing some inserts (while before
> you mentioned updates), and it's slow.
>
> Also, can you please provide info about the configuration and what
> changes have you done when tuning it?
>
> Have you seen this?
>
> https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-30 14:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
1 sibling, 0 replies; 17+ messages in thread
From: Tomas Vondra @ 2015-05-30 14:20 UTC (permalink / raw)
To: pgsql-performance
Hi,
On 05/30/15 15:46, Ashik S L wrote:
> We are using postgres SQL version 8.4.17..
FYI 8.4 is already unsupported for ~1 year, so you should consider
upgrading to a newer release. Also, the newest version in that branch is
8.4.22, so with 8.4.17 you're missing ~1 year of patches.
> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
> each row is of 60 bytes.Every time we insert 16380 bytes of data.
So you insert 273 rows and it gets stuck for 40 minutes? That's really
strange, and I find it rather unlikely even with a badly misconfigured
instance. It should easily insert thousands of rows per second.
Can you elaborate more about the database structure, or at least the
table(s) you're inserting into. Are there any foreign keys (in either
direction), indexes or triggers?
> I tried to make some config changes using above link. But I did not
> see any improvement.
> I made following changes in postgres.conf file:
> shared_buffers = 512MB // It was 32MB
> work_mem = 30MB
> effective_cache_size = 512MB // I tried with 128MB 256MB also
>
> Please let me know any config changes that I can try out.
I don't think this has anything to do with configuration. This seems
like an issue at the application level, or maybe poorly designed schema.
You mentioned you have multiple machines, and only some of them are
having this issue. What are the differences between the machines? Are
all the machines using the same schema? I assume each has a different
amount of data.
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-05-31 13:04 ` Jean-David Beyer <jeandavid8@verizon.net>
2015-05-31 14:23 ` Re: Postgres is using 100% CPU Yves Dorfsman <yves@zioup.com>
1 sibling, 1 reply; 17+ messages in thread
From: Jean-David Beyer @ 2015-05-31 13:04 UTC (permalink / raw)
To: pgsql-performance
On 05/30/2015 09:46 AM, Ashik S L wrote:
> We are using postgres SQL version 8.4.17..
> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
> each row is of 60 bytes.Every time we insert 16380 bytes of data.
Way back when, I was inserting a lot of rows of date (millions of rows)
and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
SCSI hard drives and 8 GBytes of ram. Each insert was a separate
transaction.
When I bunched up lots of rows (thousaands) into a single transaction,
the whole thing took less than an hour.
Is it possible that when you insert 273 rows at once, you are doing it
as 273 transactions instead of one?
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key:166D840A 0C610C8B Registered Machine 1935521.
/( )\ Shrewsbury, New Jersey http://linuxcounter.net
^^-^^ 09:00:01 up 3 days, 9:57, 2 users, load average: 4.89, 4.90, 4.91
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-31 13:04 ` Re: Postgres is using 100% CPU Jean-David Beyer <jeandavid8@verizon.net>
@ 2015-05-31 14:23 ` Yves Dorfsman <yves@zioup.com>
2015-06-01 05:38 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Yves Dorfsman @ 2015-05-31 14:23 UTC (permalink / raw)
To: pgsql-performance
On 2015-05-31 07:04, Jean-David Beyer wrote:
> On 05/30/2015 09:46 AM, Ashik S L wrote:
>> We are using postgres SQL version 8.4.17..
>> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
>> each row is of 60 bytes.Every time we insert 16380 bytes of data.
>
> Way back when, I was inserting a lot of rows of date (millions of rows)
> and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
> SCSI hard drives and 8 GBytes of ram. Each insert was a separate
> transaction.
>
> When I bunched up lots of rows (thousaands) into a single transaction,
> the whole thing took less than an hour.
Or use copy, \copy if possible, or a "temporary" unlogged table to copy from
later, etc...
> Is it possible that when you insert 273 rows at once, you are doing it
> as 273 transactions instead of one?
That's the thing, even on an old laptop with a slow IDE disk, 273 individual
inserts should not take more than a second.
--
http://yves.zioup.com
gpg: 4096R/32B0F416
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-31 13:04 ` Re: Postgres is using 100% CPU Jean-David Beyer <jeandavid8@verizon.net>
2015-05-31 14:23 ` Re: Postgres is using 100% CPU Yves Dorfsman <yves@zioup.com>
@ 2015-06-01 05:38 ` Ashik S L <ashiksl178@gmail.com>
2015-06-01 13:20 ` Re: Postgres is using 100% CPU Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ashik S L @ 2015-06-01 05:38 UTC (permalink / raw)
To: Yves Dorfsman <yves@zioup.com>; +Cc: pgsql-performance
> On 05/30/2015 09:46 AM, Ashik S L wrote:
>> We are using postgres SQL version 8.4.17..
>> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
>> each row is of 60 bytes.Every time we insert 16380 bytes of data.
>
> Way back when, I was inserting a lot of rows of date (millions of rows)
> and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
> SCSI hard drives and 8 GBytes of ram. Each insert was a separate
> transaction.
>
> When I bunched up lots of rows (thousaands) into a single transaction,
> the whole thing took less than an hour.
Or use copy, \copy if possible, or a "temporary" unlogged table to copy from
later, etc...
> Is it possible that when you insert 273 rows at once, you are doing it
> as 273 transactions instead of one?
>That's the thing, even on an old laptop with a slow IDE disk, 273
individual
>inserts should not take more than a second.
We are inserting 273 rows at once and its taking less than 1 second. But we
will be updating bunch of 273 rows every time which is taking high cpu.
Its like updating 273 rows 2000 to 3000 times. We will be running multiple
instances of postgres as well.
On Sun, May 31, 2015 at 7:53 PM, Yves Dorfsman <yves@zioup.com> wrote:
> On 2015-05-31 07:04, Jean-David Beyer wrote:
> > On 05/30/2015 09:46 AM, Ashik S L wrote:
> >> We are using postgres SQL version 8.4.17..
> >> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
> >> each row is of 60 bytes.Every time we insert 16380 bytes of data.
> >
> > Way back when, I was inserting a lot of rows of date (millions of rows)
> > and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
> > SCSI hard drives and 8 GBytes of ram. Each insert was a separate
> > transaction.
> >
> > When I bunched up lots of rows (thousaands) into a single transaction,
> > the whole thing took less than an hour.
>
> Or use copy, \copy if possible, or a "temporary" unlogged table to copy
> from
> later, etc...
>
> > Is it possible that when you insert 273 rows at once, you are doing it
> > as 273 transactions instead of one?
>
> That's the thing, even on an old laptop with a slow IDE disk, 273
> individual
> inserts should not take more than a second.
>
> --
> http://yves.zioup.com
> gpg: 4096R/32B0F416
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-31 13:04 ` Re: Postgres is using 100% CPU Jean-David Beyer <jeandavid8@verizon.net>
2015-05-31 14:23 ` Re: Postgres is using 100% CPU Yves Dorfsman <yves@zioup.com>
2015-06-01 05:38 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
@ 2015-06-01 13:20 ` Merlin Moncure <mmoncure@gmail.com>
2015-06-01 19:35 ` Re: Postgres is using 100% CPU Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Merlin Moncure @ 2015-06-01 13:20 UTC (permalink / raw)
To: Ashik S L <ashiksl178@gmail.com>; +Cc: Yves Dorfsman <yves@zioup.com>; pgsql-performance
On Mon, Jun 1, 2015 at 12:38 AM, Ashik S L <ashiksl178@gmail.com> wrote:
>> On 05/30/2015 09:46 AM, Ashik S L wrote:
>>> We are using postgres SQL version 8.4.17..
>>> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
>>> each row is of 60 bytes.Every time we insert 16380 bytes of data.
>>
>> Way back when, I was inserting a lot of rows of date (millions of rows)
>> and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
>> SCSI hard drives and 8 GBytes of ram. Each insert was a separate
>> transaction.
>>
>> When I bunched up lots of rows (thousaands) into a single transaction,
>> the whole thing took less than an hour.
>
> Or use copy, \copy if possible, or a "temporary" unlogged table to copy from
> later, etc...
>
>> Is it possible that when you insert 273 rows at once, you are doing it
>> as 273 transactions instead of one?
>
>>That's the thing, even on an old laptop with a slow IDE disk, 273
>> individual
>>inserts should not take more than a second.
>
> We are inserting 273 rows at once and its taking less than 1 second. But we
> will be updating bunch of 273 rows every time which is taking high cpu.
> Its like updating 273 rows 2000 to 3000 times. We will be running multiple
> instances of postgres as well.
Something is wrong. This is not typical behavior. Let's rule out
some obvious things:
*) do you have any triggers on the table
*) is your insert based on complicated query
*) can you 'explain analyze' the statement you think is causing the cpu issues
*) is this truly cpu problem or iowait?
*) are there other queries running you are not aware of? let's check
the contents of 'pg_stat_activity' when cpu issues are happening
what operating system is this? if linux/unix, let's get a 'top'
profile and confirm (via pid) that will tell you if your problems are
proper cpu or storate based. On default configuration with slow
storage, 273 inserts/sec will be the maximum the hardware will support
while syncing every transaction (note, this is 'good', and there are
many techniques to work around the problem). Try flipping the
'synchronous_commit' setting in postgresql.conf to see if that
improves performance.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:57 ` Fwd: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 19:20 ` Re: Postgres is using 100% CPU Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-31 13:04 ` Re: Postgres is using 100% CPU Jean-David Beyer <jeandavid8@verizon.net>
2015-05-31 14:23 ` Re: Postgres is using 100% CPU Yves Dorfsman <yves@zioup.com>
2015-06-01 05:38 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-06-01 13:20 ` Re: Postgres is using 100% CPU Merlin Moncure <mmoncure@gmail.com>
@ 2015-06-01 19:35 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Scott Marlowe @ 2015-06-01 19:35 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Ashik S L <ashiksl178@gmail.com>; Yves Dorfsman <yves@zioup.com>; pgsql-performance
On Mon, Jun 1, 2015 at 7:20 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Jun 1, 2015 at 12:38 AM, Ashik S L <ashiksl178@gmail.com> wrote:
>>> On 05/30/2015 09:46 AM, Ashik S L wrote:
>>>> We are using postgres SQL version 8.4.17..
>>>> Postgres DB szie is 900 MB and we are inserting 273 rows at once .and
>>>> each row is of 60 bytes.Every time we insert 16380 bytes of data.
>>>
>>> Way back when, I was inserting a lot of rows of date (millions of rows)
>>> and it was taking many hours on a machine with 6 10,000 rpm Ultra/320
>>> SCSI hard drives and 8 GBytes of ram. Each insert was a separate
>>> transaction.
>>>
>>> When I bunched up lots of rows (thousaands) into a single transaction,
>>> the whole thing took less than an hour.
>>
>> Or use copy, \copy if possible, or a "temporary" unlogged table to copy from
>> later, etc...
>>
>>> Is it possible that when you insert 273 rows at once, you are doing it
>>> as 273 transactions instead of one?
>>
>>>That's the thing, even on an old laptop with a slow IDE disk, 273
>>> individual
>>>inserts should not take more than a second.
>>
>> We are inserting 273 rows at once and its taking less than 1 second. But we
>> will be updating bunch of 273 rows every time which is taking high cpu.
>> Its like updating 273 rows 2000 to 3000 times. We will be running multiple
>> instances of postgres as well.
>
> Something is wrong. This is not typical behavior. Let's rule out
> some obvious things:performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
OP has not convinced me there's an actual problem. How many inserts
per second / minute / hour can these machines handle? Are they
handling when they're at 100% CPU. 100% CPU isn't an automatically bad
thing. Every query is limited in some way. If you've got some
monstrous IO subsystem it's not uncommon for the CPU to be the big
limiter.
I'm not sure I've seen OP use the word slow anywhere... Just 100% CPU.
I'd say we need metrics from iostat, vmstat, iotop, top, htop and
performance numbers before deciding there IS a problem.
--
To understand recursion, one must first understand recursion.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
@ 2015-06-02 07:47 Graeme B. Bell <grb@skogoglandskap.no>
2015-06-02 07:58 ` Re: Postgres is using 100% CPU Graeme B. Bell <grb@skogoglandskap.no>
0 siblings, 1 reply; 17+ messages in thread
From: Graeme B. Bell @ 2015-06-02 07:47 UTC (permalink / raw)
To: pgsql-performance
On Sun, May 31, 2015 at 7:53 PM, Yves Dorfsman <yves@zioup.com> wrote:
>> That's the thing, even on an old laptop with a slow IDE disk, 273
> individual
>> inserts should not take more than a second.
>
I think that would depend on settings such as synchronous_commit, commit_delay, or whether 2-phase commit is being used.
If synchronous commit is enabled and commit_delay was not used (e.g. 0), and you have a client synchronously making individual inserts to the DB (1 transaction each), then surely you have delays due to waiting for each transaction to commit synchronously to WAL on disk?
I believe yes / 0 are the default settings for synchronous commit and commit_delay. (Interestingly the manual pages do not specify.)
Assuming a 5400RPM laptop drive (which is a typical drive - some laptop drives run < 5000RPM), and assuming you are writing a sequential log to disk (with very short gaps between entries being added, e.g. no seek time, only rotational latency) will mean 5400 transactions per minute, 1 write per rotation.
That's a maximum 90 transactions per second synchronised to WAL. It would take just over 3 seconds.
Ashik, try altering your postgresql.conf to say 'commit_delay=100' or 'synchronous_commit=off'. Let us know if that fixes the problem. Read up on the options before you change them.
Graeme Bell
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-06-02 07:47 Re: Postgres is using 100% CPU Graeme B. Bell <grb@skogoglandskap.no>
@ 2015-06-02 07:58 ` Graeme B. Bell <grb@skogoglandskap.no>
2015-06-02 08:20 ` Re: Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Graeme B. Bell @ 2015-06-02 07:58 UTC (permalink / raw)
To: pgsql-performance
> I believe yes / 0 are the default settings for synchronous commit and commit_delay. ** (Interestingly the manual pages do not specify.) **
Sorry, I've just spotted the settings in the text. The statement (marked **) is incorrect.
Defaults are yes/0. (http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html)
Graeme.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Postgres is using 100% CPU
2015-06-02 07:47 Re: Postgres is using 100% CPU Graeme B. Bell <grb@skogoglandskap.no>
2015-06-02 07:58 ` Re: Postgres is using 100% CPU Graeme B. Bell <grb@skogoglandskap.no>
@ 2015-06-02 08:20 ` Ashik S L <ashiksl178@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Ashik S L @ 2015-06-02 08:20 UTC (permalink / raw)
To: Graeme B. Bell <grb@skogoglandskap.no>; +Cc: pgsql-performance
Thanks everyone for your response. I will try these settings.
On Tue, Jun 2, 2015 at 1:28 PM, Graeme B. Bell <grb@skogoglandskap.no>
wrote:
> > I believe yes / 0 are the default settings for synchronous commit and
> commit_delay. ** (Interestingly the manual pages do not specify.) **
>
> Sorry, I've just spotted the settings in the text. The statement (marked
> **) is incorrect.
>
> Defaults are yes/0. (
> http://www.postgresql.org/docs/9.4/static/runtime-config-wal.html)
>
> Graeme.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2015-06-02 08:20 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-29 16:40 Postgres is using 100% CPU Ashik S L <ashiksl178@gmail.com>
2015-05-29 17:17 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-29 17:18 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-29 17:57 ` Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:10 ` Ashik S L <ashiksl178@gmail.com>
2015-05-29 18:30 ` Yves Dorfsman <yves@zioup.com>
2015-05-29 19:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-30 13:46 ` Ashik S L <ashiksl178@gmail.com>
2015-05-30 14:20 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2015-05-31 13:04 ` Jean-David Beyer <jeandavid8@verizon.net>
2015-05-31 14:23 ` Yves Dorfsman <yves@zioup.com>
2015-06-01 05:38 ` Ashik S L <ashiksl178@gmail.com>
2015-06-01 13:20 ` Merlin Moncure <mmoncure@gmail.com>
2015-06-01 19:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2015-06-02 07:47 Re: Postgres is using 100% CPU Graeme B. Bell <grb@skogoglandskap.no>
2015-06-02 07:58 ` Graeme B. Bell <grb@skogoglandskap.no>
2015-06-02 08:20 ` Ashik S L <ashiksl178@gmail.com>
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