pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert performance
32+ messages / 13 participants
[nested] [flat]
* Insert performance
@ 2003-08-16 08:40 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-16 18:40 ` Re: Insert performance Josh Berkus <josh@agliodbs.com>
2003-08-18 06:21 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 2 replies; 32+ messages in thread
From: Shridhar Daithankar @ 2003-08-16 08:40 UTC (permalink / raw)
To: pgsql-performance
Hi all,
Couple of days ago, one of my colleague, Rahul Iyer posted a query regarding
insert performance of 5M rows. A common suggestion was to use copy.
Unfortunately he can not use copy due to some constraints. I was helping him to
get maximum out of it. We were playing with a data set of 500K rows on SunOS5.6
and postgresql 7.3.3
Best we could get was 500K records in 355 sec. That's roughly 1400 inserts per
sec. This was with default settings and around 10K inserts per transaction.
Postgresql was hogging all the CPU during load.
Troubled by this, I set up a similar database at home. That is a Athlon
XP2000+/512MB machine with a 40GB seagate disk. It is running slackware 9.0
with 2.4.20(IIRC).
I have attached the results of my experiements and the code I used to
benchmark. It was a simple table with an integer and a varchar(30) field.
I was really amazed to see the numbers. First of all, it beat the sunOS machine
left and right. Bruce posted some numbers of 9K inserts/sec. Here we see the
same.
Secondly I also played with filesystems. Ext3 does not seem to be performing as
good. Reiser and ext2 did fine. Unfortunately the kernel didn't support XFS/JFS
so could not test them.
I have also attached the raw benchmark data in kspread format, for the curious.
Didn't exported to anything else because kspread had troubles with exporting
formula values.
I also noticed that reiser takes hell lot more CPU than ext2 and ext3. It
nearly peaks out all CPU capacity. Not so with ext2.
Comments? One thing I can't help to notice is sunOs is not on same scale. The
sunOS machine is a 1GB RAM machine. It has oracle and mysql running on it and
have 300MB swap in use but I am sure it has SCSI disk and in all respect I
would rather expect a RISC machine to perform better than an athlon XP machine,
at least for an IO.
If you want me to post details of sparc machine, please let me know how do I
find it. I have never worked with sparcs earlier and have no intention of doing
this again..:-)
Bye
Shridhar
--
Fourth Law of Applied Terror: The night before the English History mid-term,
your Biology instructor will assign 200 pages on planaria.Corollary: Every
instructor assumes that you have nothing else to do except study for that
instructor's course.
Attachments:
insert.c (application/octet-stream, 1.4K) download
Results.zip (application/zip, 1.0K) download
pgbenchmark.zip (application/zip, 20.3K) download
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-16 18:40 ` Josh Berkus <josh@agliodbs.com>
2003-08-16 22:22 ` Re: Insert performance Christopher Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 32+ messages in thread
From: Josh Berkus @ 2003-08-16 18:40 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; pgsql-performance
Shridhar,
> Unfortunately he can not use copy due to some constraints.
Why not use COPY to load the table, and then apply the constraints by query
afterwords? It might not be faster, but then again it might.
> I was really amazed to see the numbers. First of all, it beat the sunOS
> machine left and right. Bruce posted some numbers of 9K inserts/sec. Here
> we see the same.
<snip>
> Comments? One thing I can't help to notice is sunOs is not on same scale.
> The sunOS machine is a 1GB RAM machine. It has oracle and mysql running on
> it and have 300MB swap in use but I am sure it has SCSI disk and in all
> respect I would rather expect a RISC machine to perform better than an
> athlon XP machine, at least for an IO.
It's been reported on this list several times that Solaris is the *worst* of
the *nixes for PostgreSQL performance. No analysis has been posted as to
why; my own thoughts are:
- Solaris' multi-threaded architecture which imposes a hefty per-process
overhead, about triple that of Linux, slowing new connections and large
multi-user activity;
- Poor filesystem management; Sun simply hasn't kept up with IBM, Reiser, Red
Hat and BSD in developing filesystems.
... but that's based on inadequate experimentation, just a few tests on
Bonnie++ on a Netra running Solaris 8.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-16 18:40 ` Re: Insert performance Josh Berkus <josh@agliodbs.com>
@ 2003-08-16 22:22 ` Christopher Browne <cbbrowne@acm.org>
2003-08-18 16:57 ` Re: Insert performance Josh Berkus <josh@agliodbs.com>
2003-08-19 06:28 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 2 replies; 32+ messages in thread
From: Christopher Browne @ 2003-08-16 22:22 UTC (permalink / raw)
To: pgsql-performance
Martha Stewart called it a Good Thing whenjosh@agliodbs.com (Josh Berkus)wrote:
> Shridhar,
>> Unfortunately he can not use copy due to some constraints.
> Why not use COPY to load the table, and then apply the constraints
> by query afterwords? It might not be faster, but then again it
> might.
If you can transform the information into COPYable form, that's
certainly a good thing. Whether it will be workable or not is another
question.
> ... but that's based on inadequate experimentation, just a few tests
> on Bonnie++ on a Netra running Solaris 8.
As far as the filesystem issues are concerned, you're probably using
an "old, standard" version of UFS. The "high performance" option on
Solaris involves using third-party Veritas software.
The persistence of this is somewhat surprising; I'd somewhat have
expected Sun to have bought out Veritas or some such thing, as it's a
pretty vital technology that _isn't_ totally under their control.
Actually, an entertaining option would be for them to buy out SGI, as
that would get them control of XFS and a number of other interesting
technologies.
My expectations of a Netra wouldn't be terribly high, either; they
seem to exist as a product so that people that need a cheap Sun box
have an option. They are mostly running IDE disk, and the latest
IA-32 hardware is likely to have newer faster interface options.
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://cbbrowne.com/info/advocacy.html
"Windows NT was designed to be administered by an idiot and usually
is..." -- Chris Adams" <adamsc@gigante.ml.org>
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-16 18:40 ` Re: Insert performance Josh Berkus <josh@agliodbs.com>
2003-08-16 22:22 ` Re: Insert performance Christopher Browne <cbbrowne@acm.org>
@ 2003-08-18 16:57 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 32+ messages in thread
From: Josh Berkus @ 2003-08-18 16:57 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
Chris,
> My expectations of a Netra wouldn't be terribly high, either; they
> seem to exist as a product so that people that need a cheap Sun box
> have an option. They are mostly running IDE disk, and the latest
> IA-32 hardware is likely to have newer faster interface options.
Neither are ours ... we bought an array of 1U Netras as a dot-com closeout.
At $330 per machine including OS, they were a darned good deal, and with 4 of
them the redundancy makes up for the lack of individual server performance.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-16 18:40 ` Re: Insert performance Josh Berkus <josh@agliodbs.com>
2003-08-16 22:22 ` Re: Insert performance Christopher Browne <cbbrowne@acm.org>
@ 2003-08-19 06:28 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 32+ messages in thread
From: Shridhar Daithankar @ 2003-08-19 06:28 UTC (permalink / raw)
To: pgsql-performance
On 18 Aug 2003 at 9:57, Josh Berkus wrote:
> Chris,
>
> > My expectations of a Netra wouldn't be terribly high, either; they
> > seem to exist as a product so that people that need a cheap Sun box
> > have an option. They are mostly running IDE disk, and the latest
> > IA-32 hardware is likely to have newer faster interface options.
>
> Neither are ours ... we bought an array of 1U Netras as a dot-com closeout.
> At $330 per machine including OS, they were a darned good deal, and with 4 of
> them the redundancy makes up for the lack of individual server performance.
I am sure they would run much better with linux on them rather than solaris..
Bye
Shridhar
--
Shannon's Observation: Nothing is so frustrating as a bad situation that is
beginning to improve.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-18 06:21 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 15:52 ` Re: Insert performance Hannu Krosing <hannu@tm.ee>
2003-08-18 16:02 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 2 replies; 32+ messages in thread
From: Shridhar Daithankar @ 2003-08-18 06:21 UTC (permalink / raw)
To: pgsql-performance
On 16 Aug 2003 at 11:40, Josh Berkus wrote:
> Shridhar,
>
> > Unfortunately he can not use copy due to some constraints.
>
> Why not use COPY to load the table, and then apply the constraints by query
> afterwords? It might not be faster, but then again it might.
Lol.. The constraints I mentioned weren't database constraints.. It need to
interface with another database module.. The data import can not be text..
that's a project requirement..
I should have been clearer in first place..
Bye
Shridhar
--
O'Toole's commentary on Murphy's Law: Murphy was an optimist.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 06:21 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-18 15:52 ` Hannu Krosing <hannu@tm.ee>
1 sibling, 0 replies; 32+ messages in thread
From: Hannu Krosing @ 2003-08-18 15:52 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance
Shridhar Daithankar kirjutas E, 18.08.2003 kell 09:21:
> On 16 Aug 2003 at 11:40, Josh Berkus wrote:
>
> > Shridhar,
> >
> > > Unfortunately he can not use copy due to some constraints.
> >
> > Why not use COPY to load the table, and then apply the constraints by query
> > afterwords? It might not be faster, but then again it might.
>
> Lol.. The constraints I mentioned weren't database constraints..
Can't you still apply them later ;)
My own experimentation also got numbers in 9k/sec range (on a quad
1.3GHz Xeons, 2GM mem, 50MB/sec raid) when doing 10-20 parallel runs of
~1000 inserts/transaction.
Performance dropped to ~300/sec (at about 60M rows) when I added an
index (primary key) - as I did random inserts, the hit rates for index
pages were probably low.
--------------
Hannu
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 06:21 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-18 16:02 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 22:16 ` Re: Insert performance Hannu Krosing <hannu@tm.ee>
2003-08-19 06:33 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 2 replies; 32+ messages in thread
From: Shridhar Daithankar @ 2003-08-18 16:02 UTC (permalink / raw)
To: pgsql-performance
On 18 Aug 2003 at 18:52, Hannu Krosing wrote:
> My own experimentation also got numbers in 9k/sec range (on a quad
> 1.3GHz Xeons, 2GM mem, 50MB/sec raid) when doing 10-20 parallel runs of
> ~1000 inserts/transaction.
>
> Performance dropped to ~300/sec (at about 60M rows) when I added an
> index (primary key) - as I did random inserts, the hit rates for index
> pages were probably low.
I was loading a geographic data couple of months back.. It was 3GB data when
loaded in postgresql.
I tried loading data first and creating index later. It ran out of available
9GB space. So I created index on an empty table and started loading it. It was
slow but at least finished after 3 hours... Co-incidentally oracle had same
problems as well. So creating index beforehand remains only option at times, it
seems. Tom remarked that it shouldn't have made difference but apparently it
does..
You mentioned parallel runs and still getting 9K/sec. Was that overall 9K or
per connection? If it is former, probably WAL is hit too hard. You could do
some additional testing by having WALit's own disk.
I was plannning to do the multiple writers test. But since objective was to
find out why postgresql was so slow and it tunred out to be slowaris in first
place, didn't have any enthu. left.
I recommended my colleague to move to linux. But apparently this product is a
part of suit which runs on some HUGE solaris machines. So if it has to run
postgresql, it has to run sunos. I hope they are faster with SCSI..
Bye
Shridhar
--
On my planet, to rest is to rest -- to cease using energy. To me, itis quite
illogical to run up and down on green grass, using energy,instead of saving it.
-- Spock, "Shore Leave", stardate 3025.2
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 06:21 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 16:02 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-18 22:16 ` Hannu Krosing <hannu@tm.ee>
1 sibling, 0 replies; 32+ messages in thread
From: Hannu Krosing @ 2003-08-18 22:16 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance
Shridhar Daithankar kirjutas E, 18.08.2003 kell 19:02:
> On 18 Aug 2003 at 18:52, Hannu Krosing wrote:
> > My own experimentation also got numbers in 9k/sec range (on a quad
> > 1.3GHz Xeons, 2GM mem, 50MB/sec raid) when doing 10-20 parallel runs of
> > ~1000 inserts/transaction.
> >
> > Performance dropped to ~300/sec (at about 60M rows) when I added an
> > index (primary key) - as I did random inserts, the hit rates for index
> > pages were probably low.
>
> I was loading a geographic data couple of months back.. It was 3GB data when
> loaded in postgresql.
With or without indexes ?
> I tried loading data first and creating index later. It ran out of available
> 9GB space. So I created index on an empty table and started loading it. It was
> slow but at least finished after 3 hours... Co-incidentally oracle had same
> problems as well. So creating index beforehand remains only option at times, it
> seems. Tom remarked that it shouldn't have made difference but apparently it
> does..
Tom just fixed some memory leaks on array indexing the other day. Could
there be something like that on geographic types ?
> You mentioned parallel runs and still getting 9K/sec. Was that overall 9K or
> per connection?
Overall. But notice that my setup was (a little) slower per processor.
> If it is former, probably WAL is hit too hard. You could do
> some additional testing by having WALit's own disk.
I guess that todays IDE disks are about the same speed (~50MB/sec) as my
test RAID was.
I run multiple parallel runs to have a chance to use all 4 processors
(but IIRC it was heavyly IO-bound) as well as to better use writing time
on WAL platters (not to wait for full rotation on each platter)
--------------
Hannu
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 06:21 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 16:02 ` Re: Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-08-19 06:33 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1 sibling, 0 replies; 32+ messages in thread
From: Shridhar Daithankar @ 2003-08-19 06:33 UTC (permalink / raw)
To: pgsql-performance
On 19 Aug 2003 at 1:16, Hannu Krosing wrote:
> Shridhar Daithankar kirjutas E, 18.08.2003 kell 19:02:
> > I was loading a geographic data couple of months back.. It was 3GB data when
> > loaded in postgresql.
>
> With or without indexes ?
Without index. Index was another 3 GB with 50% utilisation. It was 81M rows
with 3 floats each..
>
> > I tried loading data first and creating index later. It ran out of available
> > 9GB space. So I created index on an empty table and started loading it. It was
> > slow but at least finished after 3 hours... Co-incidentally oracle had same
> > problems as well. So creating index beforehand remains only option at times, it
> > seems. Tom remarked that it shouldn't have made difference but apparently it
> > does..
>
> Tom just fixed some memory leaks on array indexing the other day. Could
> there be something like that on geographic types ?
Dunno.. This was 7.3.2 or earlier.. Later the project abandoned all types of
databases and went to in memory structures since flat data was of the order of
200MB. Now they aer returning to databases as flat data is approaching excess
of 3 GB..
God knows what will they do next. It's an ideal example what schedule pressure
can do to architecture design of a software..
Bye
Shridhar
--
Air Force Inertia Axiom: Consistency is always easier to defend than correctness.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Insert performance
@ 2007-02-27 17:10 hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 32+ messages in thread
From: hatman @ 2007-02-27 17:10 UTC (permalink / raw)
To: pgsql-performance
Dear all,
After many tests and doc reading, i finally try to get help from
you...
Here is my problem. With some heavy insert into a simple BD (one
table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
testing it using a simple C software which use libpq and which use:
- Insert prepared statement (to avoid too many request parsing on the
server)
- transaction of 100000 inserts
My server which has the following config:
- 3G RAM
- Pentium D - 64 bits, 3Ghz
- database data on hardware raid 0 disks
- x_log (WAL logs) on an other single hard drive
The server only use 30% of the CPU, 10% of disk access and not much
RAM... So i'm wondering where could be the bottle neck and why i can't
get better performance ?
I really need to use inserts and i can't change it to use COPY...
Any advice is welcome. Sorry in advance for my bad understanding of
database !
Thanks in advance.
Regards,
Joël.W
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
@ 2007-03-06 07:34 ` Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Huxton @ 2007-03-06 07:34 UTC (permalink / raw)
To: hatman <joel.winteregg@gmail.com>; +Cc: pgsql-performance
hatman wrote:
> Dear all,
>
> After many tests and doc reading, i finally try to get help from
> you...
>
> Here is my problem. With some heavy insert into a simple BD (one
> table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
> testing it using a simple C software which use libpq and which use:
> - Insert prepared statement (to avoid too many request parsing on the
> server)
> - transaction of 100000 inserts
Are each of the INSERTs in their own transaction?
If so, you'll be limited by the speed of the disk the WAL is running on.
That means you have two main options:
1. Have multiple connections inserting simultaneously.
2. Batch your inserts together, from 10 to 10,000 per transaction.
Are either of those possible?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 07:53 ` joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 32+ messages in thread
From: joël Winteregg @ 2007-03-06 07:53 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Hi Richard,
> >
> > Here is my problem. With some heavy insert into a simple BD (one
> > table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
> > testing it using a simple C software which use libpq and which use:
> > - Insert prepared statement (to avoid too many request parsing on the
> > server)
> > - transaction of 100000 inserts
>
> Are each of the INSERTs in their own transaction?
>
No, as said above transactions are made of 100000 inserts...
> If so, you'll be limited by the speed of the disk the WAL is running on.
>
> That means you have two main options:
> 1. Have multiple connections inserting simultaneously.
Yes, you're right. That what i have been testing and what provide the
best performance ! I saw that postgresql frontend was using a lot of CPU
and not both of them (i'm using a pentium D, dual core). To the opposit,
the postmaster process use not much resources. Using several client,
both CPU are used and i saw an increase of performance (about 18000
inserts/sec).
So i think my bottle neck is more the CPU speed than the disk speed,
what do you think ?
I use 2 disks (raid 0) for the data and a single disk for pg_xlog.
> 2. Batch your inserts together, from 10 to 10,000 per transaction.
>
Yes, that's what i'm doing.
Thanks a lot for the advices !
regards,
Joël
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
@ 2007-03-06 08:08 ` Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Huxton @ 2007-03-06 08:08 UTC (permalink / raw)
To: joël Winteregg <joel.winteregg@gmail.com>; +Cc: pgsql-performance
joël Winteregg wrote:
> Hi Richard,
>
>>> Here is my problem. With some heavy insert into a simple BD (one
>>> table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
>>> testing it using a simple C software which use libpq and which use:
>>> - Insert prepared statement (to avoid too many request parsing on the
>>> server)
>>> - transaction of 100000 inserts
>> Are each of the INSERTs in their own transaction?
>>
>
> No, as said above transactions are made of 100000 inserts...
Hmm - I read that as just meaning "inserted 100000 rows". You might find
that smaller batches provide peak performance.
>> If so, you'll be limited by the speed of the disk the WAL is running on.
>>
>> That means you have two main options:
>> 1. Have multiple connections inserting simultaneously.
>
> Yes, you're right. That what i have been testing and what provide the
> best performance ! I saw that postgresql frontend was using a lot of CPU
> and not both of them (i'm using a pentium D, dual core). To the opposit,
> the postmaster process use not much resources. Using several client,
> both CPU are used and i saw an increase of performance (about 18000
> inserts/sec).
>
> So i think my bottle neck is more the CPU speed than the disk speed,
> what do you think ?
Well, I think it's fair to say it's not disk. Let's see - the original
figure was 8000 inserts/sec, which is 0.125ms per insert. That sounds
plausible to me for a round-trip to process a simple command - are you
running the client app on the same machine, or is it over the network?
Two other things to bear in mind:
1. If you're running 8.2 you can have multiple sets of values in an INSERT
http://www.postgresql.org/docs/8.2/static/sql-insert.html
2. You can do a COPY from libpq - is it really not possible?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 09:19 ` joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 32+ messages in thread
From: joël Winteregg @ 2007-03-06 09:19 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Hi and thanks for your quick answer :-)
> >
> >>> Here is my problem. With some heavy insert into a simple BD (one
> >>> table, no indexes) i can't get better perf than 8000 inserts/sec. I'm
> >>> testing it using a simple C software which use libpq and which use:
> >>> - Insert prepared statement (to avoid too many request parsing on the
> >>> server)
> >>> - transaction of 100000 inserts
> >> Are each of the INSERTs in their own transaction?
> >>
> >
> > No, as said above transactions are made of 100000 inserts...
>
> Hmm - I read that as just meaning "inserted 100000 rows". You might find
> that smaller batches provide peak performance.
>
Ahh ok ;-) sorry for my bad english... (yeah, i have been testing
several transaction size 10000, 20000 and 100000)
> >> If so, you'll be limited by the speed of the disk the WAL is running on.
> >>
> >> That means you have two main options:
> >> 1. Have multiple connections inserting simultaneously.
> >
> > Yes, you're right. That what i have been testing and what provide the
> > best performance ! I saw that postgresql frontend was using a lot of CPU
> > and not both of them (i'm using a pentium D, dual core). To the opposit,
> > the postmaster process use not much resources. Using several client,
> > both CPU are used and i saw an increase of performance (about 18000
> > inserts/sec).
> >
> > So i think my bottle neck is more the CPU speed than the disk speed,
> > what do you think ?
>
> Well, I think it's fair to say it's not disk. Let's see - the original
> figure was 8000 inserts/sec, which is 0.125ms per insert. That sounds
> plausible to me for a round-trip to process a simple command - are you
> running the client app on the same machine, or is it over the network?
I did both test. On the local machine (using UNIX sockets) i can reach
18000 insert/sec with 10 clients and prepared statements. The same test
using clients on the remote machine provide me 13000 inserts/sec.
Now, with multiple client (multi-threaded inserts) my both CPU are quite
well used (both arround 90%) so i maybe think that disk speeds are now
my bottleneck. What do you think ? or maybe i will need a better CPU ?
>
> Two other things to bear in mind:
> 1. If you're running 8.2 you can have multiple sets of values in an INSERT
> http://www.postgresql.org/docs/8.2/static/sql-insert.html
>
Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
inserts sets ! Thanks for the tip ;-)
> 2. You can do a COPY from libpq - is it really not possible?
>
Not really but i have been testing it and inserts are flying (about
100000 inserts/sec) !!
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
@ 2007-03-06 09:43 ` Richard Huxton <dev@archonet.com>
2007-03-06 12:23 ` Re: Insert performance Andreas Kostyrka <andreas@kostyrka.org>
2007-03-06 15:46 ` Re: Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 15:55 ` Re: Insert performance Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 3 replies; 32+ messages in thread
From: Richard Huxton @ 2007-03-06 09:43 UTC (permalink / raw)
To: joël Winteregg <joel.winteregg@gmail.com>; +Cc: pgsql-performance
joël Winteregg wrote:
>
>>> No, as said above transactions are made of 100000 inserts...
>> Hmm - I read that as just meaning "inserted 100000 rows". You might find
>> that smaller batches provide peak performance.
>
> Ahh ok ;-) sorry for my bad english... (yeah, i have been testing
> several transaction size 10000, 20000 and 100000)
Not your bad English, my poor reading :-)
>>>> If so, you'll be limited by the speed of the disk the WAL is running on.
>>>>
>>>> That means you have two main options:
>>>> 1. Have multiple connections inserting simultaneously.
>>> Yes, you're right. That what i have been testing and what provide the
>>> best performance ! I saw that postgresql frontend was using a lot of CPU
>>> and not both of them (i'm using a pentium D, dual core). To the opposit,
>>> the postmaster process use not much resources. Using several client,
>>> both CPU are used and i saw an increase of performance (about 18000
>>> inserts/sec).
>>>
>>> So i think my bottle neck is more the CPU speed than the disk speed,
>>> what do you think ?
>> Well, I think it's fair to say it's not disk. Let's see - the original
>> figure was 8000 inserts/sec, which is 0.125ms per insert. That sounds
>> plausible to me for a round-trip to process a simple command - are you
>> running the client app on the same machine, or is it over the network?
>
> I did both test. On the local machine (using UNIX sockets) i can reach
> 18000 insert/sec with 10 clients and prepared statements. The same test
> using clients on the remote machine provide me 13000 inserts/sec.
OK, so we know what the overhead for network connections is.
> Now, with multiple client (multi-threaded inserts) my both CPU are quite
> well used (both arround 90%) so i maybe think that disk speeds are now
> my bottleneck. What do you think ? or maybe i will need a better CPU ?
>
>> Two other things to bear in mind:
>> 1. If you're running 8.2 you can have multiple sets of values in an INSERT
>> http://www.postgresql.org/docs/8.2/static/sql-insert.html
>
> Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
> inserts sets ! Thanks for the tip ;-)
Ah-ha! Give it a go, it's designed for this sort of situation. Not sure
it'll manage thousands of value clauses, but working up from 10 perhaps.
I've not tested it for performance, so I'd be interesting in knowing how
it compares to your other results.
>> 2. You can do a COPY from libpq - is it really not possible?
>>
>
> Not really but i have been testing it and inserts are flying (about
> 100000 inserts/sec) !!
What's the problem with the COPY? Could you COPY into one table then
insert from that to your target table?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 12:23 ` Andreas Kostyrka <andreas@kostyrka.org>
2007-03-06 12:24 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 15:38 ` Re: Insert performance hatman <joel.winteregg@gmail.com>
2 siblings, 2 replies; 32+ messages in thread
From: Andreas Kostyrka @ 2007-03-06 12:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: joël Winteregg <joel.winteregg@gmail.com>; pgsql-performance
* Richard Huxton <dev@archonet.com> [070306 12:22]:
> >>2. You can do a COPY from libpq - is it really not possible?
> >>
> >Not really but i have been testing it and inserts are flying (about
> >100000 inserts/sec) !!
>
> What's the problem with the COPY? Could you COPY into one table then insert from that to your target table?
Well, there are some issues. First your client needs to support it.
E.g. psycopg2 supports only some specific CSV formatting in it's
methods. (plus I had sometimes random psycopg2 crashes, but guarding against
these is cheap compared to the speedup from COPY versus INSERT)
Plus you need to be sure that your data will apply cleanly (which in
my app was not the case), or you need to code a fallback that
localizes the row that doesn't work.
And the worst thing is, that it ignores RULES on the tables, which
sucks if you use them ;) (e.g. table partitioning).
Andreas
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 12:23 ` Re: Insert performance Andreas Kostyrka <andreas@kostyrka.org>
@ 2007-03-06 12:24 ` Richard Huxton <dev@archonet.com>
2007-03-06 12:49 ` Re: Insert performance Andreas Kostyrka <andreas@kostyrka.org>
1 sibling, 1 reply; 32+ messages in thread
From: Richard Huxton @ 2007-03-06 12:24 UTC (permalink / raw)
To: Andreas Kostyrka <andreas@kostyrka.org>; +Cc: joël Winteregg <joel.winteregg@gmail.com>; pgsql-performance
Andreas Kostyrka wrote:
> * Richard Huxton <dev@archonet.com> [070306 12:22]:
>>>> 2. You can do a COPY from libpq - is it really not possible?
>>>>
>>> Not really but i have been testing it and inserts are flying (about
>>> 100000 inserts/sec) !!
>> What's the problem with the COPY? Could you COPY into one table then insert from that to your target table?
> Well, there are some issues. First your client needs to support it.
> E.g. psycopg2 supports only some specific CSV formatting in it's
> methods. (plus I had sometimes random psycopg2 crashes, but guarding against
> these is cheap compared to the speedup from COPY versus INSERT)
> Plus you need to be sure that your data will apply cleanly (which in
> my app was not the case), or you need to code a fallback that
> localizes the row that doesn't work.
>
> And the worst thing is, that it ignores RULES on the tables, which
> sucks if you use them ;) (e.g. table partitioning).
Ah, but two things deal with these issues:
1. Joel is using libpq
2. COPY into a holding table, tidy data and INSERT ... SELECT
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 12:23 ` Re: Insert performance Andreas Kostyrka <andreas@kostyrka.org>
2007-03-06 12:24 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 12:49 ` Andreas Kostyrka <andreas@kostyrka.org>
0 siblings, 0 replies; 32+ messages in thread
From: Andreas Kostyrka @ 2007-03-06 12:49 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: joël Winteregg <joel.winteregg@gmail.com>; pgsql-performance
* Richard Huxton <dev@archonet.com> [070306 13:47]:
> Andreas Kostyrka wrote:
> >* Richard Huxton <dev@archonet.com> [070306 12:22]:
> >>>>2. You can do a COPY from libpq - is it really not possible?
> >>>>
> >>>Not really but i have been testing it and inserts are flying (about
> >>>100000 inserts/sec) !!
> >>What's the problem with the COPY? Could you COPY into one table then insert from that to your target table?
> >Well, there are some issues. First your client needs to support it.
> >E.g. psycopg2 supports only some specific CSV formatting in it's
> >methods. (plus I had sometimes random psycopg2 crashes, but guarding against
> >these is cheap compared to the speedup from COPY versus INSERT)
> >Plus you need to be sure that your data will apply cleanly (which in
> >my app was not the case), or you need to code a fallback that
> >localizes the row that doesn't work.
> >And the worst thing is, that it ignores RULES on the tables, which
> >sucks if you use them ;) (e.g. table partitioning).
>
> Ah, but two things deal with these issues:
> 1. Joel is using libpq
> 2. COPY into a holding table, tidy data and INSERT ... SELECT
Clearly COPY is the way for bulk loading data, BUT you asked, so I
wanted to point out some problems and brittle points with COPY.
(and the copy into the holding table doesn't solve completly the
problem with the dirty inconsistent data)
Andreas
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 12:23 ` Re: Insert performance Andreas Kostyrka <andreas@kostyrka.org>
@ 2007-03-06 15:38 ` hatman <joel.winteregg@gmail.com>
1 sibling, 0 replies; 32+ messages in thread
From: hatman @ 2007-03-06 15:38 UTC (permalink / raw)
To: pgsql-performance; +Cc: Andreas Kostyrka <andreas@kostyrka.org>; Richard Huxton <dev@archonet.com>
Hi Andreas,
Thanks for the info about COPY !!
On Mar 6, 1:23 pm, andr...@kostyrka.org (Andreas Kostyrka) wrote:
> * Richard Huxton <d...@archonet.com> [070306 12:22]:> >>2. You can do a COPY from libpq - is it really not possible?
>
> > >Not really but i have been testing it and inserts are flying (about
> > >100000 inserts/sec) !!
>
> > What's the problem with the COPY? Could you COPY into one table then insert from that to your target table?
>
> Well, there are some issues. First your client needs to support it.
> E.g. psycopg2 supports only some specific CSV formatting in it's
> methods. (plus I had sometimes random psycopg2 crashes, but guarding against
> these is cheap compared to the speedup from COPY versus INSERT)
> Plus you need to be sure that your data will apply cleanly (which in
> my app was not the case), or you need to code a fallback that
> localizes the row that doesn't work.
>
> And the worst thing is, that it ignores RULES on the tables, which
> sucks if you use them ;) (e.g. table partitioning).
Ok, but what about constraints (foreign keys and SERIAL id) using a
copy statement ? do we need to handle auto-generated id (SERIAL)
manually ?
Thanks for your feedback.
Regards,
Joël
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 15:46 ` hatman <joel.winteregg@gmail.com>
2 siblings, 0 replies; 32+ messages in thread
From: hatman @ 2007-03-06 15:46 UTC (permalink / raw)
To: pgsql-performance; +Cc: Richard Huxton <dev@archonet.com>; Andreas Kostyrka <andreas@kostyrka.org>
Hi Richard,
>
> >>> No, as said above transactions are made of 100000 inserts...
> >> Hmm - I read that as just meaning "inserted 100000 rows". You might find
> >> that smaller batches provide peak performance.
>
> > Ahh ok ;-) sorry for my bad english... (yeah, i have been testing
> > several transaction size 10000, 20000 and 100000)
>
> Not your bad English, my poor reading :-)
>
>
>
> >>>> If so, you'll be limited by the speed of the disk the WAL is running on.
>
> >>>> That means you have two main options:
> >>>> 1. Have multiple connections inserting simultaneously.
> >>> Yes, you're right. That what i have been testing and what provide the
> >>> best performance ! I saw that postgresql frontend was using a lot of CPU
> >>> and not both of them (i'm using a pentium D, dual core). To the opposit,
> >>> the postmaster process use not much resources. Using several client,
> >>> both CPU are used and i saw an increase of performance (about 18000
> >>> inserts/sec).
>
> >>> So i think my bottle neck is more the CPU speed than the disk speed,
> >>> what do you think ?
> >> Well, I think it's fair to say it's not disk. Let's see - the original
> >> figure was 8000 inserts/sec, which is 0.125ms per insert. That sounds
> >> plausible to me for a round-trip to process a simple command - are you
> >> running the client app on the same machine, or is it over the network?
>
> > I did both test. On the local machine (using UNIX sockets) i can reach
> > 18000 insert/sec with 10 clients and prepared statements. The same test
> > using clients on the remote machine provide me 13000 inserts/sec.
>
> OK, so we know what the overhead for network connections is.
>
> > Now, with multiple client (multi-threaded inserts) my both CPU are quite
> > well used (both arround 90%) so i maybe think that disk speeds are now
> > my bottleneck. What do you think ? or maybe i will need a better CPU ?
>
> >> Two other things to bear in mind:
> >> 1. If you're running 8.2 you can have multiple sets of values in an INSERT
> >>http://www.postgresql.org/docs/8.2/static/sql-insert.html
>
> > Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
> > inserts sets ! Thanks for the tip ;-)
>
> Ah-ha! Give it a go, it's designed for this sort of situation. Not sure
> it'll manage thousands of value clauses, but working up from 10 perhaps.
> I've not tested it for performance, so I'd be interesting in knowing how
> it compares to your other results.
Yeah, as soon as possible i will give it a try ! Thanks for the
feedback ;-)
>
> >> 2. You can do a COPY from libpq - is it really not possible?
>
> > Not really but i have been testing it and inserts are flying (about
> > 100000 inserts/sec) !!
>
> What's the problem with the COPY? Could you COPY into one table then
> insert from that to your target table?
The main problem comes from our "real time" needs. We are getting
information as a data flow from several application and we need to
store them in the DB without buffering them too much...
I have been testing the COPY using several statement (i mean using
copy to add only a few rows to a specific table and then using it on
an other table to add a few rows, etc...) and the perf are as bad as
an insert !
COPY seems to be designed to add many many rows to the same table and
not a few rows to several tables... So that's my main problem.
Regards,
Joël
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
@ 2007-03-06 15:55 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-06 16:02 ` Re: Insert performance Csaba Nagy <nagy@ecircle-ag.com>
2 siblings, 1 reply; 32+ messages in thread
From: Carlos Moreno @ 2007-03-06 15:55 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
>>> 1. If you're running 8.2 you can have multiple sets of values in an
>>> INSERT
>>> http://www.postgresql.org/docs/8.2/static/sql-insert.html
>>
>>
>> Yeah, i'm running the 8.2.3 version ! i didn't know about multiple
>> inserts sets ! Thanks for the tip ;-)
>
No kidding --- thanks for the tip from me as well !!!
I didn't know this was possible (though I read in the docs that it is ANSI
SQL standard), and I'm also having a similar situation.
Two related questions:
1) What about atomicity? Is it strictly equivalent to having multiple
insert
statements inside a transaction? (I assume it should be)
2) What about the issue with excessive locking for foreign keys when
inside a transaction? Has that issue disappeared in 8.2? And if not,
would it affect similarly in the case of multiple-row inserts?
In case you have no clue what I'm referring to:
Say that we have a table A, with one foreign key constraint to table
B --- last time I checked, there was an issue that whenever inserting
or updating table A (inside a transacion), postgres sets an exclusive
access lock on the referenced row on table B --- this is overkill, and
the correct thing to do would be to set a read-only lock (so that
no-one else can *modify or remove* the referenced row while the
transaction has not been finished).
This caused unnecessary deadlock situations --- even though no-one
is modifying table B (which is enough to guarantee that concurrent
transactions would be ok), a second transacion would fail to set the
exclusive access lock, since someone already locked it.
My solution was to sort the insert statements by the referenced value
on table B.
(I hope the above explanation clarifies what I'm trying to say)
I wonder if I should still do the same if I go with a multiple-row
insert instead of multiple insert statements inside a transaction.
Thanks,
Carlos
--
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 15:55 ` Re: Insert performance Carlos Moreno <moreno_pg@mochima.com>
@ 2007-03-06 16:02 ` Csaba Nagy <nagy@ecircle-ag.com>
2007-03-06 17:54 ` Re: Insert performance Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 1 reply; 32+ messages in thread
From: Csaba Nagy @ 2007-03-06 16:02 UTC (permalink / raw)
To: Carlos Moreno <moreno_pg@mochima.com>; +Cc: pgsql-performance
I only know to answer your no. 2:
> 2) What about the issue with excessive locking for foreign keys when
> inside a transaction? Has that issue disappeared in 8.2? And if not,
> would it affect similarly in the case of multiple-row inserts?
The exclusive lock is gone already starting with 8.0 IIRC, a
non-exclusive lock on the parent row is used instead. Thing is that this
is still too strong ;-)
The proper lock would be one which only prevents modification of the
parent key, other updates would be safe on the same row.
In any case, the current behavior is much better than what was before.
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: Insert performance
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` Re: Insert performance joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Re: Insert performance Richard Huxton <dev@archonet.com>
2007-03-06 15:55 ` Re: Insert performance Carlos Moreno <moreno_pg@mochima.com>
2007-03-06 16:02 ` Re: Insert performance Csaba Nagy <nagy@ecircle-ag.com>
@ 2007-03-06 17:54 ` Carlos Moreno <moreno_pg@mochima.com>
0 siblings, 0 replies; 32+ messages in thread
From: Carlos Moreno @ 2007-03-06 17:54 UTC (permalink / raw)
To: pgsql-performance
Csaba Nagy wrote:
>I only know to answer your no. 2:
>
>
>>2) What about the issue with excessive locking for foreign keys when
>>inside a transaction? Has that issue disappeared in 8.2? And if not,
>>would it affect similarly in the case of multiple-row inserts?
>>
>>
>
>The exclusive lock is gone already starting with 8.0 IIRC, a
>non-exclusive lock on the parent row is used instead. Thing is that this
>is still too strong ;-)
>
>The proper lock would be one which only prevents modification of the
>parent key, other updates would be safe on the same row.
>
>In any case, the current behavior is much better than what was before.
>
>
*Much* better, I would say --- though you're still correct in that it is
still
not the right thing to do.
In particular, with the previous approach. there was a serious performance
hit when concurrent transactions reference the same keys --- that is, after
having taken measures to avoid deadlocks, some transactions would have
to *wait* (for no good reason) until the other transaction is completed and
the exclusive-access lock is released. For high-traffic databases this
can be
a quite severe performance hit. I'm glad it has been fixed, even if only
partially.
Thanks,
Carlos
--
^ permalink raw reply [nested|flat] 32+ messages in thread
* insert performance
@ 2016-01-10 05:57 Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-11 19:20 ` Re: insert performance Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 32+ messages in thread
From: Jinhua Luo @ 2016-01-10 05:57 UTC (permalink / raw)
To: pgsql-performance
Hi All,
The database is postgresql 9.3, running on debian7, with 8 cpu cores and
8096MB physical memory.
There is a big table, with 70 more columns. It would be constantly at 700
rows/sec. It's not feasible to use COPY, because the data is not predefined
or provisioned, and it's generated on demand by clients.
To make a clean test env, I clone a new table, removing the indexes
(keeping the primary key) and triggers, and use pgbench to test insert
statement purely.
Here is some key items in the postgresql.conf:
--------------
shared_buffers = 1024MB
work_mem = 32MB
maintenance_work_mem = 128MB
bgwriter_delay = 20ms
synchronous_commit = off
checkpoint_segments = 64
checkpoint_completion_target = 0.9
effective_cache_size = 4096MB
log_min_duration_statement = 1000
--------------
The problem:
The log would show that the duration of some inserts exceed 1 second.
I use iotop to view the io activities of the pg backend worker process, it
shows that it writes some MB per second. It's the most confused part. The
commit is async, so it would not do and wait the wal writing, as well as
the shared buffers. I doubt it would be flush at the shared buffer
allocation. So I modify the codes, print the pgBufferUsage.blk_write_time
along with the long duration printing. But I found it's a small fraction
the total duration. I also add codes to record the total time on lock
waiting within the statement execution and print it, and it's also a small
fraction of the duration. I could not explain the result.
Then I use systemtap to check what files the process frequenlty write out:
-----
Tracing 20991 (/usr/lib/postgresql/9.3/bin/postgres)...
Please wait for 30 seconds.
=== Top 10 file writes ===
#1: 6004 times, 49184768 bytes writes in file 21986.44.
#2: 400 times, 3276800 bytes writes in file 21988.3.
#3: 12 times, 98304 bytes writes in file 57ED.
#4: 10 times, 81920 bytes writes in file 57F0.
#5: 10 times, 81920 bytes writes in file 57EE.
#6: 9 times, 73728 bytes writes in file 57F1.
#7: 9 times, 73728 bytes writes in file 57F3.
#8: 8 times, 65536 bytes writes in file 57EB.
#9: 8 times, 65536 bytes writes in file 57F2.
#10: 4 times, 32768 bytes writes in file 57EF.
-----
The "21986.44" is the table data file, and the "21988.3" is the primary key
index, and the others are subtrans files.
Obviously, the process does a lot of IO (vfs level), and I doubt the
duration spikes are due to the IO contention from time to time.
But I do not understand that why the process do so many IO with async
commit? And it does not even happen at the shared buffer flushing and locks
waiting. Where's the code path doing these IO?
Regards,
Jinhua Luo
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
@ 2016-01-10 21:05 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-13 16:41 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Jim Nasby @ 2016-01-10 21:05 UTC (permalink / raw)
To: Jinhua Luo <luajit.io@gmail.com>; pgsql-performance
On 1/9/16 11:57 PM, Jinhua Luo wrote:
> But I do not understand that why the process do so many IO with async
> commit? And it does not even happen at the shared buffer flushing and
> locks waiting. Where's the code path doing these IO?
I assume you're asking about all the IO to the heap table. That is most
likely occurring as part of ReadBuffer(). As soon as you fill up shared
buffers, BufferAlloc() is likely to end up with a dirty buffer,
resulting in it calling FlushBuffer() (see
src/backend/storage/buffer/bufmgr.c#1084).
Note that that call is tracked by
TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(), so I'd expect you to see it
in the relevant systemtap stats.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
@ 2016-01-13 16:41 ` Jinhua Luo <luajit.io@gmail.com>
2016-01-19 04:50 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Jinhua Luo @ 2016-01-13 16:41 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@bluetreble.com>; +Cc: pgsql-performance
Hi All,
I found it's not related to file I/O.
I use systemtap to diagnose the postgres backend process.
The systemtap script is a modified version of sample-bt-off-cpu:
https://gist.github.com/kingluo/15b656998035cef193bc
Test process:
1) create a simple table:
-----------
create table test(k bigserial primary key, a int, b int, c text, d text);
-----------
2) test.sql:
-----------
insert into test(a, b, c, d) values(3438, 1231,
'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
-----------
3) I run the pgbench, using 4 connections:
-----------
$ time pgbench -r -N -n -c 4 -j 1 -T 3600 -f test.sql
-----------
4) I run the systemtap script for 3 minutes (Here the 987 is the pid
of one postgres backend process):
Note that sum, max, avg, min time is in microsecond.
-----------
# ./sample-bt-off-cpu -a -v -p 987 -t 180 > /tmp/postgres.bt
Pass 1: parsed user script and 110 library script(s) using
26916virt/22300res/4276shr/18740data kb, in 380usr/10sys/386real ms.
Pass 2: analyzed script: 18 probe(s), 13 function(s), 5 embed(s), 12
global(s) using 54708virt/51956res/5920shr/46532data kb, in
1870usr/360sys/2669real ms.
Pass 3: translated to C into
"/tmp/stapteVG3Q/stap_18d161acb3024931de917335496977c3_12813_src.c"
using 54708virt/52156res/6120shr/46532data kb, in
8680usr/250sys/24647real ms.
Pass 4: compiled C into
"stap_18d161acb3024931de917335496977c3_12813.ko" in
20450usr/610sys/43898real ms.
Pass 5: starting run.
WARNING: Tracing 987 (/opt/postgresql/bin/postgres)...
WARNING: Too many CFI instuctions
WARNING: Time's up. Quitting now...(it may take a while)
WARNING: query time, count=646253, sum=102991078, max=2474344, avg=159, min=83
WARNING: lock time, count=142, sum=3306500, max=1158862, avg=23285, min=16
WARNING: lwlock time, count=141272, sum=7260098, max=1383180, avg=51, min=1
WARNING: Number of errors: 0, skipped probes: 24
Pass 5: run completed in 10usr/110sys/180744real ms.
-----------
During that 3 minutes, the postgres prints below logs:
-----------
2016-01-13 23:27:21 CST [987-157] postgres@postgres LOG: duration:
2474.304 ms statement: insert into test(a, b, c, d) values(3438,
1231, 'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
2016-01-13 23:27:48 CST [987-158] postgres@postgres LOG: duration:
1383.359 ms statement: insert into test(a, b, c, d) values(3438,
1231, 'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
2016-01-13 23:28:33 CST [987-159] postgres@postgres LOG: process 987
still waiting for ExclusiveLock on extension of relation 16386 of
database 12141 after 1000.212 ms
2016-01-13 23:28:33 CST [987-160] postgres@postgres DETAIL: Process
holding the lock: 990. Wait queue: 988, 987, 989.
2016-01-13 23:28:33 CST [987-161] postgres@postgres STATEMENT: insert
into test(a, b, c, d) values(3438, 1231,
'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
2016-01-13 23:28:33 CST [987-162] postgres@postgres LOG: process 987
acquired ExclusiveLock on extension of relation 16386 of database
12141 after 1158.818 ms
2016-01-13 23:28:33 CST [987-163] postgres@postgres STATEMENT: insert
into test(a, b, c, d) values(3438, 1231,
'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
2016-01-13 23:28:33 CST [987-164] postgres@postgres LOG: duration:
1159.512 ms statement: insert into test(a, b, c, d) values(3438,
1231, 'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
2016-01-13 23:28:45 CST [987-165] postgres@postgres LOG: duration:
1111.664 ms statement: insert into test(a, b, c, d) values(3438,
1231, 'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
-----------
The final backtrace result is converted into flame graph, see:
http://luajit.io/systemtap/pgsql/postgres_1.svg
Any advice?
Regards,
Jinhua Luo
2016-01-11 5:05 GMT+08:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
> On 1/9/16 11:57 PM, Jinhua Luo wrote:
>>
>> But I do not understand that why the process do so many IO with async
>> commit? And it does not even happen at the shared buffer flushing and
>> locks waiting. Where's the code path doing these IO?
>
>
> I assume you're asking about all the IO to the heap table. That is most
> likely occurring as part of ReadBuffer(). As soon as you fill up shared
> buffers, BufferAlloc() is likely to end up with a dirty buffer, resulting in
> it calling FlushBuffer() (see src/backend/storage/buffer/bufmgr.c#1084).
>
> Note that that call is tracked by
> TRACE_POSTGRESQL_BUFFER_WRITE_DIRTY_START(), so I'd expect you to see it in
> the relevant systemtap stats.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-13 16:41 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
@ 2016-01-19 04:50 ` Jinhua Luo <luajit.io@gmail.com>
2016-01-22 05:54 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
0 siblings, 1 reply; 32+ messages in thread
From: Jinhua Luo @ 2016-01-19 04:50 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@bluetreble.com>; +Cc: pgsql-performance
Hi,
I thought with async commit enabled, the backend process would rarely
do file io. But in fact, it still involves a lot of file io.
After inspecting the vfs probes using systemtap, and reading the
source codes of postgresql, I found the tight loop of insert or update
will cause heavy file io upon the data files (table, indexes) directly
by the backend process! And those io has nothing to do with shared
buffer dirty writes.
The heap_insert() or heap_update() would invoke
RelationGetBufferForTuple(), which in turn finally invoke
ReadBuffer_common():
1) lookup or allocate the buffer from shared buffer, which may cause
dirty write (but in my case, it's rare. Maybe the shared buffer is big
enough and the checkpointer or bgwriter always clean it in time). If
the target buffer is found, skip following steps.
2) if it needs to extend the relation (insert or update on new table
would normally fall in this case), then it would write zero-filled
page into the disk (used to occupy the file space? But most file
systems support file hole or space reservation, so maybe this part
could be optimized?) This procedure would hold the exclusive lock on
the relation. So if the write is slow, it would slow down all pending
queries of the lock waiters.
3) Otherwise, it would read from disk.
The target buffer would be locked exclusively until the insert or
update finish. Note that the insert or update also involve xlog
insert, although with async commit enabled, the backend process would
not flush the xlog, but chances are that the xlog buffer dirty writes
happens (although it's also rare in my case).
So I guess the real reason is the file io with lock holding. If io
spike happens, it would cause long query duration.
Am I correct? Look forward to any advice.
Thanks.
Regards,
Jinhua Luo
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-13 16:41 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-19 04:50 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
@ 2016-01-22 05:54 ` Jinhua Luo <luajit.io@gmail.com>
2016-01-24 23:24 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 1 reply; 32+ messages in thread
From: Jinhua Luo @ 2016-01-22 05:54 UTC (permalink / raw)
To: Jim Nasby <Jim.Nasby@bluetreble.com>; +Cc: pgsql-performance
Hi,
There is another problem.
When the autovacuum recycles the old pages, the ReadBuffer_common()
would do mdread() instead of mdextend().
The read is synchronous, while the write could be mostly asynchronous,
so the frequent read is much worse than write version.
Any help? Please.
Regards,
Jinhua Luo
2016-01-19 12:50 GMT+08:00 Jinhua Luo <luajit.io@gmail.com>:
> Hi,
>
> I thought with async commit enabled, the backend process would rarely
> do file io. But in fact, it still involves a lot of file io.
>
> After inspecting the vfs probes using systemtap, and reading the
> source codes of postgresql, I found the tight loop of insert or update
> will cause heavy file io upon the data files (table, indexes) directly
> by the backend process! And those io has nothing to do with shared
> buffer dirty writes.
>
> The heap_insert() or heap_update() would invoke
> RelationGetBufferForTuple(), which in turn finally invoke
> ReadBuffer_common():
>
> 1) lookup or allocate the buffer from shared buffer, which may cause
> dirty write (but in my case, it's rare. Maybe the shared buffer is big
> enough and the checkpointer or bgwriter always clean it in time). If
> the target buffer is found, skip following steps.
>
> 2) if it needs to extend the relation (insert or update on new table
> would normally fall in this case), then it would write zero-filled
> page into the disk (used to occupy the file space? But most file
> systems support file hole or space reservation, so maybe this part
> could be optimized?) This procedure would hold the exclusive lock on
> the relation. So if the write is slow, it would slow down all pending
> queries of the lock waiters.
>
> 3) Otherwise, it would read from disk.
>
> The target buffer would be locked exclusively until the insert or
> update finish. Note that the insert or update also involve xlog
> insert, although with async commit enabled, the backend process would
> not flush the xlog, but chances are that the xlog buffer dirty writes
> happens (although it's also rare in my case).
>
> So I guess the real reason is the file io with lock holding. If io
> spike happens, it would cause long query duration.
>
> Am I correct? Look forward to any advice.
>
> Thanks.
>
> Regards,
> Jinhua Luo
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-13 16:41 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-19 04:50 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-22 05:54 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
@ 2016-01-24 23:24 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 0 replies; 32+ messages in thread
From: Jim Nasby @ 2016-01-24 23:24 UTC (permalink / raw)
To: Jinhua Luo <luajit.io@gmail.com>; +Cc: pgsql-performance
On 1/21/16 11:54 PM, Jinhua Luo wrote:
> There is another problem.
> When the autovacuum recycles the old pages, the ReadBuffer_common()
> would do mdread() instead of mdextend().
> The read is synchronous, while the write could be mostly asynchronous,
> so the frequent read is much worse than write version.
Please don't top post.
AFAICT your analysis is correct, though I don't see what autovacuum has
to do with anything. When we need a new block to put data on we'll
either find one with free space in the free space map and use it, or
we'll extend the relation.
> Any help? Please.
There's been some discussion on ways to improve the performance of
relation extension (iirc one of those was to not zero the new page), but
ultimately you're at the mercy of the underlying OS and hardware.
If you have ideas for improving this you should speak up on -hackers,
but before doing so you should read the archives about what's been
proposed in the past.
> 2016-01-19 12:50 GMT+08:00 Jinhua Luo <luajit.io@gmail.com>:
>> Hi,
>>
>> I thought with async commit enabled, the backend process would rarely
>> do file io. But in fact, it still involves a lot of file io.
>>
>> After inspecting the vfs probes using systemtap, and reading the
>> source codes of postgresql, I found the tight loop of insert or update
>> will cause heavy file io upon the data files (table, indexes) directly
>> by the backend process! And those io has nothing to do with shared
>> buffer dirty writes.
>>
>> The heap_insert() or heap_update() would invoke
>> RelationGetBufferForTuple(), which in turn finally invoke
>> ReadBuffer_common():
>>
>> 1) lookup or allocate the buffer from shared buffer, which may cause
>> dirty write (but in my case, it's rare. Maybe the shared buffer is big
>> enough and the checkpointer or bgwriter always clean it in time). If
>> the target buffer is found, skip following steps.
>>
>> 2) if it needs to extend the relation (insert or update on new table
>> would normally fall in this case), then it would write zero-filled
>> page into the disk (used to occupy the file space? But most file
>> systems support file hole or space reservation, so maybe this part
>> could be optimized?) This procedure would hold the exclusive lock on
>> the relation. So if the write is slow, it would slow down all pending
>> queries of the lock waiters.
>>
>> 3) Otherwise, it would read from disk.
>>
>> The target buffer would be locked exclusively until the insert or
>> update finish. Note that the insert or update also involve xlog
>> insert, although with async commit enabled, the backend process would
>> not flush the xlog, but chances are that the xlog buffer dirty writes
>> happens (although it's also rare in my case).
>>
>> So I guess the real reason is the file io with lock holding. If io
>> spike happens, it would cause long query duration.
>>
>> Am I correct? Look forward to any advice.
>>
>> Thanks.
>>
>> Regards,
>> Jinhua Luo
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
@ 2016-01-11 19:20 ` Jeff Janes <jeff.janes@gmail.com>
2016-01-13 10:03 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
1 sibling, 1 reply; 32+ messages in thread
From: Jeff Janes @ 2016-01-11 19:20 UTC (permalink / raw)
To: Jinhua Luo <luajit.io@gmail.com>; +Cc: pgsql-performance
On Sat, Jan 9, 2016 at 9:57 PM, Jinhua Luo <luajit.io@gmail.com> wrote:
>
> To make a clean test env, I clone a new table, removing the indexes (keeping
> the primary key) and triggers, and use pgbench to test insert statement
> purely.
Can you share the pgbench command line, and the sql file you feed to
it (and whatever is needed to set up the schema)?
Thanks,
Jeff
--
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] 32+ messages in thread
* Re: insert performance
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-11 19:20 ` Re: insert performance Jeff Janes <jeff.janes@gmail.com>
@ 2016-01-13 10:03 ` Jinhua Luo <luajit.io@gmail.com>
0 siblings, 0 replies; 32+ messages in thread
From: Jinhua Luo @ 2016-01-13 10:03 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
Hi,
I found the insert performance is not related to the table schema.
In fact, I could recur the issue using simple table:
create table test(k bigserial primary key, a int, b int, c text, d text);
test.sql:
insert into test(a, b, c, d) values(3438, 1231,
'ooooooooooooooooooooooooooooooooooooo',
'kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk');
pgbench -r -N -n -c 4 -j 1 -T 600 -f test.sql
I also compile and run it on the latest 9.4 version, the same issue.
Regards,
Jinhua Luo
2016-01-12 3:20 GMT+08:00 Jeff Janes <jeff.janes@gmail.com>:
> On Sat, Jan 9, 2016 at 9:57 PM, Jinhua Luo <luajit.io@gmail.com> wrote:
>>
>> To make a clean test env, I clone a new table, removing the indexes (keeping
>> the primary key) and triggers, and use pgbench to test insert statement
>> purely.
>
> Can you share the pgbench command line, and the sql file you feed to
> it (and whatever is needed to set up the schema)?
>
>
> Thanks,
>
> Jeff
--
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] 32+ messages in thread
end of thread, other threads:[~2016-01-24 23:24 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-16 08:40 Insert performance Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-16 18:40 ` Josh Berkus <josh@agliodbs.com>
2003-08-16 22:22 ` Christopher Browne <cbbrowne@acm.org>
2003-08-18 16:57 ` Josh Berkus <josh@agliodbs.com>
2003-08-19 06:28 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 06:21 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 15:52 ` Hannu Krosing <hannu@tm.ee>
2003-08-18 16:02 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-08-18 22:16 ` Hannu Krosing <hannu@tm.ee>
2003-08-19 06:33 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2007-02-27 17:10 Insert performance hatman <joel.winteregg@gmail.com>
2007-03-06 07:34 ` Richard Huxton <dev@archonet.com>
2007-03-06 07:53 ` joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 08:08 ` Richard Huxton <dev@archonet.com>
2007-03-06 09:19 ` joël Winteregg <joel.winteregg@gmail.com>
2007-03-06 09:43 ` Richard Huxton <dev@archonet.com>
2007-03-06 12:23 ` Andreas Kostyrka <andreas@kostyrka.org>
2007-03-06 12:24 ` Richard Huxton <dev@archonet.com>
2007-03-06 12:49 ` Andreas Kostyrka <andreas@kostyrka.org>
2007-03-06 15:38 ` hatman <joel.winteregg@gmail.com>
2007-03-06 15:46 ` hatman <joel.winteregg@gmail.com>
2007-03-06 15:55 ` Carlos Moreno <moreno_pg@mochima.com>
2007-03-06 16:02 ` Csaba Nagy <nagy@ecircle-ag.com>
2007-03-06 17:54 ` Carlos Moreno <moreno_pg@mochima.com>
2016-01-10 05:57 insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-10 21:05 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-13 16:41 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-19 04:50 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-22 05:54 ` Re: insert performance Jinhua Luo <luajit.io@gmail.com>
2016-01-24 23:24 ` Re: insert performance Jim Nasby <Jim.Nasby@BlueTreble.com>
2016-01-11 19:20 ` Re: insert performance Jeff Janes <jeff.janes@gmail.com>
2016-01-13 10:03 ` Re: insert performance Jinhua Luo <luajit.io@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