Horton

Thread

  1. Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-24T03:48:13Z

    My situation is this. We have a semi-production server where we 
pre-process data and then upload the finished data to our production 
servers. We need the fastest possible write performance. Having the DB 
go corrupt due to power loss/OS crash is acceptable because we can 
always restore from last night and re-run everything that was done since 
then.

I already have fsync off. Short of buying more hardware -- which I will 
probably do anyways once I figure out whether I need more CPU, memory or 
disk -- what else can I do to max out the speed? Operation mix is about 
50% select, 40% insert, 10% update.

  2. Re: Maximum Possible Insert Performance?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-24T04:21:33Z

    William Yu <wyu@talisys.com> writes:
> [ we don't care about data integrity ]
> I already have fsync off. Short of buying more hardware -- which I will 
> probably do anyways once I figure out whether I need more CPU, memory or 
> disk -- what else can I do to max out the speed? Operation mix is about 
> 50% select, 40% insert, 10% update.

Batch operations so you commit more than one insert per transaction.
(With fsync off, this isn't such a killer consideration as it would be
with fsync on, but the per-transaction overhead is still nontrivial.)

Get rid of as many integrity constraints as you feel can reasonably be
postponed to the final upload.  FK checks are particularly painful.

Eliminate indexes where possible.

Also (I hate to say this, but...) you should consider using Some Other
Database.  "I don't care about data integrity, only speed" sounds like
a good fit to MySQL ...

			regards, tom lane

  3. Re: Maximum Possible Insert Performance?

    Josh Berkus <josh@agliodbs.com> — 2003-11-24T04:29:04Z

    William,

> I already have fsync off. Short of buying more hardware -- which I will
> probably do anyways once I figure out whether I need more CPU, memory or
> disk -- what else can I do to max out the speed? Operation mix is about
> 50% select, 40% insert, 10% update.

Disk.   Multi-channel RAID is where it's at, and/or RAID with a great write 
cache enabled.  For really fast updates, I'd suggest 6-disk or even 8-disk 
RAID 1+0.

As soon as you have gobs of extra disk space, jack your checkpoint_buffers way 
up, like a couple of gigs.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  4. Re: Maximum Possible Insert Performance?

    Joe Conway <mail@joeconway.com> — 2003-11-24T04:40:15Z

    William Yu wrote:
> My situation is this. We have a semi-production server where we 
> pre-process data and then upload the finished data to our production 
> servers. We need the fastest possible write performance. Having the DB 
> go corrupt due to power loss/OS crash is acceptable because we can 
> always restore from last night and re-run everything that was done since 
> then.

If you can, use COPY -- it is far faster than INSERT.

See:
http://www.postgresql.org/docs/current/static/sql-copy.html

HTH,

Joe

  5. Re: Maximum Possible Insert Performance?

    Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-11-24T05:50:36Z

    William Yu wrote:

> My situation is this. We have a semi-production server where we 
> pre-process data and then upload the finished data to our production 
> servers. We need the fastest possible write performance. Having the DB 
> go corrupt due to power loss/OS crash is acceptable because we can 
> always restore from last night and re-run everything that was done since 
> then.
> 
> I already have fsync off. Short of buying more hardware -- which I will 
> probably do anyways once I figure out whether I need more CPU, memory or 
> disk -- what else can I do to max out the speed? Operation mix is about 
> 50% select, 40% insert, 10% update.

Mount WAL on RAM disk. WAL is most often hit area for heavy updates/inserts. If 
you spped that up, things should be pretty faster.

A non-tried advice though. Given that you can afford a crash, I would say it is 
worth a try..

  Shridhar

  6. Re: Maximum Possible Insert Performance?

    PT <wmoran@potentialtech.com> — 2003-11-24T13:38:31Z

    William Yu wrote:
> My situation is this. We have a semi-production server where we 
> pre-process data and then upload the finished data to our production 
> servers. We need the fastest possible write performance. Having the DB 
> go corrupt due to power loss/OS crash is acceptable because we can 
> always restore from last night and re-run everything that was done since 
> then.
> 
> I already have fsync off. Short of buying more hardware -- which I will 
> probably do anyways once I figure out whether I need more CPU, memory or 
> disk -- what else can I do to max out the speed? Operation mix is about 
> 50% select, 40% insert, 10% update.

In line with what Tom Lane said, you may want to look at the various
memory databases available (I'm not familiar with any one to recommend,
though)  If you can fit the whole database in RAM, that would work
great, if not, you may be able to split the DB up and put the most
used tables just in the memory database.

I have also seen a number tutorials on how to put a database on a
RAM disk.  This helps, but it's still not as fast as a database server
that's designed to keep all its data in RAM.

-- 
Bill Moran
Potential Technologies
http://www.potentialtech.com

  7. Re: Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-24T17:19:20Z

    This is an intriguing thought which leads me to think about a similar 
solution for even a production server and that's a solid state drive for 
just the WAL. What's the max disk space the WAL would ever take up? 
There's quite a few 512MB/1GB/2GB solid state drives available now in 
the ~$200-$500 range and if you never hit those limits...

When my current job batch is done, I'll save a copy of the dir and give 
the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local 
store and run that through the hooper.


Shridhar Daithankar wrote:
> 
> Mount WAL on RAM disk. WAL is most often hit area for heavy 
> updates/inserts. If you spped that up, things should be pretty faster.

  8. Re: Maximum Possible Insert Performance?

    Josh Berkus <josh@agliodbs.com> — 2003-11-24T17:25:58Z

    William,

> When my current job batch is done, I'll save a copy of the dir and give
> the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local
> store and run that through the hooper.

We'll be interested in the results.   The Sandisk won't be much of a 
performance test; last I checked, their access speed was about 1/2 that of a 
fast SCSI drive.   But it could be a feasability test for the more expensive 
RAMdrive approach.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  9. Re: Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-24T17:45:22Z

    Josh Berkus wrote:
> William,
> 
> 
>>When my current job batch is done, I'll save a copy of the dir and give
>>the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local
>>store and run that through the hooper.
> 
> 
> We'll be interested in the results.   The Sandisk won't be much of a 
> performance test; last I checked, their access speed was about 1/2 that of a 
> fast SCSI drive.   But it could be a feasability test for the more expensive 
> RAMdrive approach.
> 


The SanDisks do seem a bit pokey at 16MBps. On the otherhand, you could 
get 4 of these suckers, put them in a mega-RAID-0 stripe for 64MBps. You 
shouldn't need to do mirroring with a solid state drive.

Time to Google up some more solid state drive vendors.

  10. Re: Maximum Possible Insert Performance?

    Josh Berkus <josh@agliodbs.com> — 2003-11-24T18:04:37Z

    William,

> The SanDisks do seem a bit pokey at 16MBps. On the otherhand, you could
> get 4 of these suckers, put them in a mega-RAID-0 stripe for 64MBps. You
> shouldn't need to do mirroring with a solid state drive.

I wouldn't count on RAID0 improving the speed of SANDisk's much.  How are you 
connecting to them?  USB?   USB doesn't support fast parallel data access.

Now, if it turns out that 256MB ramdisks are less than 1/5 the cost of 1GB 
ramdisks, then that's worth considering.

You're right, though, mirroring a solid state drive is pretty pointless; if 
power fails, both mirrors are dead.  

As I said before, though, we're all very interested in this test.  Using a 
ramdisk for WAL has been discussed on this list numerous times but not 
attempted by anyone who published their results.

All that aside, though, I think you should also experiment with the Background 
Writer patch recently discussed on Hackers, as it may give you a performance 
boost as well.
-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  11. Re: Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-24T18:23:36Z

    Josh Berkus wrote:
> William,
> 
> 
>>The SanDisks do seem a bit pokey at 16MBps. On the otherhand, you could
>>get 4 of these suckers, put them in a mega-RAID-0 stripe for 64MBps. You
>>shouldn't need to do mirroring with a solid state drive.
> 
> 
> I wouldn't count on RAID0 improving the speed of SANDisk's much.  How are you 
> connecting to them?  USB?   USB doesn't support fast parallel data access.

You can get ATA SanDisks up to 2GB. Another vendor I checked out -- 
BitMicro -- has solid state drives for SATA, SCSI and FiberChannel. I'd 
definitely would not use USB SSDs -- USB performance would be so pokey 
to be useless.

> Now, if it turns out that 256MB ramdisks are less than 1/5 the cost of 1GB 
> ramdisks, then that's worth considering.

Looks like they're linear with size. SanDisk Flashdrive 1GB is about 
$1000 while 256MB is $250.

> You're right, though, mirroring a solid state drive is pretty pointless; if 
> power fails, both mirrors are dead.  

Actually no. Solid state memory is non-volatile. They retain data even 
without power.

  12. Re: Maximum Possible Insert Performance?

    Greg Stark <gsstark@mit.edu> — 2003-11-25T01:16:27Z

    William Yu <wyu@talisys.com> writes:

> > You're right, though, mirroring a solid state drive is pretty pointless; if
> > power fails, both mirrors are dead.
> 
> Actually no. Solid state memory is non-volatile. They retain data even without
> power.

Note that flash ram only has a finite number of write cycles before it fails.

On the other hand that might not be so bad for WAL which writes sequentially,
you can easily calculate how close you are to the maximum. For things like
heap storage or swap it's awful as you can get hot spots that get written to
thousands of times before the rest of the space is used.

-- 
greg

  13. Re: Maximum Possible Insert Performance?

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-11-25T06:06:50Z

    William Yu wrote:

> This is an intriguing thought which leads me to think about a similar 
> solution for even a production server and that's a solid state drive for 
> just the WAL. What's the max disk space the WAL would ever take up? 
> There's quite a few 512MB/1GB/2GB solid state drives available now in 
> the ~$200-$500 range and if you never hit those limits...

Maximum number of WAL segments at any time in 2*(number of checkpoint 
segments)+1 IIRC.

So if you have 3 checkpoint segments, you can not have more than 7 WAL segments 
at any time. Give or take 1.

Correct me if I am wrong..

  Shridhar

  14. Re: Maximum Possible Insert Performance?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-25T15:47:37Z

    Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> William Yu wrote:
>> This is an intriguing thought which leads me to think about a similar 
>> solution for even a production server and that's a solid state drive for 
>> just the WAL. What's the max disk space the WAL would ever take up? 

> Maximum number of WAL segments at any time in 2*(number of checkpoint 
> segments)+1 IIRC.
> So if you have 3 checkpoint segments, you can not have more than 7 WAL
> segments at any time. Give or take 1.

I don't believe that's a *hard* limit.  The system tries to schedule
checkpoints often enough to prevent WAL from getting bigger than that,
but if you had a sufficiently big spike in update activity, it's at
least theoretically possible that more than checkpoint_segments segments
could be filled before the concurrently running checkpoint finishes and
releases some old segments.

The odds of this being a real problem are small, especially if you don't
try to fit on an undersized SSD by reducing checkpoint_segments.  I'd
think that a 512Mb SSD would be plenty of space for ordinary update load
levels ...

			regards, tom lane

  15. Re: Maximum Possible Insert Performance?

    Suchandra Thapa <ssthapa@netzero.com> — 2003-11-25T18:59:54Z

    On Mon, 2003-11-24 at 19:16, Greg Stark wrote:
> William Yu <wyu@talisys.com> writes:
> 
> > > You're right, though, mirroring a solid state drive is pretty pointless; if
> > > power fails, both mirrors are dead.
> > 
> > Actually no. Solid state memory is non-volatile. They retain data even without
> > power.
> 
> Note that flash ram only has a finite number of write cycles before it fails.
> 
> On the other hand that might not be so bad for WAL which writes sequentially,
> you can easily calculate how close you are to the maximum. For things like
> heap storage or swap it's awful as you can get hot spots that get written to
> thousands of times before the rest of the space is used.

I could be wrong, but I was under the impression that most of the newer
flash disks tended to spread writes out over the drive so that hotspots
are minimized.  

-- 
Suchandra Thapa <ssthapa@netzero.com>

  16. Re: Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-26T16:44:59Z

    Josh Berkus wrote:
> William,
> 
> 
>>When my current job batch is done, I'll save a copy of the dir and give
>>the WAL on ramdrive a test. And perhaps even buy a Sandisk at the local
>>store and run that through the hooper.
> 
> 
> We'll be interested in the results.   The Sandisk won't be much of a 
> performance test; last I checked, their access speed was about 1/2 that of a 
> fast SCSI drive.   But it could be a feasability test for the more expensive 
> RAMdrive approach.

Some initial numbers. I simulated a CPU increase by underclocking the 
processors. Most of the time, performance does not scale linearly with 
clock speed but since I also underclocked the FSB and memory bandwidth 
with the CPU, it's nearly an exact match.

1.15GHz    6.14
1.53GHz    6.97 +33% CPU = +13.5% performance

I then simulated adding a heapload of extra memory by running my job a 
second time. Unfortunately, to keep my 25GB DB mostly cached in memory, 
the word heapload is too accurate.

Run 1      6.97
Run 2      7.99 +14%

I popped in an extra IDE hard drive to store the WAL files and that 
boosted the numbers by a little. From looking at iostat, the ratio 
looked like 300K/s WAL for 1MB/s data.

WAL+Data on same disk    6.97
WAL+Data separated       7.26 +4%

I then tried to put the WAL directory onto a ramdisk. I turned off 
swapping, created a tmpfs mount point and copied the pg_xlog directory 
over. Everything looked fine as far as I could tell but Postgres just 
panic'd with a "file permissions" error. Anybody have thoughts to why 
tmpfs would not work?

  17. Re: Maximum Possible Insert Performance?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-26T17:55:56Z

    William Yu <wyu@talisys.com> writes:
> I then tried to put the WAL directory onto a ramdisk. I turned off 
> swapping, created a tmpfs mount point and copied the pg_xlog directory 
> over. Everything looked fine as far as I could tell but Postgres just 
> panic'd with a "file permissions" error. Anybody have thoughts to why 
> tmpfs would not work?

I'd say you got the file or directory ownership or permissions wrong.

			regards, tom lane

  18. Re: Maximum Possible Insert Performance?

    William Yu <wyu@talisys.com> — 2003-11-26T18:03:47Z

    Tom Lane wrote:
> William Yu <wyu@talisys.com> writes:
> 
>>I then tried to put the WAL directory onto a ramdisk. I turned off 
>>swapping, created a tmpfs mount point and copied the pg_xlog directory 
>>over. Everything looked fine as far as I could tell but Postgres just 
>>panic'd with a "file permissions" error. Anybody have thoughts to why 
>>tmpfs would not work?
> 
> 
> I'd say you got the file or directory ownership or permissions wrong.

I did a mv instead of a cp which duplicates ownership & permissions exactly.

  19. Re: Maximum Possible Insert Performance?

    Dror Matalon <dror@zapatec.com> — 2003-11-26T18:54:21Z

    But the permissions of the base ramdisk might be wrong. I'd su to the
user that you run postgres as (probably postgres), and make sure that
you can go to the directory where the log and the database files are and
make sure you can see the files.

On Wed, Nov 26, 2003 at 10:03:47AM -0800, William Yu wrote:
> Tom Lane wrote:
> >William Yu <wyu@talisys.com> writes:
> >
> >>I then tried to put the WAL directory onto a ramdisk. I turned off 
> >>swapping, created a tmpfs mount point and copied the pg_xlog directory 
> >>over. Everything looked fine as far as I could tell but Postgres just 
> >>panic'd with a "file permissions" error. Anybody have thoughts to why 
> >>tmpfs would not work?
> >
> >
> >I'd say you got the file or directory ownership or permissions wrong.
> 
> I did a mv instead of a cp which duplicates ownership & permissions exactly.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Dror Matalon
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.fastbuzz.com
http://www.zapatec.com