Thread: Increasing system speed by using -F option
Hi,
I notice a dramatic speed increase (3 to 4 times faster) by using the -F option when starting the database, at a cost of database corruption if the OS crashes. This database is mission-critical so data reliability is extremely important, meaning I cannot use the -F option. Are there any ways of achieving similar speed increases without using the -F option? I am using RedHat 6.1, 7.0.2, -B 2048 -N 1024 -i.
Thanks,
Mark.
-F makes PG *not* call fsync(), therefore the risk of corruption and the speed increase. (The good, the bad and if it crashes , the ugly) It's possible to speed up queries by increasing your -B and -S parameters to use some more memory.. There is also a lot to be said for _proper_ use of indexes ( Use EXPLAIN!).. If you could give us a little more information about the tables you're searching and the queries you're using, maybe we could provide some more specific help.. Good luck! ----- Original Message ----- From: "Mark Alliban" <MarkA@idnltd.com> To: <pgsql-general@postgresql.org> Sent: Friday, September 01, 2000 5:53 AM Subject: [GENERAL] Increasing system speed by using -F option Hi, I notice a dramatic speed increase (3 to 4 times faster) by using the -F option when starting the database, at a cost of database corruption if the OS crashes. This database is mission-critical so data reliability is extremely important, meaning I cannot use the -F option. Are there any ways of achieving similar speed increases without using the -F option? I am using RedHat 6.1, 7.0.2, -B 2048 -N 1024 -i. Thanks, Mark.
Mitch Vincent wrote: > > -F makes PG *not* call fsync(), therefore the risk of corruption and the > speed increase. (The good, the bad and if it crashes , the ugly) > > It's possible to speed up queries by increasing your -B and -S parameters to > use some more memory.. There is also a lot to be said for _proper_ use of > indexes ( Use EXPLAIN!).. > > If you could give us a little more information about the tables you're > searching and the queries you're using, maybe we could provide some more > specific help.. > > Good luck! > > ----- Original Message ----- > From: "Mark Alliban" <MarkA@idnltd.com> > To: <pgsql-general@postgresql.org> > Sent: Friday, September 01, 2000 5:53 AM > Subject: [GENERAL] Increasing system speed by using -F option > > Hi, > > I notice a dramatic speed increase (3 to 4 times faster) by using the -F > option when starting the database, at a cost of database corruption if the > OS crashes. This database is mission-critical so data reliability is > extremely important, meaning I cannot use the -F option. Are there any ways > of achieving similar speed increases without using the -F option? I am using > RedHat 6.1, 7.0.2, -B 2048 -N 1024 -i. > > Thanks, > Mark. Well from my experience the reason systems crash are hardware failuers or power failuers. If your hard disk crashes it won't make any differnce whether you fsync was on off, you are reloading from backups unless you are running araid system. If the system is a mission critical one then I'd assume you would have some sort of UPS or alternate power supply.. it would seem that the chances of a hardware failure is minimal leaving the fsync off is probably pretty safe. -- Dave Smith Candata Systems Ltd. (416) 493-9020 dave@candata.com
> Well from my experience the reason systems crash are hardware failuers > or power failuers. If your hard disk crashes it won't make any differnce > whether you fsync was on off, you are reloading from backups unless you > are running araid system. If the system is a mission critical one then > I'd assume you would have some sort of UPS or alternate power supply.. > it would seem that the chances of a hardware failure is minimal leaving > the fsync off is probably pretty safe. ... or someone tripping over a power cord to the UPS or someone hitting the reset button on the machine thinking it was a different machine -- the thing is that with fsync() off, even someone hitting the reset button on the machine and rebooting it could cause severe database corruption even though the rest of your system is virtually unharmed with a spontaneous reboot (minus the running of fsck or other utilities to verify the drive's integrity ).. I agree that the chances of it happening are probably low but if the data is mission critical then I wouldn't even take a small chance with corruption.. Just be careful! -Mitch
Dave Smith wrote: > > Mitch Vincent wrote: > > > > -F makes PG *not* call fsync(), therefore the risk of corruption and the > > speed increase. (The good, the bad and if it crashes , the ugly) > > > > It's possible to speed up queries by increasing your -B and -S parameters to > > use some more memory.. There is also a lot to be said for _proper_ use of > > indexes ( Use EXPLAIN!).. > > > > If you could give us a little more information about the tables you're > > searching and the queries you're using, maybe we could provide some more > > specific help.. > > > > Good luck! > > > > ----- Original Message ----- > > From: "Mark Alliban" <MarkA@idnltd.com> > > To: <pgsql-general@postgresql.org> > > Sent: Friday, September 01, 2000 5:53 AM > > Subject: [GENERAL] Increasing system speed by using -F option > > > > Hi, > > > > I notice a dramatic speed increase (3 to 4 times faster) by using the -F > > option when starting the database, at a cost of database corruption if the > > OS crashes. This database is mission-critical so data reliability is > > extremely important, meaning I cannot use the -F option. Are there any ways > > of achieving similar speed increases without using the -F option? I am using > > RedHat 6.1, 7.0.2, -B 2048 -N 1024 -i. > > > > Thanks, > > Mark. > > Well from my experience the reason systems crash are hardware failuers > or power failuers. You obviously aren't running a M$ operating system.
Joseph Shraibman wrote: > > Dave Smith wrote: > > > > Mitch Vincent wrote: > > > > > > -F makes PG *not* call fsync(), therefore the risk of corruption and the > > > speed increase. (The good, the bad and if it crashes , the ugly) > > > > > > It's possible to speed up queries by increasing your -B and -S parameters to > > > use some more memory.. There is also a lot to be said for _proper_ use of > > > indexes ( Use EXPLAIN!).. > > > > > > If you could give us a little more information about the tables you're > > > searching and the queries you're using, maybe we could provide some more > > > specific help.. > > > > > > Good luck! > > > > > > ----- Original Message ----- > > > From: "Mark Alliban" <MarkA@idnltd.com> > > > To: <pgsql-general@postgresql.org> > > > Sent: Friday, September 01, 2000 5:53 AM > > > Subject: [GENERAL] Increasing system speed by using -F option > > > > > > Hi, > > > > > > I notice a dramatic speed increase (3 to 4 times faster) by using the -F > > > option when starting the database, at a cost of database corruption if the > > > OS crashes. This database is mission-critical so data reliability is > > > extremely important, meaning I cannot use the -F option. Are there any ways > > > of achieving similar speed increases without using the -F option? I am using > > > RedHat 6.1, 7.0.2, -B 2048 -N 1024 -i. > > > > > > Thanks, > > > Mark. > > > > Well from my experience the reason systems crash are hardware failuers > > or power failuers. > > You obviously aren't running a M$ operating system. Microsoft doesn't run their own mission critcal applications on their OS's -- Dave Smith Candata Systems Ltd. (416) 493-9020 dave@candata.com
> Mark Alliban wrote: > Are there any ways of achieving similar speed increases without using the -F option? I am using RedHat 6.1, 7.0.2, -B2048 -N 1024 -i. Many factors affect overall speed. You may have done some of these already, you may not have considered others. 1. Tune the kernel. RH 6.1 ships with scads of stuff you will never use (such as making a box into a high-speed-token-ring-router-to-isdn-multiplexor) turned on, making for a slower machine overall. Rip it all out, and recompile. 2. Turn other processes off. xfs, nfs, kudzu, pcmcia, inetd, httpd... all sorts of stuff is taking up your cpu cycles, your network bandwidth, your system bus. Anything you don't use hourly, *remove* from your running table. Anything you only use a few times an hour, nice[] it down. Anything that can be done on another box, do it there. For god's sake, don't run X on the same box, play mp3's, whatever. 3. Use a faster disk bus. Many folks start out with a single development machine, using some slower Ultra ATA/IDE disk, limiting their throughput to 33/66MB at _maximum_. The latest SCSI busses hit a theoretical 160MB, without tying up the CPU (as IDE/ATA does). You *can* get that kind of speed boost just by going from a cheapie IDE disk server to a high performance disk setup. 4. Use RAID. Again, this can be a trade-off of speed/stability, depending on the flavour of RAID. If you use a RAID card with a built-in battery, and can keep your drives powered separately from your motherboard, a power event may be staved off long enough for a write. Hardware RAID is faster than OS driven RAID. 5. Split your data files out to different busses. On my heaviest RedHat data box, I have 4 160MB SCSI busses, with a striped RAID on each, 128MB disk cache per pair of busses... this means that rather than maxing out at the limit of one disk bus, I can push to the maximum of 4 busses. 6. Fast disks are required for fast busses. 10K RPM at minimum, look for extremely low latency speeds, as most DB's aren't about high speed overall thoughput, but many tiny quick transactional hits. 7. Turn off any excessive logging. By default, many redhat boxen log an insane amount (all email, all logins, etc.), fsyc'ing every time, slowing the other disk writes (i.e. postgresql) down. 8. Get another CPU and/or more RAM. A second CPU will speed up _any_ multiprocess machine, more RAM means more of an OS-based disk cache for reads. You may or may not increase speed by 3x, (especially if you've already done all this), but it can get you closer (or even exceed it, you didn't say much about your hardware)... you might also want to focus on asking the question of *how* you could use -F "safely", by: 1. Using a reliable set of OS software, 2. battery backups (UPS'S), 3. redundant power supplies for both your CPU boxes and your disk boxes, 4. keep the box under lock and key (to avoid the "plug kickout" factor), 5. backup hourly, 6. Deny any non-postgresql uses of the box (prevent crashes/stalls/load from other user processes) HTH, -Bop -- Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine, which is currently in MacOS land. Your bopping may vary.
Mitch Vincent wrote: > > Well from my experience the reason systems crash are hardware failuers > > or power failuers. If your hard disk crashes it won't make any differnce > > whether you fsync was on off, you are reloading from backups unless you > > are running araid system. If the system is a mission critical one then > > I'd assume you would have some sort of UPS or alternate power supply.. > > it would seem that the chances of a hardware failure is minimal leaving > > the fsync off is probably pretty safe. > > ... or someone tripping over a power cord to the UPS or someone hitting the > reset button on the machine thinking it was a different machine -- the thing > is that with fsync() off, even someone hitting the reset button on the > machine and rebooting it could cause severe database corruption even though > the rest of your system is virtually unharmed with a spontaneous reboot > (minus the running of fsck or other utilities to verify the drive's > integrity ).. > > I agree that the chances of it happening are probably low but if the data is > mission critical then I wouldn't even take a small chance with corruption.. > > Just be careful! > > -Mitch If your system is mission critical, then you lock it away in a separate room, and take care that only the people who really need access have keys. This should not be more than three persons. And since this is Linux, once the system is up and running, all administration can be done via telnet. The only reason that you enter the room is to change backup-tapes (or maybe backups can be taken remotely) and to do hardware maintenance. Jurgen
> should not be more than three persons. And since this is Linux, once the > system is up and running, all administration can be done via telnet. s/telnet/ssh/ Using telnet is like dropping your spare key outside the security shield every time you go in. :) - Jeff -- excuse the email software - i have not yet enlightened my coworkers --