Thread: insert/update tps slow with indices on table > 1M rows

insert/update tps slow with indices on table > 1M rows

From
andrew klassen
Date:
Running postgres 8.2.5
 
I have a table that has 5 indices, no foreign keys or any
dependency on any other table. If delete the database and
start entering entries, everything works very well until I get
to some point (let's say 1M rows). Basically, I have a somewhat
constant rate of inserts/updates that go into a work queue and then
get passed to postgres. The work queue starts filling up as the
responsiveness slows down. For example at 1.5M
rows it takes >2 seconds for 300 inserts issued in one transaction.
 
Prior to this point I had added regular VACUUM ANALYZE on
the table and it did help.  I increased maintenance work memory to
128M. I also set the fillfactor on the table indices to 50% (not sure
if that made any difference have to study results more closely). 
 
In an effort to figure out the bottleneck, I DROPed 4 of the indices
on the table and the tps increased to over 1000. I don't really know
which index removal gave the best performance improvement. I
dropped 2 32-bit indices and 2 text indices which all using btree.
 
The cpu load is not that high, i.e. plenty of idle cpu. I am running an older
version of freebsd and the iostat output is not very detailed.
During this time, the number is low < 10Mbs. The system has an
LSI Logic MegaRAID controller with 2 disks.
 
Any ideas on how to find the bottleneck/decrease overhead of index usage.
 
Thanks.
 
 
 
 
 
 

Re: insert/update tps slow with indices on table > 1M rows

From
PFC
Date:
On Wed, 04 Jun 2008 00:36:09 +0200, andrew klassen <aptklassen@yahoo.com>
wrote:

> Running postgres 8.2.5
>  
> I have a table that has 5 indices, no foreign keys or any
> dependency on any other table. If delete the database and
> start entering entries, everything works very well until I get
> to some point (let's say 1M rows). Basically, I have a somewhat
> constant rate of inserts/updates that go into a work queue and then
> get passed to postgres. The work queue starts filling up as the
> responsiveness slows down. For example at 1.5M
> rows it takes >2 seconds for 300 inserts issued in one transaction.
>  
> Prior to this point I had added regular VACUUM ANALYZE on
> the table and it did help.  I increased maintenance work memory to
> 128M. I also set the fillfactor on the table indices to 50% (not sure
> if that made any difference have to study results more closely). 
>  
> In an effort to figure out the bottleneck, I DROPed 4 of the indices
> on the table and the tps increased to over 1000. I don't really know
> which index removal gave the best performance improvement. I
> dropped 2 32-bit indices and 2 text indices which all using btree.
>  
> The cpu load is not that high, i.e. plenty of idle cpu. I am running an
> older
> version of freebsd and the iostat output is not very detailed.
> During this time, the number is low < 10Mbs. The system has an
> LSI Logic MegaRAID controller with 2 disks.
>  
> Any ideas on how to find the bottleneck/decrease overhead of index usage.
>  
> Thanks.

    If you are filling an empty table it is generally faster to create the
indexes after the data import.
    Of course if this is a live table or you need the indexes during the
import, this is not an option.
    I find it generally faster to lightly preprocess the data and generate
text files that I then import using COPY, then doing the rest of the
processing in SQL.

    How much RAM in the box ? size of the data & indexes ?

Re: insert/update tps slow with indices on table > 1M rows

From
andrew klassen
Date:
I am not currently using copy, but  I am using prepared statements  
for table insert/updates so the overhead for the actual data transfer
should be pretty good. I am sending at most  300 inserts/updates
per transaction, but that is just an arbitrary value. When the queue
grows, I could easily send more per transaction. I  did experiment
a little, but it did not seem to help significantly at the time.
 
The system has 4G total memory. Shared memory is locked by the OS,
i.e. not paged so I am only using shared_buffers=28MB.
 
The maximum data per row is 324 bytes assuming maximum expected length of two
text fields. There are 5 total indices: 1 8-byte, 2 4-byte and 2 text fields.
As mentioned all indices are btree.
 

 
----- Original Message ----
From: PFC <lists@peufeu.com>
To: andrew klassen <aptklassen@yahoo.com>; pgsql-performance@postgresql.org
Sent: Tuesday, June 3, 2008 7:15:10 PM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Wed, 04 Jun 2008 00:36:09 +0200, andrew klassen <aptklassen@yahoo.com
wrote:

> Running postgres 8.2.5
>  
> I have a table that has 5 indices, no foreign keys or any
> dependency on any other table. If delete the database and
> start entering entries, everything works very well until I get
> to some point (let's say 1M rows). Basically, I have a somewhat
> constant rate of inserts/updates that go into a work queue and then
> get passed to postgres. The work queue starts filling up as the
> responsiveness slows down. For example at 1.5M
> rows it takes >2 seconds for 300 inserts issued in one transaction.
>  
> Prior to this point I had added regular VACUUM ANALYZE on
> the table and it did help.  I increased maintenance work memory to
> 128M. I also set the fillfactor on the table indices to 50% (not sure
> if that made any difference have to study results more closely). 
>  
> In an effort to figure out the bottleneck, I DROPed 4 of the indices
> on the table and the tps increased to over 1000. I don't really know
> which index removal gave the best performance improvement. I
> dropped 2 32-bit indices and 2 text indices which all using btree.
>  
> The cpu load is not that high, i.e. plenty of idle cpu. I am running an 
> older
> version of freebsd and the iostat output is not very detailed.
> During this time, the number is low < 10Mbs. The system has an
> LSI Logic MegaRAID controller with 2 disks.
>  
> Any ideas on how to find the bottleneck/decrease overhead of index usage.
>  
> Thanks.

    If you are filling an empty table it is generally faster to create the 
indexes after the data import.
    Of course if this is a live table or you need the indexes during the 
import, this is not an option.
    I find it generally faster to lightly preprocess the data and generate 
text files that I then import using COPY, then doing the rest of the 
processing in SQL.

    How much RAM in the box ? size of the data & indexes ?

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: insert/update tps slow with indices on table > 1M rows

From
"Scott Marlowe"
Date:
On Tue, Jun 3, 2008 at 4:36 PM, andrew klassen <aptklassen@yahoo.com> wrote:

> The cpu load is not that high, i.e. plenty of idle cpu. I am running an
> older
> version of freebsd and the iostat output is not very detailed.
> During this time, the number is low < 10Mbs. The system has an
> LSI Logic MegaRAID controller with 2 disks.
>
> Any ideas on how to find the bottleneck/decrease overhead of index usage.

Older versions of BSD can be pretty pokey compared to the 6.x and 7.x
branches.  I seriously consider upgrading to 7 if possible.

The cost of maintaining indexes is always an issue.  There are a few
things you can do to help out.

Partitioning and having fewer indexes are what I'd recommend.

Re: insert/update tps slow with indices on table > 1M rows

From
Matthew Wakeling
Date:
On Tue, 3 Jun 2008, andrew klassen wrote:
> Basically, I have a somewhat constant rate of inserts/updates that go
> into a work queue and then get passed to postgres.

> The cpu load is not that high, i.e. plenty of idle cpu. I am running an older
> version of freebsd and the iostat output is not very detailed.

If you're running a "work queue" architecture, that probably means you
only have one thread doing all the updates/inserts? It might be worth
going multi-threaded, and issuing inserts and updates through more than
one connection. Postgres is designed pretty well to scale performance by
the number of simultaneous connections.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                 -- Kyle Hearn

Re: insert/update tps slow with indices on table > 1M rows

From
andrew klassen
Date:
 
I am using multiple threads, but only one worker thread for insert/updated to this table.
I don't mind trying to add multiple threads for this table, but my guess is it would not
help because basically the overall tps rate is decreasing so dramatically. Since
the cpu time consumed by the corresponding postgres server process for my thread is
small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
 
Do you agree or is there some flaw in my reasoning?

 
----- Original Message ----
From: Matthew Wakeling <matthew@flymine.org>
To: andrew klassen <aptklassen@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 5:31:22 AM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Tue, 3 Jun 2008, andrew klassen wrote:
> Basically, I have a somewhat constant rate of inserts/updates that go
> into a work queue and then get passed to postgres.

> The cpu load is not that high, i.e. plenty of idle cpu. I am running an older
> version of freebsd and the iostat output is not very detailed.

If you're running a "work queue" architecture, that probably means you
only have one thread doing all the updates/inserts? It might be worth
going multi-threaded, and issuing inserts and updates through more than
one connection. Postgres is designed pretty well to scale performance by
the number of simultaneous connections.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                -- Kyle Hearn
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: insert/update tps slow with indices on table > 1M rows

From
Matthew Wakeling
Date:
On Wed, 4 Jun 2008, andrew klassen wrote:
> I am using multiple threads, but only one worker thread for insert/updated to this table.
> I don't mind trying to add multiple threads for this table, but my guess is it would not
> help because basically the overall tps rate is decreasing so dramatically. Since
> the cpu time consumed by the corresponding postgres server process for my thread is
> small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
> Do you agree or is there some flaw in my reasoning?

There is indeed a flaw in your reasoning - there may be very little CPU
time consumed, but that just indicates that the discs are busy. Getting
Postgres to do multiple things at once will cause a more efficient use of
the disc subsystem, resulting in greater overall throughput. This is
especially the case if you have multiple discs in your box.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                 -- Kyle Hearn

Re: insert/update tps slow with indices on table > 1M rows

From
andrew klassen
Date:

I agree that the discs are probably very busy. I do have 2 disks but they are
for redundancy. Would it help to put the data, indexes and xlog on separate
disk partitions?
 
I'll try adding more threads to update the table as you suggest.
 

 
----- Original Message ----
From: Matthew Wakeling <matthew@flymine.org>
To: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 10:10:38 AM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

On Wed, 4 Jun 2008, andrew klassen wrote:
> I am using multiple threads, but only one worker thread for insert/updated to this table.
> I don't mind trying to add multiple threads for this table, but my guess is it would not
> help because basically the overall tps rate is decreasing so dramatically. Since
> the cpu time consumed by the corresponding postgres server process for my thread is
> small it does not seem to be the bottleneck. There has to be a bottleneck somewhere else.
> Do you agree or is there some flaw in my reasoning?

There is indeed a flaw in your reasoning - there may be very little CPU
time consumed, but that just indicates that the discs are busy. Getting
Postgres to do multiple things at once will cause a more efficient use of
the disc subsystem, resulting in greater overall throughput. This is
especially the case if you have multiple discs in your box.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are.                -- Kyle Hearn
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: insert/update tps slow with indices on table > 1M rows

From
James Mansion
Date:
Matthew Wakeling wrote:
> If you're running a "work queue" architecture, that probably means you
> only have one thread doing all the updates/inserts? It might be worth
> going multi-threaded, and issuing inserts and updates through more
> than one connection. Postgres is designed pretty well to scale
> performance by the number of simultaneous connections.
That would explain a disappointing upper limit on insert rate, but not
any sort of cliff for the rate.  Nor, really, any material slowdown, if
the single thread implies that we're stuck on round trip latency as a
material limiting factor.

James


Re: insert/update tps slow with indices on table > 1M rows

From
James Mansion
Date:
andrew klassen wrote:
> I'll try adding more threads to update the table as you suggest.
You could try materially increasing the update batch size too.  As an
exercise you could
see what the performance of COPY is by backing out the data and
reloading it from
a suitable file.


Re: insert/update tps slow with indices on table > 1M rows

From
andrew klassen
Date:

 

I am using the c-library interface and for these particular transactions

I preload PREPARE statements. Then as I get requests, I issue a BEGIN,

followed by at most 300 EXECUTES and then a COMMIT. That is the

general scenario. What value beyond 300 should I try?

 

Also, how might COPY (which involves file I/O) improve the

above scenario?

 

Thanks.

----- Original Message ----
From: James Mansion <james@mansionfamily.plus.com>
To: andrew klassen <aptklassen@yahoo.com>
Cc: pgsql-performance@postgresql.org
Sent: Wednesday, June 4, 2008 3:20:26 PM
Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M rows

andrew klassen wrote:
> I'll try adding more threads to update the table as you suggest.
You could try materially increasing the update batch size too.  As an
exercise you could
see what the performance of COPY is by backing out the data and
reloading it from
a suitable file.


Re: insert/update tps slow with indices on table > 1M rows

From
PFC
Date:
> I am using the c-library interface and for these particular transactions
> I preload PREPARE statements. Then as I get requests, I issue a BEGIN,
> followed by at most 300 EXECUTES and then a COMMIT. That is the
> general scenario. What value beyond 300 should I try?
> Thanks.

    Do you have PREPARE statements whose performance might change as the
table grows ?

    I mean, some selects, etc... in that case if you start with an empty
table, after inserting say 100K rows you might want to just disconnect,
reconnect and analyze to trigger replanning of those statements.

> Also, how might COPY (which involves file I/O) improve the
> above scenario?

    It won't but if you see that COPY is very much faster than your INSERT
based process it will give you a useful piece of information.

    I understand your problem is :

- Create table with indexes
- Insert batches of rows
- After a while it gets slow

    Try :

- Create table with indexes
- COPY huge batch of rows
- Compare time with above

    Since COPY also updates the indexes just like your inserts do it will
tell you if it's the indexes which slow you down or something else.

    Also for insert heavy loads it's a good idea to put the xlog on a
separate disk (to double your disk bandwidth) unless you have a monster
disk setup.

    During your INSERTs, do you also make some SELECTs ? Do you have triggers
on the table ? Foreign keys ? Anything ?
    How much RAM you have ? And can you measure the size of the table+indexes
when it gets slow ?


>
>
> ----- Original Message ----
> From: James Mansion <james@mansionfamily.plus.com>
> To: andrew klassen <aptklassen@yahoo.com>
> Cc: pgsql-performance@postgresql.org
> Sent: Wednesday, June 4, 2008 3:20:26 PM
> Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M
> rows
>
> andrew klassen wrote:
>> I'll try adding more threads to update the table as you suggest.
> You could try materially increasing the update batch size too.  As an
> exercise you could
> see what the performance of COPY is by backing out the data and
> reloading it from
> a suitable file.
>
>
>



Re: insert/update tps slow with indices on table > 1M rows

From
Tom Lane
Date:
andrew klassen <aptklassen@yahoo.com> writes:
> I am using the c-library interface and for these particular transactions
> I preload PREPARE statements. Then as I get requests, I issue a BEGIN,
> followed by at most 300 EXECUTES and then a COMMIT. That is the
> general scenario. What value beyond 300 should I try?

Well, you could try numbers in the low thousands, but you'll probably
get only incremental improvement.

> Also, how might COPY (which involves file I/O) improve the
> above scenario?

COPY needn't involve file I/O.  If you are using libpq you can push
anything you want into PQputCopyData.  This would involve formatting
the data according to COPY's escaping rules, which are rather different
from straight SQL, but I doubt it'd be a huge amount of code.  Seems
worth trying.

            regards, tom lane

Re: insert/update tps slow with indices on table > 1M rows

From
"Heikki Linnakangas"
Date:
andrew klassen wrote:
> I am using the c-library interface and for these particular transactions
> I preload PREPARE statements. Then as I get requests, I issue a BEGIN,
> followed by at most 300 EXECUTES and then a COMMIT. That is the
> general scenario. What value beyond 300 should I try?

Make sure you use the asynchronous PQsendQuery, instead of plain PQexec.
Otherwise you'll be doing a round-trip for each EXECUTE anyway
regardless of the batch size. Of course, if the bottleneck is somewhere
else, it won't make a difference..

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com