Re: [PERFORM] file system and raid performance
M. Edward (Ed) Borasky wrote: Ah, but shouldn't a PostgreSQL (or any other database, for that matter) have its own set of filesystems tuned to the application's I/O patterns? Sure, there are some people who need to have all of their eggs in one basket because they can't afford multiple baskets. For them, maybe the OS defaults are the right choice. But if you're building a database-specific server, you can optimize the I/O for that. I used to run IBM's DB2 database management system. It can use a normal Linux file system (e.g., ext2 or ext3), but it prefers to run a partition (or more, preferably more than one) itself in raw mode. This eliminates core-to-core copies of in put and output, organizing the disk space as it prefers, allows multiple writer processes (typically one per disk drive), and multiple reader processes (also, typically one per drive), and potentially increasing the concurrency of reading, writing, and processing. My dbms needs are extremely modest (only one database, usually only one user, all on one machine), so I saw only a little benefit to using DB2, and there were administrative problems. I use Red Hat Enterprise Linux, and the latest version of that (RHEL 5) does not offer raw file systems anymore, but provides the same thing by other means. Trouble is, I would have to buy the latest version of DB2 to be compatible with my version of Linux. Instead, I just converted everything to postgreSQL instead, it it works very well. When I started with this in 1986, I first tried Microsoft Access, but could not get it to accept the database description I was using. So I switched to Linux (for many reasons -- that was just one of them) and tried postgreSQL. At the time, it was useless. One version would not do views (it accepted the construct, IIRC, but they did not work), and the other version would do views, but would not do something else (I forget what), so I got Informix that worked pretty well with Red Hat Linux 5.0. When I upgraded to RHL 5.2 or 6.0 (I forget which), Informix would not work (could not even install it), I could get no support from them, so that is why I went to DB2. When I got tired of trying to keep DB2 working with RHEL 5, I switched to postgreSQL, and the dbms itself worked right out of the box. I had to diddle my programs very slightly (I used embedded SQL), but there were superficial changes here and there. The advantage of using one of the OS's file systems (I use ext2 for the dbms and ext3 for everything else) are that the dbms developers have to be ready for only about one file system. That is a really big advantage, I imagine. I also have atime turned off. The main database is on 4 small hard drive (about 18 GBytes each) each of which has just one partition taking the entire drive. They are all on a single SCSI controller that also has my backup tape drive on it. The machine has two other hard drives (around 80 GBytes each) on another SCSI controller and nothing else on that controller. One of the drives has a partition on it where mainly the WAL is placed, and another with little stuff. Those two drives have other partitions for the Linus stuff, /tmp, /var, and /home as the main partitions on them, but the one with the WAL on it is just about unused (contains /usr/source and stuff like that) when postgres is running. That is good enough for me. If I were in a serious production environment, I would take everything except the dbms off that machine and run it on another one. I cannot make any speed comparisons between postgreSQL and DB2, because the machine I ran DB2 on has two 550 MHz processors and 512 Megabytes RAM running RHL 7.3, and the new machine for postgres has two 3.06 GBYte hyperthreaded Xeon processors and 8 GBytes RAM running RHEL 5, so a comparison would be kind of meaningless. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 06:50:01 up 4 days, 17:08, 4 users, load average: 4.18, 4.15, 4.07 -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] file system and raid performance
On Sun, Dec 7, 2008 at 10:59 PM, M. Edward (Ed) Borasky <[EMAIL PROTECTED]> wrote: > Ah, but shouldn't a PostgreSQL (or any other database, for that matter) > have its own set of filesystems tuned to the application's I/O patterns? > Sure, there are some people who need to have all of their eggs in one > basket because they can't afford multiple baskets. For them, maybe the > OS defaults are the right choice. But if you're building a > database-specific server, you can optimize the I/O for that. It's really about a cost / benefits analysis. 20 years ago file systems were slow and buggy and a database could, with little work, outperform them. Nowadays, not so much. I'm guessing that the extra cost and effort of maintaining a file system for pgsql outweighs any real gain you're likely to see performance wise. But I'm sure that if you implemented one that outran XFS / ZFS / ext3 et. al. people would want to hear about it. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] file system and raid performance
On Mon, 8 Dec 2008, Scott Marlowe wrote: On Sun, Dec 7, 2008 at 10:59 PM, M. Edward (Ed) Borasky <[EMAIL PROTECTED]> wrote: Ah, but shouldn't a PostgreSQL (or any other database, for that matter) have its own set of filesystems tuned to the application's I/O patterns? Sure, there are some people who need to have all of their eggs in one basket because they can't afford multiple baskets. For them, maybe the OS defaults are the right choice. But if you're building a database-specific server, you can optimize the I/O for that. It's really about a cost / benefits analysis. 20 years ago file systems were slow and buggy and a database could, with little work, outperform them. Nowadays, not so much. I'm guessing that the extra cost and effort of maintaining a file system for pgsql outweighs any real gain you're likely to see performance wise. especially with the need to support the new 'filesystem' on many different OS types. David Lang But I'm sure that if you implemented one that outran XFS / ZFS / ext3 et. al. people would want to hear about it. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need help with 8.4 Performance Testing
On Sun, 7 Dec 2008, Scott Marlowe wrote: When I last used pgbench I wanted to test it with an extremely large dataset, but it maxes out at -s 4xxx or so, and that's only in the 40Gigabyte range. Is the limit raised for the pgbench included in contrib in 8.4? I'm guessing it's an arbitrary limit. There's no artificial limit, just ones that result from things like integer overflow. I don't think has been an issue so far because pgbench becomes seek limited and stops producing interesting results once the database exceeds the sum of all available caching, which means you'd need more than 32GB of RAM in the system running pgbench before this is an issue. Which happens to be the largest size system I've ever ran it on... I'd expect this statement around line 1060 of pgbench.c to overflow first: for (i = 0; i < naccounts * scale; i++) Where i is an integer, naccounts=100,000 , and scale is what you input. That will overflow a signed 32-bit integer at a scale of 2147. If you had tests that failed at twice that, I wonder if you were actually executing against a weird data set for scales of (2148..4294). It's not completely trivial to fix (I just tried), the whole loop needs to be restructured to run against scale and 100,000 separately while keeping the current progress report intact. I'll take a shot at fixing it correctly, this is a bug that should be corrected before 8.4 goes out. I guarantee that version will be used on systems with 64GB of RAM where this matters. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need help with 8.4 Performance Testing
On Mon, Dec 8, 2008 at 1:15 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Sun, 7 Dec 2008, Scott Marlowe wrote: > >> When I last used pgbench I wanted to test it with an extremely large >> dataset, but it maxes out at -s 4xxx or so, and that's only in the >> 40Gigabyte range. Is the limit raised for the pgbench included in >> contrib in 8.4? I'm guessing it's an arbitrary limit. > > There's no artificial limit, just ones that result from things like integer > overflow. I don't think has been an issue so far because pgbench becomes > seek limited and stops producing interesting results once the database > exceeds the sum of all available caching, which means you'd need more than > 32GB of RAM in the system running pgbench before this is an issue. Which > happens to be the largest size system I've ever ran it on... Well, I have 32 Gig of ram and wanted to test it against a database that was at least twice as big as memory. I'm not sure why you'd consider the results uninteresting though, I'd think knowing how the db will perform with a very large transactional store that is twice or more the size of memory would be when it starts getting interesting. -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need help with 8.4 Performance Testing
On Mon, 8 Dec 2008, Scott Marlowe wrote: Well, I have 32 Gig of ram and wanted to test it against a database that was at least twice as big as memory. I'm not sure why you'd consider the results uninteresting though, I'd think knowing how the db will perform with a very large transactional store that is twice or more the size of memory would be when it starts getting interesting. If you refer back to the picture associated with the link Josh suggested: http://www.westnet.com/~gsmith/gregsmith/content/postgresql/scaling.png You'll see that pgbench results hit a big drop once you clear the amount of memory being used to cache the accounts table. This curve isn't unique to what I did; I've seen the same basic shape traced out by multiple other testers on different hardware, independent of me. It just expands to the right based on the amount of RAM available. All I was trying to suggest was that even if you've got 32GB of RAM, you may already be into the more flat right section of that curve even with a 40GB database. That was a little system with 1GB of RAM+256MB of disk cache, and it was already toast at 750MB of database. Once you've gotten a database big enough to reach that point, results degrade to something related to database seeks/second rather than anything else, and further increases don't give you that much more info. This is why I'm not sure if the current limit really matters with 32GB of RAM, but it sure will be important if you want any sort of useful pgbench results at 64GB. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need help with 8.4 Performance Testing
On Mon, Dec 8, 2008 at 5:52 PM, Greg Smith <[EMAIL PROTECTED]> wrote: > On Mon, 8 Dec 2008, Scott Marlowe wrote: > >> Well, I have 32 Gig of ram and wanted to test it against a database >> that was at least twice as big as memory. I'm not sure why you'd >> consider the results uninteresting though, I'd think knowing how the >> db will perform with a very large transactional store that is twice or >> more the size of memory would be when it starts getting interesting. > > If you refer back to the picture associated with the link Josh suggested: > > http://www.westnet.com/~gsmith/gregsmith/content/postgresql/scaling.png > > You'll see that pgbench results hit a big drop once you clear the amount of > memory being used to cache the accounts table. This curve isn't unique to > what I did; I've seen the same basic shape traced out by multiple other > testers on different hardware, independent of me. It just expands to the > right based on the amount of RAM available. > > All I was trying to suggest was that even if you've got 32GB of RAM, you may > already be into the more flat right section of that curve even with a 40GB > database. That was a little system with 1GB of RAM+256MB of disk cache, and > it was already toast at 750MB of database. Once you've gotten a database > big enough to reach that point, results degrade to something related to > database seeks/second rather than anything else, and further increases don't > give you that much more info. This is why I'm not sure if the current limit > really matters with 32GB of RAM, but it sure will be important if you want > any sort of useful pgbench results at 64GB. I wonder if shared_buffers has any effect on how far you can go before you hit the 'tipping point'. merlin -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Need help with 8.4 Performance Testing
On Mon, 8 Dec 2008, Merlin Moncure wrote: I wonder if shared_buffers has any effect on how far you can go before you hit the 'tipping point'. If your operating system has any reasonable caching itself, not so much at first. As long as the index on the account table fits in shared_buffers, even the basic sort of caching logic an OS uses is perfectly functional for swapping the individual pages of the account table in and out, the main limiting factor on pgbench performance. There is a further out tipping point I've theorized about but not really explored: the point where even the database indexes stop fitting in memory usefully. As you get closer to that, I'd expect that the clock sweep algorithm used by shared_buffers should make it a bit more likely that those important blocks would hang around usefully if you put them there, rather than giving most of the memory to the OS to manage. Since the data is about 7.5X as large as the indexes, that point is way further out than the basic bottlenecks. And if you graph pgbench results on a scale that usefully shows the results for in-memory TPS scores, you can barely see that part of the chart a well. One day I may get to mapping that out better, and if I do it will be interesting to see if the balance of shared_buffers to OS cache works the way I expect. I was waiting until I finished the pgtune program for that, that's building some of the guts I wanted to make it easier to tweak postgresql.conf settings programmatically in between pgbench runs. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
