Re: Postgres is using 100% CPU

On Mon, Jun 1, 2015 at 7:20 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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:performance(at)postgresql(dot)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.