Thread: Performance issues
PostGreSQL 7.2, Red Hat Linux 6.2:
I am having a very strange performance issue. I am using postgresql-ruby, but
I don't think this is a postgresql-ruby issue. If I issue 1,000 inserts in a
row as part of a transaction, I can get 900 inserts per second. If I mix the
inserts with a select inbetween on the same row, I can insert at only 7
inserts per second.
I.e.:
begin;
insert into migfiles (source,mtime) values ('/foo/bar',12341523);
.... [999 more]
end;
runs quite rapidly, while
begin;
select from migfiles where source = #{filename};
...(and if the file doesn't exist) ...
insert into migfiles (source, mtime) values ('#{filename}',#{mtime});
[ 999 more select/insert pairs ]
end;
takes a quite long time to run.
(Obviously there's some ruby logic to do the loop and all that good stuff,
but this should give you the gist of things).
'source' and 'mtime' are a text field and an integer field, respectively. The
only index is on 'source', necessary in order to make the 'select' work
properly once we get up past 200,000 records.
I did an strace on the back end:
recv(7, "Qinsert into MigFiles (migrate_t"..., 8192, 0) = 290
lseek(29, 0, SEEK_END) = 8192
lseek(29, 0, SEEK_END) = 8192
send(7, "Pblank\0CINSERT 455040 1\0Z", 25, 0) = 25
recv(7, "Qselect source from migfiles whe"..., 8192, 0) = 94
lseek(40, 0, SEEK_END) = 73097216
send(7, "Pblank\0T\0\1source\0\0\0\0\31\377\377\377\377\377\377"..., 36, 0) =
36recv(7, "Qinsert into MigFiles (migrate_t"..., 8192, 0) = 290
lseek(29, 0, SEEK_END) = 8192
lseek(29, 0, SEEK_END) = 8192
send(7, "Pblank\0CINSERT 455041 1\0Z", 25, 0) = 25
recv(7, "Qselect source from migfiles whe"..., 8192, 0) = 94
lseek(40, 0, SEEK_END) = 73097216
send(7, "Pblank\0T\0\1source\0\0\0\0\31\377\377\377\377\377\377"..., 36, 0) =
36recv(7, "Qinsert into MigFiles (migrate_t"..., 8192, 0) = 290
lseek(29, 0, SEEK_END) = 8192
lseek(29, 0, SEEK_END) = 8192
send(7, "Pblank\0CINSERT 455042 1\0Z", 25, 0) = 25
recv(7, "Qselect source from migfiles whe"..., 8192, 0) = 94
lseek(40, 0, SEEK_END) = 73097216
send(7, "Pblank\0T\0\1source\0\0\0\0\31\377\377\377\377\377\377"..., 36, 0) =
36
So there's nothing there unusual.
I thought perhaps that I was not properly batching these between a begin/end
pair for the inserts, but I modified my test program and even using the
inherent per-insert transaction locking it manages 219 files per second.
I'm stumped. There's no reason in the world why I should only be inserting at
7 records per second! (And yes, I'm running this against data sets that have
not yet been migrated into the database). Anybody have any other suggestions
for what I should do to try to speed things up? I'm about at the point where
I'm just going to batch up the 1000 filenames in an array, do a bulk insert
all at one time (i.e., no more mixed in selects, do a begin, a bunch of
inserts, then an end), and see if that speeds things up... does that sound
like something that might work?
I've done a 'vacuum analyze;' and an 'explain' on the select (yes it's using
the index, the 'select' works quite rapidly, if I run the program on a
directory that has already been entered into the database the program runs
quite rapidly).
BTW: Why does 'select count(*) from migfiles;' run so slowly compared to
MySQL? Is there a better way to do this in PostGres?
We're trying to get away from MySQL because of its limitations, but we can't
live with 7 inserts per second :-(. This one has me stumped.
Eric Lee Green GnuPG public key at http://badtux.org/eric/eric.gpg
mailto:eric@badtux.org Web: http://www.badtux.org/eric
Join the Conspiracy: http://evidence-eliminator-sucks.com
Eric Lee Green <eric@badtux.org> writes: > PostGreSQL 7.2, Red Hat Linux 6.2: > > I am having a very strange performance issue. I am using postgresql-ruby, but > I don't think this is a postgresql-ruby issue. If I issue 1,000 inserts in a > row as part of a transaction, I can get 900 inserts per second. If I mix the > inserts with a select inbetween on the same row, I can insert at only 7 > inserts per second. > > I.e.: [snippage] Hmm, my guess is that SELECT queries that return a null result set are what's slowing things down--they have to scan the whole table. Is there any reason you have to do this? You might try timing 1000 null-returning SELECTs against a populated table and see how long they take, just to see if my hypothesis is correct. The INSERT-without-SELECT goes fast because PG just appends to the table without having to scan it. IF you do this, or even better, use COPY rather than INSERT for bulk loading, it'll go fast. What usage patterns is this app going to have? If "record not there" is the common case, try putting a UNIQUE INDEX on 'source' and just catch INSERT errors when they happen. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
On Monday 18 March 2002 09:11 pm, Doug McNaught wrote: > Eric Lee Green <eric@badtux.org> writes: > > PostGreSQL 7.2, Red Hat Linux 6.2: > > > > I am having a very strange performance issue. I am using postgresql-ruby, > > but I don't think this is a postgresql-ruby issue. If I issue 1,000 > > inserts in a row as part of a transaction, I can get 900 inserts per > > second. If I mix the inserts with a select inbetween on the same row, I > > can insert at only 7 inserts per second. > > Hmm, my guess is that SELECT queries that return a null result set > are what's slowing things down--they have to scan the whole table. explain select * from migfiles where source = 'foo' and mtime = 5; says it's using the index. Seat of the pants verifies this: select * from migfiles where source = 'foo' and mtime = 5; returns immediately (there is no file named 'foo' in the database), just as select * from migfiles where source = 'bar' and mtime = 5; returns immediately (there is a file named 'bar' in the database). select * from migfiles where mtime = -1; takes about 5 seconds to tell me there's no record (200,000 records, slow IDE drive on a laptop), showing what scanning the whole database would cost (there is no index on mtime). Same thing with select count(*) from migfiles; it takes about 5 seconds to tell me how many records are in the database. > Is > there any reason you have to do this? Yeah, this is a two-phase commit protocol and if I created a unique index on (source,mtime) and then did the insert, I'd have to add an extra field for 'state' and then come back and do an update to "commit" the (non-sql) transaction. Right now I'm using the database record to signal the "commit", and recording the initial phase of the (non-sql) transaction elsewhere. In addition, if I recall right an error aborts a (sql) transaction, which I decidedly do NOT want to have happen, because there is an enormous performance gain from doing a bunchy of inserts as a single transaction rather than as seperate individual transactions. > You might try timing 1000 null-returning SELECTs against a populated > table and see how long they take, just to see if my hypothesis is > correct. Near instantaneously. > > The INSERT-without-SELECT goes fast because PG just appends to the > table without having to scan it. Not quite. There's the index to bother with. > IF you do this, or even better, use > COPY rather than INSERT for bulk loading, it'll go fast. I'm thinking of doing this. But this will mean that I must have a more complicated recovery mechanism. At the moment I have a single (non-SQL) transaction outstanding. If I go to bulk loading, I could have a thousand transactions outstanding. What currently consists of a single stat() call would turn into a complicated loop and transaction log walk. > What usage patterns is this app going to have? If "record not there" > is the common case, try putting a UNIQUE INDEX on 'source' and just > catch INSERT errors when they happen. The typical usage pattern is that on a daily run, 80% of the entries will match the 'select' statement and be skipped, the other 20% will match no record in the database and will have a transaction run against them and then be inserted into the database as the final 'commit' phase of the transaction. Typically they will be bunched together -- a thousand files that match the 'select', a hundred files that don't and must be processed, etc. My boss says we can't switch from MySQL unless I can make the beast insert at least 25 records per second into the database as part of the transactional process (we get more than that from MySQL, though MySQL on Linux has some decided scaling problems as you approach 10,000,000 records that makes me want to switch). I will create some more test programs tomorrow and post them if I can duplicate the problem in a way that won't cause Legal to have a heart attack :-}. Geez, I wish it was possible to make a living in the Open Source world nowdays, this business of NDA's and such sucks. -- Eric Lee Green GnuPG public key at http://badtux.org/eric/eric.gpg mailto:eric@badtux.org Web: http://www.badtux.org/eric Join the Conspiracy: http://evidence-eliminator-sucks.com
Eric Lee Green <eric@badtux.org> writes: > > You might try timing 1000 null-returning SELECTs against a populated > > table and see how long they take, just to see if my hypothesis is > > correct. > > Near instantaneously. Hmmm. I think this is a situation where you're going to need to profile the backend to see what's taking up all the time. It may be a bad case of lock contention (though I can't see why), or something really dumb may be going on. If you can provide a test case, I'm willing to bet the PG gurus will be interested... So just te be clear, what you're seeing is that batched INSERTs go fast, and batched SELECTs go fast, but when you interleave them it's really slow? > I will create some more test programs tomorrow and post them if I can > duplicate the problem in a way that won't cause Legal to have a heart attack > :-}. Geez, I wish it was possible to make a living in the Open Source world > nowdays, this business of NDA's and such sucks. I hear that. ;) -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
Doug McNaught <doug@wireboard.com> writes:
> So just te be clear, what you're seeing is that batched INSERTs go
> fast, and batched SELECTs go fast, but when you interleave them it's
> really slow?
> If you can provide a test case, I'm
> willing to bet the PG gurus will be interested...
Yup, I'd be interested in a test case. This behavior makes no sense
to me either :-(
regards, tom lane
On Monday 18 March 2002 11:21 pm, Tom Lane wrote: > Doug McNaught <doug@wireboard.com> writes: > > So just te be clear, what you're seeing is that batched INSERTs go > > fast, and batched SELECTs go fast, but when you interleave them it's > > really slow? > > If you can provide a test case, I'm > > willing to bet the PG gurus will be interested... > > Yup, I'd be interested in a test case. This behavior makes no sense > to me either :-( GRRR! I wrote a quicky test program that bypasses the transaction processing and just logs or skips the file. I can skip already-inserted files at approximately 1,000 records per second, and insert new files into the database at approximately 300 records per second. It's obvious that I'm not having a PostgreSQL problem. I'm beginning to think I have an interaction with a $^%@#$% piece of crap commercial device driver installed on the system. I thank you for your kind responses anyhow, even though it didn't turn out to be a PostGreSQL problem. -- Eric