pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed8.4.1 ubuntu karmic slow createdb
22+ messages / 10 participants
[nested] [flat]
* 8.4.1 ubuntu karmic slow createdb
@ 2009-12-10 20:41 Michael Clemmons <glassresistor@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Clemmons @ 2009-12-10 20:41 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Hey,
I've got a computer which runs but 8.3 and 8.4. To create a db it takes 4s
for 8.3 and 9s for 8.4. I have many unit tests which create databases all
of the time and now run much slower than 8.3 but it seems to be much longer
as I remember at one point creating databases I considered an instantaneous
thing. Does any on the list know why this is true and if I can get it back
to normal.
-Michael
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-10 21:56 Andres Freund <andres@anarazel.de>
parent: Michael Clemmons <glassresistor@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andres Freund @ 2009-12-10 21:56 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Michael Clemmons <glassresistor@gmail.com>
On Thursday 10 December 2009 21:41:08 Michael Clemmons wrote:
> Hey,
> I've got a computer which runs but 8.3 and 8.4. To create a db it takes 4s
> for 8.3 and 9s for 8.4. I have many unit tests which create databases all
> of the time and now run much slower than 8.3 but it seems to be much longer
> as I remember at one point creating databases I considered an instantaneous
> thing. Does any on the list know why this is true and if I can get it back
> to normal.
Possibly you had fsync=off at the time?
Andres
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-10 22:01 Michael Clemmons <glassresistor@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Clemmons @ 2009-12-10 22:01 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Im not sure what that means ppl in my office with slower hd speeds using 8.4
can create a db in 2s vs my 8-12s. Could using md5 instead of ident do it?
On Thu, Dec 10, 2009 at 4:56 PM, Andres Freund <andres@anarazel.de> wrote:
> On Thursday 10 December 2009 21:41:08 Michael Clemmons wrote:
> > Hey,
> > I've got a computer which runs but 8.3 and 8.4. To create a db it takes
> 4s
> > for 8.3 and 9s for 8.4. I have many unit tests which create databases
> all
> > of the time and now run much slower than 8.3 but it seems to be much
> longer
> > as I remember at one point creating databases I considered an
> instantaneous
> > thing. Does any on the list know why this is true and if I can get it
> back
> > to normal.
> Possibly you had fsync=off at the time?
>
> Andres
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-10 22:09 Andres Freund <andres@anarazel.de>
parent: Michael Clemmons <glassresistor@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andres Freund @ 2009-12-10 22:09 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Michael Clemmons <glassresistor@gmail.com>
Hi,
On Thursday 10 December 2009 23:01:08 Michael Clemmons wrote:
> Im not sure what that means ppl in my office with slower hd speeds using
> 8.4 can create a db in 2s vs my 8-12s.
- Possibly their config is different - they could have disabled the "fsync"
parameter which turns the database to be not crashsafe anymore but much faster
in some circumstances.
- Possibly you have much data in your template1 database?
You could check whether
CREATE DATABASE speedtest TEMPLATE template1; takes more time than
CREATE DATABASE speedtest TEMPLATE template0;.
You should issue both multiple times to ensure caching on the template
database doesnt play a role.
> Could using md5 instead of ident do it?
Seems unlikely.
Is starting psql near-instantaneus? Are you using "createdb" or are you
issuing "CREATE DATABASE ..."?
Andres
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 01:38 Nikolas Everett <nik9000@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 22+ messages in thread
From: Nikolas Everett @ 2009-12-11 01:38 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
In my limited experience ext4 as presented by Karmic is not db friendly. I
had to carve my swap partition into a swap partition and an xfs partition to
get better db performance. Try fsync=off first, but if that doesn't work
then try a mini xfs.
On Thu, Dec 10, 2009 at 5:09 PM, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On Thursday 10 December 2009 23:01:08 Michael Clemmons wrote:
> > Im not sure what that means ppl in my office with slower hd speeds using
> > 8.4 can create a db in 2s vs my 8-12s.
> - Possibly their config is different - they could have disabled the "fsync"
> parameter which turns the database to be not crashsafe anymore but much
> faster
> in some circumstances.
>
> - Possibly you have much data in your template1 database?
> You could check whether
>
> CREATE DATABASE speedtest TEMPLATE template1; takes more time than
> CREATE DATABASE speedtest TEMPLATE template0;.
>
> You should issue both multiple times to ensure caching on the template
> database doesnt play a role.
>
> > Could using md5 instead of ident do it?
> Seems unlikely.
> Is starting psql near-instantaneus? Are you using "createdb" or are you
> issuing "CREATE DATABASE ..."?
>
> Andres
>
> --
> 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] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 17:58 Joshua D. Drake <jd@commandprompt.com>
parent: Nikolas Everett <nik9000@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Joshua D. Drake @ 2009-12-11 17:58 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
On Thu, 2009-12-10 at 20:38 -0500, Nikolas Everett wrote:
> In my limited experience ext4 as presented by Karmic is not db
> friendly. I had to carve my swap partition into a swap partition and
> an xfs partition to get better db performance. Try fsync=off first,
> but if that doesn't work then try a mini xfs.
Do not turn fsync off. That is bad advice. I would not suggest ext4 at
this point for database operations. Use ext3. It is backward compatible.
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 20:43 Nikolas Everett <nik9000@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 22+ messages in thread
From: Nikolas Everett @ 2009-12-11 20:43 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
Turning fsync off on a dev database is a bad idea? Sure you might kill it
and have to start over, but thats kind of the point in a dev database.
On Fri, Dec 11, 2009 at 12:58 PM, Joshua D. Drake <jd@commandprompt.com>wrote:
> On Thu, 2009-12-10 at 20:38 -0500, Nikolas Everett wrote:
> > In my limited experience ext4 as presented by Karmic is not db
> > friendly. I had to carve my swap partition into a swap partition and
> > an xfs partition to get better db performance. Try fsync=off first,
> > but if that doesn't work then try a mini xfs.
>
> Do not turn fsync off. That is bad advice. I would not suggest ext4 at
> this point for database operations. Use ext3. It is backward compatible.
>
> Joshua D. Drake
>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
> Consulting, Training, Support, Custom Development, Engineering
> If the world pushes look it in the eye and GRR. Then push back harder. -
> Salamander
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 20:50 Joshua D. Drake <jd@commandprompt.com>
parent: Nikolas Everett <nik9000@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Joshua D. Drake @ 2009-12-11 20:50 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
On Fri, 2009-12-11 at 15:43 -0500, Nikolas Everett wrote:
> Turning fsync off on a dev database is a bad idea? Sure you might
> kill it and have to start over, but thats kind of the point in a dev
> database.
My experience is that bad dev practices turn into bad production
practices, whether intentionally or not.
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 21:39 Nikolas Everett <nik9000@gmail.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 22+ messages in thread
From: Nikolas Everett @ 2009-12-11 21:39 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 3:50 PM, Joshua D. Drake <jd@commandprompt.com>wrote:
> On Fri, 2009-12-11 at 15:43 -0500, Nikolas Everett wrote:
> > Turning fsync off on a dev database is a bad idea? Sure you might
> > kill it and have to start over, but thats kind of the point in a dev
> > database.
>
> My experience is that bad dev practices turn into bad production
> practices, whether intentionally or not.
>
Fair enough. I'm of the opinion that developers need to have their unit
tests run fast. If they aren't fast then your just not going to test as
much as you should. If your unit tests *have* to createdb then you have to
do whatever you have to do to get it fast. It'd probably be better if unit
tests don't create databases or alter tables at all though.
Regardless of what is going on on your dev box you really should leave fsync
on on your continuous integration, integration test, and QA machines.
They're what your really modeling your production on anyway.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 21:57 Scott Marlowe <scott.marlowe@gmail.com>
parent: Nikolas Everett <nik9000@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2009-12-11 21:57 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: jd@commandprompt.com, Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 2:39 PM, Nikolas Everett <nik9000@gmail.com> wrote:
>
>
> On Fri, Dec 11, 2009 at 3:50 PM, Joshua D. Drake <jd@commandprompt.com>
> wrote:
>>
>> On Fri, 2009-12-11 at 15:43 -0500, Nikolas Everett wrote:
>> > Turning fsync off on a dev database is a bad idea? Sure you might
>> > kill it and have to start over, but thats kind of the point in a dev
>> > database.
>>
>> My experience is that bad dev practices turn into bad production
>> practices, whether intentionally or not.
>
> Fair enough. I'm of the opinion that developers need to have their unit
> tests run fast. If they aren't fast then your just not going to test as
> much as you should. If your unit tests *have* to createdb then you have to
> do whatever you have to do to get it fast. It'd probably be better if unit
> tests don't create databases or alter tables at all though.
This is my big issue. dropping / creating databases for unit tests is
overkill. Running any DDL at all for a unit test seems wrong to me
too. Insert a row if you need it, MAYBE. Unit tests should work with
a test database that HAS the structure and database already in place.
What happens if your unit tests get lose in production and drop a
database, or a table. Not good.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 21:59 Scott Mead <scott.lists@enterprisedb.com>
parent: Nikolas Everett <nik9000@gmail.com>
1 sibling, 2 replies; 22+ messages in thread
From: Scott Mead @ 2009-12-11 21:59 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: jd <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 4:39 PM, Nikolas Everett <nik9000@gmail.com> wrote:
>
>
>
> Fair enough. I'm of the opinion that developers need to have their unit
> tests run fast. If they aren't fast then your just not going to test as
> much as you should. If your unit tests *have* to createdb then you have to
> do whatever you have to do to get it fast. It'd probably be better if unit
> tests don't create databases or alter tables at all though.
>
> Regardless of what is going on on your dev box you really should leave
> fsync on on your continuous integration, integration test, and QA machines.
> They're what your really modeling your production on anyway.
>
The other common issue is that developers running with something like
'fsync=off' means that they have completely unrealistic expectations of the
performance surrounding something. If your developers see that when fsync
is on, createdb takes x seconds vs. when it's off, then they'll know that
basing their entire process on that probably isn't a good idea. When
developers think something is lightning, they tend to base lots of stuff on
it, whether it's production ready or not.
--Scott
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 22:12 Scott Carey <scott@richrelevance.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Scott Carey @ 2009-12-11 22:12 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Nikolas Everett <nik9000@gmail.com>; +Cc: jd@commandprompt.com <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
On 12/11/09 1:57 PM, "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
> This is my big issue. dropping / creating databases for unit tests is
> overkill. Running any DDL at all for a unit test seems wrong to me
> too. Insert a row if you need it, MAYBE. Unit tests should work with
> a test database that HAS the structure and database already in place.
>
> What happens if your unit tests get lose in production and drop a
> database, or a table. Not good.
>
Production should not have a db with the same username/pw combination as dev
boxes and unit tests . . .
Unfortunately, unit-like (often more than a 'unit') tests can't always rely
on a test db being already set up. If one leaves any cruft around, it might
break later tests later on non-deterministically. Automated tests that
insert data are absolutely required somewhere if the application inserts
data.
The best way to do this in postgres is to create a template database from
scratch with whatever DDL is needed at the start of the run, and then create
and drop db's as copies of that template per test or test suite.
So no, its not overkill at all IMO. I do wish to avoid it, and ideally all
tests clean up after themselves, but in practice this does not happen and
results in hard to track down issues where test X fails because of something
that any one of tests A to W did (all of which pass), often wasting time of
the most valuable developers -- those who know the majority of the system
well enough to track down such issues across the whole system.
One thing to consider, is putting this temp database in a RAMFS, or ramdisk
since postgres does a lot of file creates and fsyncs when cloning a db from
a template. For almost all such test db's the actual data is small, but the
# of tables is large.
> --
> 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] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 22:12 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Mead <scott.lists@enterprisedb.com>
1 sibling, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2009-12-11 22:12 UTC (permalink / raw)
To: Scott Mead <scott.lists@enterprisedb.com>; +Cc: Nikolas Everett <nik9000@gmail.com>; jd <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 2:59 PM, Scott Mead
<scott.lists@enterprisedb.com> wrote:
> On Fri, Dec 11, 2009 at 4:39 PM, Nikolas Everett <nik9000@gmail.com> wrote:
>>
>>
>>
>> Fair enough. I'm of the opinion that developers need to have their unit
>> tests run fast. If they aren't fast then your just not going to test as
>> much as you should. If your unit tests *have* to createdb then you have to
>> do whatever you have to do to get it fast. It'd probably be better if unit
>> tests don't create databases or alter tables at all though.
>>
>> Regardless of what is going on on your dev box you really should leave
>> fsync on on your continuous integration, integration test, and QA machines.
>> They're what your really modeling your production on anyway.
>
>
> The other common issue is that developers running with something like
> 'fsync=off' means that they have completely unrealistic expectations of the
> performance surrounding something. If your developers see that when fsync
> is on, createdb takes x seconds vs. when it's off, then they'll know that
> basing their entire process on that probably isn't a good idea. When
> developers think something is lightning, they tend to base lots of stuff on
> it, whether it's production ready or not.
Yeah, it's a huge mistake to give development super fast servers to
test on. Keep in mind production may need to handle 10k requests a
minute / second whatever. Developers cannot generate that kind of
load by just pointing and clicking. Our main production is on a
cluster of 8 and 12 core machines with scads of memory and RAID-10
arrays all over the place. Development gets a 4 core machine with 8G
ram and an 8 drive RAID-6. It ain't slow, but it ain't really that
fast either.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 22:19 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 22+ messages in thread
From: Scott Marlowe @ 2009-12-11 22:19 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Nikolas Everett <nik9000@gmail.com>; jd@commandprompt.com <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 3:12 PM, Scott Carey <scott@richrelevance.com> wrote:
>
> On 12/11/09 1:57 PM, "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
>>
>> This is my big issue. dropping / creating databases for unit tests is
>> overkill. Running any DDL at all for a unit test seems wrong to me
>> too. Insert a row if you need it, MAYBE. Unit tests should work with
>> a test database that HAS the structure and database already in place.
>>
>> What happens if your unit tests get lose in production and drop a
>> database, or a table. Not good.
>>
>
> Production should not have a db with the same username/pw combination as dev
> boxes and unit tests . . .
>
> Unfortunately, unit-like (often more than a 'unit') tests can't always rely
> on a test db being already set up. If one leaves any cruft around, it might
> break later tests later on non-deterministically. Automated tests that
> insert data are absolutely required somewhere if the application inserts
> data.
>
> The best way to do this in postgres is to create a template database from
> scratch with whatever DDL is needed at the start of the run, and then create
> and drop db's as copies of that template per test or test suite.
Debateable. Last job we had 44k or so unit tests, and we gave each
dev their own db made from the main qa / unit testing db that they
could refresh at any time, and run the unit tests locally before
committing code. Actual failures like the one you mention were very
rare because of this approach. A simple ant refresh-db and they were
ready to test their code before committing it to the continuous
testing farm.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 22:39 Greg Smith <greg@2ndquadrant.com>
parent: Scott Mead <scott.lists@enterprisedb.com>
1 sibling, 1 reply; 22+ messages in thread
From: Greg Smith @ 2009-12-11 22:39 UTC (permalink / raw)
To: Scott Mead <scott.lists@enterprisedb.com>; +Cc: Nikolas Everett <nik9000@gmail.com>; jd <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
Scott Mead wrote:
> The other common issue is that developers running with something like
> 'fsync=off' means that they have completely unrealistic expectations
> of the performance surrounding something.
Right, but the flip side here is that often the production server will
have hardware such as a caching RAID card that vastly improves
performance in this area. There's some room to cheat in order to
accelerate the dev systems lack of such things, while still not giving a
completely unrealistic view of performance.
As far as I'm concerned, using "fsync=off" is almost never excusable if
you're running 8.3 or later where "synchronous_commit=off" is a
possibility. If you use that, it will usually improve the worst part of
commit issues substantially. And it happens in a way that's actually
quite similar to how a caching write production server will run: small
writes happen instantly, but eventually bigger ones will end up
bottlenecked at the disks anyway.
It would improve the average safety of our community members if anytime
someone suggests "fsync=off", we strongly suggest
"synchronous_commit=off" and potentially tuning its interval instead as
a middle ground, while still helping people who need to speed their
systems up. Saying "never turn fsync off" without suggesting this
alternative is counter-productive. If you're in the sort of position
where fsync is killing your performance you'll do anything to speed
things up (I've seen a 100:1 speed improvement) no matter how risky.
I've ran a production system of 8.2 with fsync off, a TB of data, and no
safety net if a crash introduced corruption beyond a ZFS snapshot. It
wasn't fun, but it was the only possibility to get bulk loading (there
was an ETL step in the middle after COPY) to happen fast enough. Using
async commit instead is a much better approach now that it's available.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 22:52 Michael Clemmons <glassresistor@gmail.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 2 replies; 22+ messages in thread
From: Michael Clemmons @ 2009-12-11 22:52 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Thanks all this has been a good help.
I don't have control(or easy control) over unit tests creating/deleting
databases since Im using the django framework for this job. Createdb takes
12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow for
running 200unittests. Fsync got it to .2secs or so which is blazing but
also the speed I expected being used to 8.3 and xfs. This dev box is my
laptop and the data is litterally unimportant and doesn't exist longer than
20sec but Im all about good practices. Will definately try synchronous
commit tonight once Im done working for the day. I've got some massive
copying todo later though so this will probably help in the future as well.
On Fri, Dec 11, 2009 at 5:39 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Mead wrote:
>
>> The other common issue is that developers running with something like
>> 'fsync=off' means that they have completely unrealistic expectations of the
>> performance surrounding something.
>>
> Right, but the flip side here is that often the production server will have
> hardware such as a caching RAID card that vastly improves performance in
> this area. There's some room to cheat in order to accelerate the dev
> systems lack of such things, while still not giving a completely unrealistic
> view of performance.
>
> As far as I'm concerned, using "fsync=off" is almost never excusable if
> you're running 8.3 or later where "synchronous_commit=off" is a possibility.
> If you use that, it will usually improve the worst part of commit issues
> substantially. And it happens in a way that's actually quite similar to how
> a caching write production server will run: small writes happen instantly,
> but eventually bigger ones will end up bottlenecked at the disks anyway.
>
> It would improve the average safety of our community members if anytime
> someone suggests "fsync=off", we strongly suggest "synchronous_commit=off"
> and potentially tuning its interval instead as a middle ground, while still
> helping people who need to speed their systems up. Saying "never turn fsync
> off" without suggesting this alternative is counter-productive. If you're
> in the sort of position where fsync is killing your performance you'll do
> anything to speed things up (I've seen a 100:1 speed improvement) no matter
> how risky. I've ran a production system of 8.2 with fsync off, a TB of
> data, and no safety net if a crash introduced corruption beyond a ZFS
> snapshot. It wasn't fun, but it was the only possibility to get bulk
> loading (there was an ETL step in the middle after COPY) to happen fast
> enough. Using async commit instead is a much better approach now that it's
> available.
>
> --
> Greg Smith 2ndQuadrant Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com www.2ndQuadrant.com
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-11 23:59 Scott Marlowe <scott.marlowe@gmail.com>
parent: Michael Clemmons <glassresistor@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2009-12-11 23:59 UTC (permalink / raw)
To: Michael Clemmons <glassresistor@gmail.com>; +Cc: pgsql-performance@postgresql.org
On Fri, Dec 11, 2009 at 3:52 PM, Michael Clemmons
<glassresistor@gmail.com> wrote:
> Thanks all this has been a good help.
> I don't have control(or easy control) over unit tests creating/deleting
> databases since Im using the django framework for this job.
Reminds of the issues we had with Ruby on Rails and it's (at the time)
very mysql-centric tools that made us take a fork to large portions of
its brain to get things like this working. Worked with a developer
for a day or two fixing most of the worst mysqlisms in RoR at the time
to just get this kind of stuff working.
> Createdb takes
> 12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow for
> running 200unittests.
Wait, so each unit test createdbs by itself? Wow...
> Fsync got it to .2secs or so which is blazing but
> also the speed I expected being used to 8.3 and xfs. This dev box is my
> laptop and the data is litterally unimportant and doesn't exist longer than
> 20sec but Im all about good practices. Will definately try synchronous
> commit tonight once Im done working for the day. I've got some massive
> copying todo later though so this will probably help in the future as well.
Yeah, I'd probably resort to fsync off in that circumstance too
especially if syn commit off didn't help that much.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-12 00:19 Andres Freund <andres@anarazel.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Andres Freund @ 2009-12-12 00:19 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Michael Clemmons <glassresistor@gmail.com>
Hi,
On Saturday 12 December 2009 00:59:13 Scott Marlowe wrote:
> On Fri, Dec 11, 2009 at 3:52 PM, Michael Clemmons
> > Createdb takes
> > 12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow for
> > running 200unittests.
> > Fsync got it to .2secs or so which is blazing but
> > also the speed I expected being used to 8.3 and xfs. This dev box is my
> > laptop and the data is litterally unimportant and doesn't exist longer
> > than 20sec but Im all about good practices. Will definately try
> > synchronous commit tonight once Im done working for the day. I've got
> > some massive copying todo later though so this will probably help in the
> > future as well.
> Yeah, I'd probably resort to fsync off in that circumstance too
> especially if syn commit off didn't help that much.
How should syn commit help with creating databases?
The problem with 8.4 and creating databases is that the number of files
increased hugely because of the introduction of relation forks.
It probably wouldnt be that hard to copy all files first, then reopen and fsync
them. Actually that should be a patch doable in an hour or two.
Andres
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-12 20:36 Michael Clemmons <glassresistor@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 22+ messages in thread
From: Michael Clemmons @ 2009-12-12 20:36 UTC (permalink / raw)
To: Hannu Krosing <hannu@2ndquadrant.com>; +Cc: Andres Freund <andres@anarazel.de>; pgsql-performance@postgresql.org, Scott Marlowe <scott.marlowe@gmail.com>
If ppl think its worth it I'll create a ticket
On Sat, Dec 12, 2009 at 6:09 AM, Hannu Krosing <hannu@2ndquadrant.com>wrote:
> On Sat, 2009-12-12 at 01:19 +0100, Andres Freund wrote:
> > Hi,
> >
> > On Saturday 12 December 2009 00:59:13 Scott Marlowe wrote:
> > > On Fri, Dec 11, 2009 at 3:52 PM, Michael Clemmons
> > > > Createdb takes
> > > > 12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow
> for
> > > > running 200unittests.
> > > > Fsync got it to .2secs or so which is blazing but
> > > > also the speed I expected being used to 8.3 and xfs. This dev box is
> my
> > > > laptop and the data is litterally unimportant and doesn't exist
> longer
> > > > than 20sec but Im all about good practices. Will definately try
> > > > synchronous commit tonight once Im done working for the day. I've
> got
> > > > some massive copying todo later though so this will probably help in
> the
> > > > future as well.
> > > Yeah, I'd probably resort to fsync off in that circumstance too
> > > especially if syn commit off didn't help that much.
> >
> > How should syn commit help with creating databases?
>
> It does not help here. Tested ;)
>
> > The problem with 8.4 and creating databases is that the number of files
> > increased hugely because of the introduction of relation forks.
>
> Plus the fact that fsync on ext4 is really slow. some info here:
>
> http://ldn.linuxfoundation.org/article/filesystems-data-preservation-fsync-and-benchmarks-pt-3
>
> > It probably wouldnt be that hard to copy all files first, then reopen and
> fsync
> > them. Actually that should be a patch doable in an hour or two.
>
> Probably something worth doing, as it will speed this up on all
> filesystems, and doubly so on ext4 and xfs.
>
> --
> Hannu Krosing http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
> Services, Consulting and Training
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-12 20:38 Andres Freund <andres@anarazel.de>
parent: Michael Clemmons <glassresistor@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Andres Freund @ 2009-12-12 20:38 UTC (permalink / raw)
To: pgsql-performance@postgresql.org; +Cc: Michael Clemmons <glassresistor@gmail.com>; Hannu Krosing <hannu@2ndquadrant.com>; Scott Marlowe <scott.marlowe@gmail.com>
On Saturday 12 December 2009 21:36:27 Michael Clemmons wrote:
> If ppl think its worth it I'll create a ticket
Thanks, no need. I will post a patch tomorrow or so.
Andres
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-13 03:56 Robert Haas <robertmhaas@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Robert Haas @ 2009-12-13 03:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Scott Mead <scott.lists@enterprisedb.com>; Nikolas Everett <nik9000@gmail.com>; jd <jd@commandprompt.com>; Andres Freund <andres@anarazel.de>; pgsql-performance <pgsql-performance@postgresql.org>; Michael Clemmons <glassresistor@gmail.com>
On Fri, Dec 11, 2009 at 5:12 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Dec 11, 2009 at 2:59 PM, Scott Mead
> <scott.lists@enterprisedb.com> wrote:
>> On Fri, Dec 11, 2009 at 4:39 PM, Nikolas Everett <nik9000@gmail.com> wrote:
>>>
>>>
>>>
>>> Fair enough. I'm of the opinion that developers need to have their unit
>>> tests run fast. If they aren't fast then your just not going to test as
>>> much as you should. If your unit tests *have* to createdb then you have to
>>> do whatever you have to do to get it fast. It'd probably be better if unit
>>> tests don't create databases or alter tables at all though.
>>>
>>> Regardless of what is going on on your dev box you really should leave
>>> fsync on on your continuous integration, integration test, and QA machines.
>>> They're what your really modeling your production on anyway.
>>
>>
>> The other common issue is that developers running with something like
>> 'fsync=off' means that they have completely unrealistic expectations of the
>> performance surrounding something. If your developers see that when fsync
>> is on, createdb takes x seconds vs. when it's off, then they'll know that
>> basing their entire process on that probably isn't a good idea. When
>> developers think something is lightning, they tend to base lots of stuff on
>> it, whether it's production ready or not.
>
> Yeah, it's a huge mistake to give development super fast servers to
> test on. Keep in mind production may need to handle 10k requests a
> minute / second whatever. Developers cannot generate that kind of
> load by just pointing and clicking. Our main production is on a
> cluster of 8 and 12 core machines with scads of memory and RAID-10
> arrays all over the place. Development gets a 4 core machine with 8G
> ram and an 8 drive RAID-6. It ain't slow, but it ain't really that
> fast either.
My development box at work is an 1.8 Ghz Celeron with 256K of CPU
cache, 1 GB of memory, and a single IDE drive... I don't have too
many slow queries in there.
...Robert
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: 8.4.1 ubuntu karmic slow createdb
@ 2009-12-28 23:57 Thomas Kellerer <spam_eater@gmx.net>
parent: Michael Clemmons <glassresistor@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Thomas Kellerer @ 2009-12-28 23:57 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
Michael Clemmons wrote on 11.12.2009 23:52:
> Thanks all this has been a good help.
> I don't have control(or easy control) over unit tests creating/deleting
> databases since Im using the django framework for this job. Createdb
> takes 12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow
> for running 200unittests.
I wonder if you could simply create one database, and then a new schema for each of the tests.
After creating the schema you could alter the search_path for the "unit test user" and it would look like a completely new database.
Thomas
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2009-12-28 23:57 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-10 20:41 8.4.1 ubuntu karmic slow createdb Michael Clemmons <glassresistor@gmail.com>
2009-12-10 21:56 ` Andres Freund <andres@anarazel.de>
2009-12-10 22:01 ` Michael Clemmons <glassresistor@gmail.com>
2009-12-10 22:09 ` Andres Freund <andres@anarazel.de>
2009-12-11 01:38 ` Nikolas Everett <nik9000@gmail.com>
2009-12-11 17:58 ` Joshua D. Drake <jd@commandprompt.com>
2009-12-11 20:43 ` Nikolas Everett <nik9000@gmail.com>
2009-12-11 20:50 ` Joshua D. Drake <jd@commandprompt.com>
2009-12-11 21:39 ` Nikolas Everett <nik9000@gmail.com>
2009-12-11 21:57 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-11 22:12 ` Scott Carey <scott@richrelevance.com>
2009-12-11 22:19 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-11 21:59 ` Scott Mead <scott.lists@enterprisedb.com>
2009-12-11 22:12 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-13 03:56 ` Robert Haas <robertmhaas@gmail.com>
2009-12-11 22:39 ` Greg Smith <greg@2ndquadrant.com>
2009-12-11 22:52 ` Michael Clemmons <glassresistor@gmail.com>
2009-12-11 23:59 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-12-12 00:19 ` Andres Freund <andres@anarazel.de>
2009-12-12 20:36 ` Michael Clemmons <glassresistor@gmail.com>
2009-12-12 20:38 ` Andres Freund <andres@anarazel.de>
2009-12-28 23:57 ` Thomas Kellerer <spam_eater@gmx.net>
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