8.4.1 ubuntu karmic slow createdb
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
Import Notes
Reply to msg id not found: 4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.comReference msg id not found: 4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.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
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:
Show quoted text
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 takes4s
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
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
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:
Show quoted text
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 whetherCREATE 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
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
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:
Show quoted text
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
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
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.
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.
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
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.
Show quoted text
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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.
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.
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
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:
Show quoted text
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
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.
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
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:
Show quoted text
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 slowfor
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 ismy
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'vegot
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
Import Notes
Reply to msg id not found: 1260616155.2287.15.camel@huvostroReference msg id not found: 4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.com
