Re: Postgres is using 100% CPU

On Mon, Jun 1, 2015 at 12:38 AM, Ashik S L <ashiksl178(at)gmail(dot)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