[PERFORM] DRBD and Postgres: how to improve the perfomance?
Hello.
We have made some performance tests with DRBD and Postgresql 8.2.3. We
have two identical servers in a cluster (Dell 2950) with a partition of
100 GB managed by DRBD: once we checked Postgres keeping his data folder
in a local partition, the second time we moved the data folder in the
shared partition. The two servers are connected point to point using a
cross cable to reduce their latency.
The partition is mounted with the option noatime in order to not update
the inode access time in case of read access.
We used pgbench for the testings, creating a dabase of about 3GB with a
scale of 200. After we perfomed 10 tests for each configuration,
simulating the usage of 100 clients with 500 transactions each.
DRBD configuration:
resource drbd0 {
protocol C;
incon-degr-cmd halt -f;
on db-node1 {
device /dev/drbd0;
disk /dev/sda2;
address10.0.0.201:7788;
meta-disk internal;
}
on db-node2 {
device/dev/drbd0;
disk /dev/sda2;
address10.0.0.202:7788;
meta-disk internal;
}
syncer {
rate 70K;
}
}
Pgbench
pgbench -i pgbench -s 200
pgbench -c 100 -t 500 pgbench
The results were that the TPS (transaction per second) with Postgres
running in the local partition is almost double than the one with the DRDB:
Postgres in shared DRBD partition: 60.863324 TPS
Postgres in local partition: 122.016138 TPS
Obviously, working with the database in DRBD, we had two writes instead
of only one but we are a bit disappointed about the low results. We
would like to know if there is any way to improve the performance in
order to have a 3/4 rate instead of the 1/2 one.
We would really appreciate it if you could give us some feedback.
Thank you in advance,
Maila Fatticcioni
--
__
Maila Fatticcioni
__
Mediterranean Broadband Infrastructure s.r.l.
ITALY
__
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] SAN vs Internal Disks
On Fri, Sep 07, 2007 at 12:33:41PM +0200, Tobias Brox wrote: Advantages: 1. Higher I/O (at least the salesman claims so) Benchmark it. It is extremely unlikely that you'll get I/O *as good as* DAS at a similar price point. 2. Easier to upgrade the disk capacity Is this an issue? You may find that you can simply get dramatically more space for the money with DAS and not have to worry about an upgrade. Also, you can use the postgres tablespace functionality to migrate data to a new partition fairly transparently. 3. Easy to set up warm standby functionality. (Then again, if the postgres server fails miserably, it's likely to be due to a disk crash). You may find that using db replication will gain you even more reliability for less money. Also, my boss states that all big enterprises uses SAN nowadays. Use SAN *for what*? Mike Stone ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
Maila Fatticcioni wrote:
Hello.
We have made some performance tests with DRBD and Postgresql 8.2.3. We
have two identical servers in a cluster (Dell 2950) with a partition of
100 GB managed by DRBD: once we checked Postgres keeping his data folder
in a local partition, the second time we moved the data folder in the
shared partition. The two servers are connected point to point using a
cross cable to reduce their latency.
The partition is mounted with the option noatime in order to not update
the inode access time in case of read access.
We used pgbench for the testings, creating a dabase of about 3GB with a
scale of 200. After we perfomed 10 tests for each configuration,
simulating the usage of 100 clients with 500 transactions each.
DRBD configuration:
resource drbd0 {
protocol C;
incon-degr-cmd halt -f;
on db-node1 {
device /dev/drbd0;
disk /dev/sda2;
address10.0.0.201:7788;
meta-disk internal;
}
on db-node2 {
device/dev/drbd0;
disk /dev/sda2;
address10.0.0.202:7788;
meta-disk internal;
}
syncer {
rate 70K;
}
}
Pgbench
pgbench -i pgbench -s 200
pgbench -c 100 -t 500 pgbench
The results were that the TPS (transaction per second) with Postgres
running in the local partition is almost double than the one with the DRDB:
Postgres in shared DRBD partition: 60.863324 TPS
Postgres in local partition: 122.016138 TPS
Obviously, working with the database in DRBD, we had two writes instead
of only one but we are a bit disappointed about the low results. We
would like to know if there is any way to improve the performance in
order to have a 3/4 rate instead of the 1/2 one.
You seem to be limited by the speed you can fsync the WAL to the DRBD
device. Using a RAID controller with a battery-backed up cache in both
servers should help, with and without DRBD. You might find that the
difference between local and shared partition just gets bigger, but you
should get better numbers.
In 8.3, you could turn synchronous_commit=off, if you can accept the
loss of recently committed transactions in case of a crash.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN vs Internal Disks
We're also considering to install postgres on SAN - that is, my boss is convinced this is the right way to go. Advantages: 1. Higher I/O (at least the salesman claims so) 2. Easier to upgrade the disk capacity 3. Easy to set up warm standby functionality. (Then again, if the postgres server fails miserably, it's likely to be due to a disk crash). Also, my boss states that all big enterprises uses SAN nowadays. Disadvantages: 1. Risky? One gets the impression that there are frequent problems with data integrity when reading some of the posts in this thread. 2. Expensive 3. Single point of failure ... but that you have either it's a SAN or a local disk, one will anyway need good backup systems (and eventually warm standby-servers running from physically separated disks). 4. More complex setup? 5. If there are several hosts with write permission towards the same disk, I can imagine the risks being higher for data integrity breakages. Particularly, I can imagine that if two postgres instances is started up towards the same disk (due to some sysadmin mistake), it could be disasterous. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On 9/7/07, Maila Fatticcioni [EMAIL PROTECTED] wrote: Obviously, working with the database in DRBD, we had two writes instead of only one but we are a bit disappointed about the low results. We would like to know if there is any way to improve the performance in order to have a 3/4 rate instead of the 1/2 one. Have you considered warm standby PITR? It achieves essentially the same thing with very little overhead on the master. The only downside relative to DRDB is you have to think about the small gap between WAL file rotations. From what I understand, there is some new stuff (check out skype skytools) that may help minimize this problem. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
On 9/7/07, Florian Weimer [EMAIL PROTECTED] wrote: * Scott Marlowe: And there's the issue that with windows / NTFS that when one process opens a file for read, it locks it for all other users. This means that things like virus scanners can cause odd, unpredictable failures of your database. I think most of them open the file in shared/backup mode. The only lock that is created by that guards deletion and renaming. It can still lead to obscure failures, but it's not a wholly-eclusive lock. Well, there've been a lot of issues with anti-virus and postgresql not getting along. I wonder if pgsql takes out a stronger lock, and when it can't get it then the failure happens. Not familiar enough with windows to do more than speculate. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] postgres memory management issues?
Richard Yen [EMAIL PROTECTED] writes: Here is a snippet of my log output (I can give more if necessary): Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of Memory: Kill process 11696 (postgres) score 1181671 and children. My understanding is that if any one postgres process's memory usage, plus the shared memory, exceeds the kernel limit of 4GB, then the kernel will kill the process off. Is this true? No. The OOM killer is not about individual process size. It's about getting the kernel out of the corner it's backed itself into when it's promised more memory for the total collection of processes than it can actually deliver. As already noted, fooling with the overcommit parameter might help, and migrating to a 64-bit kernel might help. (32-bit kernels can run out of space for lowmem long before all of your 16G is used up.) ObDigression: The reason the kernel would do such a silly-sounding thing as promise more memory than it has is that in a lot of cases pages are shared by more than one process --- in fact, immediately after a fork() the child process shares *all* pages of its parent --- and it would be really restrictive to insist on having sufficient RAM+swap for each process to have an independent copy of shared pages. The problem is that it's hard to guess whether currently-shared pages will need multiple copies in future. After a fork() the child's pages are supposed to be independent of the parent, so if either one scribbles on a shared page then the kernel has to instantiate separate copies at that moment (google for copy on write for more about this). The problem is that if there is not enough memory for another copy, there is no clean API for the kernel to return out of memory. It cannot just fail the write instruction, so the only recourse is to nuke some process or other to release memory. The whole thing makes considerable sense until you are trying to run critical applications, and then you just wanna turn it off. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] utilising multi-cpu/core machines?
Hi Thomas, PostgreSQL does scale up very well. But you have to keep in mind that this also depends on profile of the application you're on PostgreSQL. Insufficient memory and slow disk systems can interfere PostgreSQL. Another issue is contention if the server has more than 4 cpus. (Please check out discussions about context strom in this group.) Anyhow, I had create a benchmark for my company which shows the scale up of PostgreSQL 8.1.4. This benchmark does try to enforce contention because of the profile of our application. Clients/scale-up factor 1 1 2 1,78 3 2,47 4 3,12 5 3,62 6 4,23 7 4,35 8 4,79 9 5,05 10 5,17 Scale-up factor is relative to one client the number of completed queries in a time frame. (throughput) This test was done on a 16 core Intel-box (4-way Xeon E7340). The results of TPC-B benchmark are looking similar. Sven. Thomas Finneid schrieb: Hi I couldnt find any specifics on this subject in the documentation, so I thought I'd ask the group. how does pg utilise multi cpus/cores, i.e. does it use more than one core? and possibly, how, are there any documentation about this. thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Sven Geisler [EMAIL PROTECTED] Tel +49.30.921017.81 Fax .50 Senior Developer, AEC/communications GmbH Co. KG Berlin, Germany ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgres memory management issues?
Richard Yen [EMAIL PROTECTED] writes: My understanding is that if any one postgres process's memory usage, plus the shared memory, exceeds the kernel limit of 4GB, then the kernel will kill the process off. Is this true? If so, would postgres have some prevention mechanism that would keep a particular process from getting too big? (Maybe I'm being too idealistic, or I just simply don't understand how postgres works under the hood) I don't think you have an individual process going over 4G. I think what you have is 600 processes which in aggregate are using more memory than you have available. Do you really need 600 processes by the way? You could try lowering work_mem but actually your value seems fairly reasonable. Perhaps your kernel isn't actually able to use 16GB? What does cat /proc/meminfo say? What does it say when this is happening? You might also tweak /proc/sys/vm/overcommit_memory but I don't remember what the values are, you can search to find them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] postgres memory management issues?
I've recently run into problems with my kernel complaining that I ran out of memory, thus killing off postgres and bringing my app to a grinding halt. I'm on a 32-bit architecture with 16GB of RAM, under Gentoo Linux. Naturally, I have to set my shmmax to 2GB because the kernel can't support more (well, I could set it to 3GB, but I use 2GB for safety). Wouldn't it make sense to install an amd64 version with so much RAM? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] postgres memory management issues?
Richard Yen wrote: Hi All, I've recently run into problems with my kernel complaining that I ran out of memory, thus killing off postgres and bringing my app to a grinding halt. I'm on a 32-bit architecture with 16GB of RAM, under Gentoo Linux. Naturally, I have to set my shmmax to 2GB because the kernel can't support more (well, I could set it to 3GB, but I use 2GB for safety). Shared_buffers is 20 and max_connections is 600. OK, that's ~ 1.6GB shared-memory Here is a snippet of my log output (I can give more if necessary): Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of Memory: Kill process 11696 (postgres) score 1181671 and children. OK, you've run out of memory at some point. My understanding is that if any one postgres process's memory usage, plus the shared memory, exceeds the kernel limit of 4GB, then the kernel will kill the process off. Is this true? If so, would postgres have some prevention mechanism that would keep a particular process from getting too big? (Maybe I'm being too idealistic, or I just simply don't understand how postgres works under the hood) You've got max_connections of 600 and you think individual backends are using more than 2.4GB RAM each? Long before that you'll run out of actual RAM+Swap. If you actually had 600 backends you'd be able to allocate ~24MB to each. You'd actually want much less, to allow for disk-cache in the OS. The important information missing is: 1. How much memory is in use, and by what (vmstat/top output) 2. What memory settings do you have in your postgresql.conf (work_mem, maintenance_work_mem) 3. What was happening at the time (how many connections etc) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] postgres memory management issues?
Hi All, I've recently run into problems with my kernel complaining that I ran out of memory, thus killing off postgres and bringing my app to a grinding halt. I'm on a 32-bit architecture with 16GB of RAM, under Gentoo Linux. Naturally, I have to set my shmmax to 2GB because the kernel can't support more (well, I could set it to 3GB, but I use 2GB for safety). Shared_buffers is 20 and max_connections is 600. Here is a snippet of my log output (I can give more if necessary): Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of Memory: Kill process 11696 (postgres) score 1181671 and children. Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of Memory: Kill process 11696 (postgres) score 1181671 and children. Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of memory: Killed process 11704 (postgres). Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com Out of memory: Killed process 11704 (postgres). [...] Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com postgres[11696]: [6-1] 2007-09-05 18:38:57.626 PDT [user=,db= PID:11696 XID:]LOG: background writer process (PID 11704) was terminated by signal 9 Sep 5 18:38:57 tii-db2.oaktown.iparadigms.com postgres[11696]: [7-1] 2007-09-05 18:38:57.626 PDT [user=,db= PID:11696 XID:]LOG: terminating any other active server processes My understanding is that if any one postgres process's memory usage, plus the shared memory, exceeds the kernel limit of 4GB, then the kernel will kill the process off. Is this true? If so, would postgres have some prevention mechanism that would keep a particular process from getting too big? (Maybe I'm being too idealistic, or I just simply don't understand how postgres works under the hood) --Richard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN vs Internal Disks
I'm getting a san together to consolidate my disk space usage for my servers. It's iscsi based and I'll be pxe booting my servers from it. The idea is to keep spares on hand for one system (the san) and not have to worry about spares for each specific storage system on each server. This also makes growing filesystems and such pretty simple. Redundancy is also good since I'll have two iscsi switches plugged into two cisco ethernet switches and two different raid controllers on the jbod. I'll start plugging my servers into each switch for further redundancy. In the end I could loose disks, ethernet switches, cables, iscsi switches, raid controller, whatever, and it keeps on moving. That said, I'm not putting my postgres data on the san. The DB server will boot from the san and use it for is OS, but there are 6 15k SAS disks in it setup with raid-10 that will be used for the postgres data mount. The machine is a dell 2950 and uses an LSI raid card. The end result is a balance of cost, performance, and reliability. I'm using iscsi for the cost, reliability, and ease of use, but where I need performance I'm sticking to local disks. schu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
Scott, Well, there've been a lot of issues with anti-virus and postgresql not getting along. I wonder if pgsql takes out a stronger lock, and when it can't get it then the failure happens. Not familiar enough with windows to do more than speculate. without touching the file-concurrency issues caused by virus scanners: a LOT of the Postgres - VirusScanner problems on Windows were caused during the postgres spawns a new process and communicates with that process via ipstack Many Virus Scanners seam to have dealt with the TCP/IP stack in a not compatible manner... Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [PERFORM] postgres memory management issues?
On Thu, Sep 06, 2007 at 09:06:53AM -0700, Richard Yen wrote: My understanding is that if any one postgres process's memory usage, plus the shared memory, exceeds the kernel limit of 4GB, On a 32 bit system the per-process memory limit is a lot lower than 4G. If you want to use 16G effectively it's going to be a lot easier to simply use a 64bit system. That said, it's more likely that you've got a number of processes using an aggregate of more than 16G than that you're exceeding the limit per process. (Hitting the per-process limit should result in a memory allocation failure rather than an out of memory condition.) Mike Stone ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SAN vs Internal Disks
On Fri, Sep 07, 2007 at 12:26:23AM -0400, Greg Smith wrote: consider is this: your SAN starts having funky problems, and your database is down because of it. You call the vendor. They find out you're running CentOS instead of RHEL and say that's the cause of your problem (even though it probably isn't). How much will such a passing the buck problem cost your company? If it's a significant number, you'd be foolish to run CentOS instead of the real RHEL. Some SAN vendors can be very, very picky about what they will support, and for most business environments the RHEL subscription isn't so expensive that it's worth wandering into an area where your support situation is fuzzy just to save that money. Correct. Far more sensible to skip the expensive SAN solution, not worry about having to play games, and save *even more* money. SANs have their place, but postgres storage generally isn't it; you'll get more bang/buck with DAS and very likely better absolute performance as well. SANs make sense if you're doing a shared filesystem (don't even think about doing this with postgres), or if you're consolidating backups DR (which doesn't work especially well with databases). Mike Stone ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance on 8CPU's and 32GB of RAM
* Scott Marlowe: And there's the issue that with windows / NTFS that when one process opens a file for read, it locks it for all other users. This means that things like virus scanners can cause odd, unpredictable failures of your database. I think most of them open the file in shared/backup mode. The only lock that is created by that guards deletion and renaming. It can still lead to obscure failures, but it's not a wholly-eclusive lock. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] SAN vs Internal Disks
* Arjen van der Meijden: The disadvantage of using Areca or 3Ware is obviously the lack of support in A-brand servers and the lack of support for SAS-disks. Only recently Areca has stepped in the SAS-market, but I have no idea how easily those controllers are integrated in standard servers (they tend to be quite large, which will not fit in 2U and maybe not even in 3U or 4U-servers). Recent 3ware controllers are a bit on the hot side, too. We had to switch from two 12 port controllers to a single 24 port controller because of that (combined with an unlucky board layout: the two 8x PCIe connectors are next to each other). Unfortunately, read performance maxes out at about 8 disks in a RAID-10 configuration. Software RAID-0 across hardware RAID-1 is significantly faster (factor of 3 to 5 in low-level benchmarks). However, it seems that something in this stack does not enforce write barriers properly, so I don't think we will use this in production. RAID-6 doesn't perform well, either (especially for several processes reading different files sequentially). We'll probably split the 24 disks into a couple of RAID-10s, and distribute tables and indexes manually among the file systems. This is a bit disappointing, especially because the system is able to read at 800+ MB/s, as shown by the software-RAID-on-hardware-RAID configuration. I haven't seen 24-disk benchmarks with Areca controllers. A comparison might be interesting. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] SAN vs Internal Disks
We are currently running our database against on SAN share. It looks like this: 2 x RAID 10 (4 disk SATA 7200 each) Raid Group 0 contains the tables + indexes Raid Group 1 contains the log files + backups (pg_dump) Our database server connects to the san via iSCSI over Gig/E using jumbo frames. File system is XFS (noatime). I believe our raid controller is an ARECA. Whatever it is, it has the option of adding a battery to it but I have not yet been able to convince my boss that we need it. Maintenance is nice, we can easily mess around with the drive shares, expand and contract them, snapshot them, yadda yadda yadda. All things which we NEVER do to our database anyway. :) Performance, however, is a mixed bag. It handles concurrency very well. We have a number of shares (production shares, data shares, log file shares, backup shares, etc. etc.) spread across the two raid groups and it handles them with aplomb. Throughput, however, kinda sucks. I just can't get the kind of throughput to it I was hoping to get. When our memory cache is blown, the database can be downright painful for the next few minutes as everything gets paged back into the cache. I'd love to try a single 8 disk RAID 10 with battery wired up directly to our database, but given the size of our company and limited funds, it won't be feasible any time soon. Bryan On 9/7/07, Matthew Schumacher [EMAIL PROTECTED] wrote: I'm getting a san together to consolidate my disk space usage for my servers. It's iscsi based and I'll be pxe booting my servers from it. The idea is to keep spares on hand for one system (the san) and not have to worry about spares for each specific storage system on each server. This also makes growing filesystems and such pretty simple. Redundancy is also good since I'll have two iscsi switches plugged into two cisco ethernet switches and two different raid controllers on the jbod. I'll start plugging my servers into each switch for further redundancy. In the end I could loose disks, ethernet switches, cables, iscsi switches, raid controller, whatever, and it keeps on moving. That said, I'm not putting my postgres data on the san. The DB server will boot from the san and use it for is OS, but there are 6 15k SAS disks in it setup with raid-10 that will be used for the postgres data mount. The machine is a dell 2950 and uses an LSI raid card. The end result is a balance of cost, performance, and reliability. I'm using iscsi for the cost, reliability, and ease of use, but where I need performance I'm sticking to local disks. schu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] postgres memory management issues?
* Gregory Stark: You might also tweak /proc/sys/vm/overcommit_memory but I don't remember what the values are, you can search to find them. 2 is the interesting value, it turns off overcommit. However, if you're tight on memory, this will only increase your problems because the system fails sooner. The main difference is that it's much more deterministic: malloc fails in a predictable manner and this situation can be handled gracefully (at least by some processes); no processes are killed. We use this setting on all of our database server, just in case someone performs a huge SELECT locally, resulting in a a client process sucking up all available memory. With vm.overcommit_memory=2, memory allocation in the client process will fail. Without it, typically the postgres process feeding it is killed by the kernel. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SAN vs Internal Disks
On Friday 07 September 2007 10:56, Bryan Murphy [EMAIL PROTECTED] wrote: Our database server connects to the san via iSCSI over Gig/E using jumbo frames. File system is XFS (noatime). Throughput, however, kinda sucks. I just can't get the kind of throughput to it I was hoping to get. A single Gig/E couldn't even theoretically do better than 125MB/sec, so yeah I would expect throughput sucks pretty bad. -- A democracy is a sheep and two wolves deciding on what to have for lunch. Freedom is a well armed sheep contesting the results of the decision. -- Benjamin Franklin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] SAN vs Internal Disks
Bryan Murphy wrote: Our database server connects to the san via iSCSI over Gig/E using jumbo frames. File system is XFS (noatime). ... Throughput, however, kinda sucks. I just can't get the kind of throughput to it I was hoping to get. When our memory cache is blown, the database can be downright painful for the next few minutes as everything gets paged back into the cache. Remember that Gig/E is bandwidth limited to about 100 Mbyte/sec. Maybe a little faster than that downhill with a tailwind, but not much. You're going to get much better bandwidth connecting to a local raid card talking to local disks simply due to not having the ethernet as a bottleneck. iSCSI is easy to set up and manage, but it's slow. This is the big advantage Fibre Channel has- serious performance. You can have multiple channels on a single fibre channel card- IIRC, QLogic's cards have a default of 4 channels- each pumping 400 Mbyte/sec. At which point the local bus rapidly becomes the bottleneck. Of course, this comes at the cost of a signifigant increase in complexity. Brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] SAN vs Internal Disks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alan Hodgson wrote: On Friday 07 September 2007 10:56, Bryan Murphy [EMAIL PROTECTED] wrote: Our database server connects to the san via iSCSI over Gig/E using jumbo frames. File system is XFS (noatime). Throughput, however, kinda sucks. I just can't get the kind of throughput to it I was hoping to get. A single Gig/E couldn't even theoretically do better than 125MB/sec, so yeah I would expect throughput sucks pretty bad. We have a customer that has a iSCSI SAN that can bond multiple Gig/E connections that provides them with reasonable performance. You should see if yours allows it. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4ZbGATb/zqfZUUQRAhtmAKCh/PsmkL/JOPq4++Aci2/XwDDJ7wCfbwJs 5vBg+TG5xQFKoJMdybpjDWo= =up8R -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
On Fri, 2007-09-07 at 11:37 +0200, Maila Fatticcioni wrote: protocol C; Try protocol B instead. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] SAN vs Internal Disks
On Fri, 7 Sep 2007, Tobias Brox wrote: We're also considering to install postgres on SAN - that is, my boss is convinced this is the right way to go. Advantages: 1. Higher I/O (at least the salesman claims so) Shockingly, the salesman is probably lying to you. The very concept of SAN says that you're putting something in between your system and the disks, and that something therefore must slow things down compared to connecting directly. iSCSI, FC, whatever you're using as the communications channel can't be as fast as a controller card with a good interface straight into the motherboard. For example, a PCI-E x16 disk controller card maxes out at 4GB/s in each direction; good luck bonding enough iSCSI or FC channels together to reach that transfer rate and getting something even remotely cost-competative with an internal card. The cases where a SAN can improve upon performance over direct discs are when the comparison isn't quite fair; for example: 1) The SAN allows spreading the load over more disks than you can fit internally in the system 2) The SAN provides a larger memory cache than the internal cards you're comparing against If you're in one of those situations, then perhaps the salesman's claim could have some merit. There are lots of reasons one might want to use a SAN, but a higher I/O rate when fairly comparing to connecting disks directly is unlikely to be on that list. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] SAN vs Internal Disks
On Sep 6, 2007, at 2:42 PM, Scott Marlowe wrote: I'd recommend against Dell unless you're at a company that orders computers by the hundred lot. My experience with Dell has been that unless you are a big customer you're just another number (a small one at that) on a spreadsheet. I order maybe 5-6 servers per year from dell, and the sales rep knows me when I call him. Just set up a business account. That said, lately I've been buying Sun X4100's for my DB servers. These machines are built like tanks and extremely fast. The only difficulty is hooking up disks to them. The only sane choice is to use a fibre channel card to an external array. The only dual-channel internal SCSI RAID controller that fits is an Adaptec model, and it is to be avoided. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN vs Internal Disks
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: On Fri, 7 Sep 2007, Tobias Brox wrote: We're also considering to install postgres on SAN - that is, my boss is convinced this is the right way to go. Advantages: 1. Higher I/O (at least the salesman claims so) In general a SAN does not provide more I/O than direct attached storage. It is all about the BUS, Controller and drive types. only if you buy better disks for the SAN then for the local system (note that this includes battery backed ram for write caching. the SAN will include a bunch becouse it's performance would _suck_ otherwise. if you don't put any on your stand-alone system you are comparing apples to oranges) 2. Easier to upgrade the disk capacity only if you buy a SAN with a lot of empty drive slots, but wouldn't buy a system with empty drive slots. Well there are SANs that have trays that can be stacked, but then again you can get the same thing with DAS too. 3. Easy to set up warm standby functionality. (Then again, if the postgres server fails miserably, it's likely to be due to a disk crash). Also, my boss states that all big enterprises uses SAN nowadays. Uhmm as someone who consults with many of the big enterprises that are running PostgreSQL, that is *not* true. 2. Expensive no, _extremely expensive. price one and then look at how much hardware Let me just +1 this. The amount of DAS storage you can get for 30k is amazing compared to the amount of SAN you can get for 30k. Joshua D. Drake you could buy instead. you can probably buy much mroe storage, and a couple complete spare systems (do replication to a local spare as well as your remote system) and end up with even more reliability. 3. Single point of failure ... but that you have either it's a SAN or a local disk, one will anyway need good backup systems (and eventually warm standby-servers running from physically separated disks). no, with local disks you can afford to have multiple systems so that you don't have a SPOF 4. More complex setup? 5. If there are several hosts with write permission towards the same disk, I can imagine the risks being higher for data integrity breakages. Particularly, I can imagine that if two postgres instances is started up towards the same disk (due to some sysadmin mistake), it could be disasterous. when you are useing a SAN for a database the SAN vendor will have you allocate complete disks to each box, so you don't have multiple boxes hitting the same drive, but you also don't get a lot of the anvantages the salesman talks about. David Lang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4b9/ATb/zqfZUUQRAnBiAJ4kdOicN3If4scLAVdaU4nS+srGHQCgnkR2 C6RvSyLcAtgQ1bJJEau8s00= =lqbw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] SAN vs Internal Disks
On Fri, 7 Sep 2007, Tobias Brox wrote: We're also considering to install postgres on SAN - that is, my boss is convinced this is the right way to go. Advantages: 1. Higher I/O (at least the salesman claims so) only if you buy better disks for the SAN then for the local system (note that this includes battery backed ram for write caching. the SAN will include a bunch becouse it's performance would _suck_ otherwise. if you don't put any on your stand-alone system you are comparing apples to oranges) 2. Easier to upgrade the disk capacity only if you buy a SAN with a lot of empty drive slots, but wouldn't buy a system with empty drive slots. 3. Easy to set up warm standby functionality. (Then again, if the postgres server fails miserably, it's likely to be due to a disk crash). and if postgres dies for some other reason the image on disk needs repair, unless you script stopping postgres when the SAN does it's snapshots, those snapshots are not going to be that good. the problems are useually repairable, but that makes starting your warm spare harder. Also, my boss states that all big enterprises uses SAN nowadays. your bos would be very surprised at what the really big shops are doing (and not doing). yes they have a SAN, they have many SANs, from many different vendors, and they have many systems that don't use the SAN and use local disks instead. when you get really large you can find just about anything _somewhere_ in the company. Disadvantages: 1. Risky? One gets the impression that there are frequent problems with data integrity when reading some of the posts in this thread. SAN's add more parts and more potential points of failure, then when you add the SAN replication to the mix things get even more 'interesting'. doing SAN replication across a significant distance to your DR facility can be a LOT harder to get working right then the salesman makes it sound. it's not uncommon to see a san replication decide that it's going to take a week to catch up after doing a DR test for example. 2. Expensive no, _extremely expensive. price one and then look at how much hardware you could buy instead. you can probably buy much mroe storage, and a couple complete spare systems (do replication to a local spare as well as your remote system) and end up with even more reliability. 3. Single point of failure ... but that you have either it's a SAN or a local disk, one will anyway need good backup systems (and eventually warm standby-servers running from physically separated disks). no, with local disks you can afford to have multiple systems so that you don't have a SPOF 4. More complex setup? 5. If there are several hosts with write permission towards the same disk, I can imagine the risks being higher for data integrity breakages. Particularly, I can imagine that if two postgres instances is started up towards the same disk (due to some sysadmin mistake), it could be disasterous. when you are useing a SAN for a database the SAN vendor will have you allocate complete disks to each box, so you don't have multiple boxes hitting the same drive, but you also don't get a lot of the anvantages the salesman talks about. David Lang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
--On Freitag, September 07, 2007 20:00:16 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Fri, 2007-09-07 at 11:37 +0200, Maila Fatticcioni wrote: protocol C; Try protocol B instead. But that would have an impact on transaction safety, wouldn't it? It will return immediately after reaching the remote buffer cache and you can't be sure your data hits the remote disk. It's a while ago i've played with such a setup, but it could be worth to play around with max_buffers, al-extends, snd-bufsize. Oh and i think Maila's 'rate' setting is too high: i've found rate settings counterproductive when set too high (try a value slightly above your max bandwidth of your connection). But i second Heikki, you should take care on your disk setup as well. -- Thanks Bernd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
