Re: [PERFORM] Writting a "search engine" for a pgsql DB
On Tue, Feb 27, 2007 at 06:36:11PM -0500, Charles Sprickman wrote: > On Tue, 27 Feb 2007, Dave Page wrote: > > >Magnus Hagander wrote: > >> > >>Just as a datapoint, we did try to use mnogosearch for the > >>postgresql.org website+archives search, and it fell over completely. > >>Indexing took way too long, and we had search times several thousand > >>times longer than with tsearch2. > >> > >>That said, I'm sure there are cases when it works fine :-) > > > >There are - in fact before your time the site did use Mnogosearch. We > >moved to our own port of ASPSeek when we outgrew Mnogo's capabilities, > >and then to your TSearch code when we outgrew ASPSeek. > > At risk of pulling this way too far off topic, may I ask how many > documents (mail messages) you were dealing with when things started to > fall apart with mnogo? We're looking at it for a new project that will > hopefully get bigger and bigger. We will be throwing groups of mailing > lists into their own mnogo config/tables... If we should save ourselves > the pain and look at something more homebrew, then we'll start > investigating "Tsearch". I don't know when it broke exactly, but I know we're currently doing about 600,000 documents. AFAIK it started to fall apart pretty long before that. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Opinions on Raid
Joe Uhl wrote: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. One thing you should appreciate about hw vs sw raid is that with the former you can battery-back it and enable controller write caching in order to make disk write latency largely disappear. How much of a performance difference that makes depends on what you're doing with it, of course. See the current thread "Two hard drives --- what to do with them?" for some discussion of the virtues of battery-backed raid. [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. Personally I use nagios with nrpe for most of the monitoring, and write a little wrapper around the cli monitoring tool from the controller manufacturer to grok whether it's in a good/degraded/bad state. Dell PERC controllers I think are mostly just derivatives of Adaptec/LSI controllers, so you might be able to get a more convenient monitoring tool from one of them that might work. See if you can find your PERC version in http://pciids.sourceforge.net/pci.ids, or if you're using Linux then which hw raid module is loaded for it, to get an idea of which place to start looking for that. - Geoff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Writting a "search engine" for a pgsql DB
On Tue, 27 Feb 2007, Dave Page wrote: Magnus Hagander wrote: Just as a datapoint, we did try to use mnogosearch for the postgresql.org website+archives search, and it fell over completely. Indexing took way too long, and we had search times several thousand times longer than with tsearch2. That said, I'm sure there are cases when it works fine :-) There are - in fact before your time the site did use Mnogosearch. We moved to our own port of ASPSeek when we outgrew Mnogo's capabilities, and then to your TSearch code when we outgrew ASPSeek. At risk of pulling this way too far off topic, may I ask how many documents (mail messages) you were dealing with when things started to fall apart with mnogo? We're looking at it for a new project that will hopefully get bigger and bigger. We will be throwing groups of mailing lists into their own mnogo config/tables... If we should save ourselves the pain and look at something more homebrew, then we'll start investigating "Tsearch". Thanks, Charles When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop pretending to be Google... /D ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Writting a "search engine" for a pgsql DB
Madison Kelly wrote: Hi all, I am asking in this list because, at the end of the day, this is a performance question. I am looking at writing a search engine of sorts for my database. I have only ever written very simple search engines before which amounted to not much more that the query string being used with ILIKE on a pile of columns. This was pretty rudimentary and didn't offer anything like relevance sorting and such (I'd sort by result name, age or whatnot). So I am hoping some of you guys and gals might be able to point me towards some resources or offer some tips or gotcha's before I get started on this. I'd really like to come up with a more intelligent search engine that doesn't take two minutes to return results. :) I know, in the end good indexes and underlying hardware will be important, but a sane as possible query structure helps to start with. As someone mentioned, tsearch2 is a good option. I wrote a small article about how to get it set up relatively easily: http://www.designmagick.com/article/27/ -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Two hard drives --- what to do with them?
On Tue, 2007-02-27 at 13:23, Jeff Davis wrote: > Also, put things in context. The chances of failure due to these kinds > of things are fairly low. If it's more likely that someone spills coffee > on your server than the UPS fails, it doesn't make sense to spend huge > amounts of money on NVRAM (or something) to store your data. So identify > the highest-risk scenarios and prevent those first. > > Also keep in mind what the cost of failure is: a few hundred bucks more > on a better RAID controller is probably a good value if it prevents a > day of chaos and unhappy customers. Just FYI, I can testify to the happiness a good battery backed caching RAID controller can bring. I had the only server that survived a complete power grid failure in the data center where I used to work. A piece of wire blew out a power conditioner, which killed the other power conditioner, all three UPSes and the switch to bring the diesel generator online. the only problem the pgsql server had coming back up was that it had remote nfs mounts it used for file storage that weren't able to boot up fast enough so we just waited a few minutes and rebooted it. All of our other database servers had to be restored from backup due to massive data corruption because someone had decided that NFS mounts were a good idea under databases. ---(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] Two hard drives --- what to do with them?
On Tue, 2007-02-27 at 09:27 +0100, Peter Kovacs wrote: > I wonder if running a machine on a UPS + 1 hot standby internal PS is > equivalent, in terms of data integrity, to using battery backed write > cache. Instinctively, I'd think that UPS + 1 hot standby internal PS > is better, since this setup also provides for the disk to actually > write out the content of the cache -- as you pointed out. It's all about the degree of safety. A battery-backed cache on a RAID controller sits below all of these points of failure: * External power * Power supply * Operating system and with proper system administration, can recover from any transient errors in the above. Keep in mind that it can only recover from transient failures: if you have a long blackout that outlasts your UPS and cache battery, you can still have data loss. Also, you need a very responsive system administrator that can make sure that data gets to disk in case of failure. Let's say you have a RAID system but you rely on the UPS to make sure the data hits disk. Well, now if you have an OS crash (caused by another piece of hardware failing, perhaps), you've lost your data. If you can afford it (in terms of dollars or performance hit) go with the safe solution. Also, put things in context. The chances of failure due to these kinds of things are fairly low. If it's more likely that someone spills coffee on your server than the UPS fails, it doesn't make sense to spend huge amounts of money on NVRAM (or something) to store your data. So identify the highest-risk scenarios and prevent those first. Also keep in mind what the cost of failure is: a few hundred bucks more on a better RAID controller is probably a good value if it prevents a day of chaos and unhappy customers. Regards, Jeff Davis ---(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] Opinions on Raid
On Tue, 2007-02-27 at 12:28, Joe Uhl wrote: > Really appreciate all of the valuable input. The current server has the > Perc4ei controller. > > The impression I am taking from the responses is that we may be okay with > software raid, especially if raid 1 and 10 are what we intend to use. > > I think we can collect enough information from the archives of this list to > help make decisions for the new machine(s), was just very interested in > hearing feedback on software vs. hardware raid. > > We will likely be using the 2.6.18 kernel. Well, whatever you do, benchmark it with what you think will be your typical load. you can do some simple initial tests to see if you're in the ballpark with bonnie++, dd, etc... Then move on to real database tests after that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [EMAIL PROTECTED]: Anyone interested in improving postgresql scaling?]
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Ok, looks like the FreeBSD community is interested in PostgreSQL > performance, or at least enough to investigate it. I think this guy is barking up the wrong tree though, because we don't ever have multiple processes sleeping on the same sema. Not that there's anything wrong with his proposal, but it doesn't affect Postgres. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Two hard drives --- what to do with them?
Peter Kovacs wrote: > The reason this becomes an issue is that many consumer-grade disks have > write cache enabled by default and no way to make sure the cached data > actually gets written. So, essentially, these disks "lie" and say they > wrote the data, when in reality, it's in volatile memory. It's > recommended that you disable write cache on such a device. From all that I have heard this is another advantage of SCSI disks - they honor these settings as you would expect - many IDE/SATA disks often say "sure I'll disable the cache" but continue to use it or don't retain the setting after restart. As far as I know, SCSI drives also have "write cache" which is turned off by default, but can be turned on (e.g. with the sdparm utility on Linux). The reason I am so much interested in how write cache is typically used (on or off) is that I recently ran our benchmarks on a machine with SCSI disks and those benchmarks with high commit ratio suffered significantly compared to our previous results "traditionally" obtained on machines with IDE drives. Most likely - with write cache, when the drive gets the data it puts it into cache and then says "yep all done" and you continue on as it puts it on the disk. But if the power goes out as it's doing that you got trouble. The difference between SCSI and IDE/SATA in this case is a lot if not all IDE/SATA drives tell you that the cache is disabled when you ask it to but they either don't actually disable it or they don't retain the setting so you get caught later. SCSI disks can be trusted when you set this option. I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out. This is covering two different scenarios. The UPS maintains power in the event of a black out. The hot standby internal PS maintains power when the first PS dies. It is a good choice to have both as a PS dying will be just as bad as losing power without a UPS and the UPS won't save you if the PS goes. A battery backed raid card sits in between these - as long as the drive's write cache is off - the raid card will hold data that was sent to disk until it confirms it is written to disk. The battery backup will even hold that data until the machine is switched back on when it completes the writing to disk. That would cover you even if the PS goes. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] [EMAIL PROTECTED]: Anyone interested in improving postgresql scaling?]
Ok, looks like the FreeBSD community is interested in PostgreSQL performance, or at least enough to investigate it. Anyone here have experience hacking on FreeBSD? - Forwarded message from Kris Kennaway <[EMAIL PROTECTED]> - X-Spam-Checker-Version: SpamAssassin 3.1.6 (2006-10-03) on noel.decibel.org X-Spam-Level: X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_50, FORGED_RCVD_HELO,SPF_PASS autolearn=no version=3.1.6 Date: Sun, 25 Feb 2007 19:22:35 -0500 From: Kris Kennaway <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] User-Agent: Mutt/1.4.2.2i Cc: [EMAIL PROTECTED] Subject: Anyone interested in improving postgresql scaling? Precedence: list Errors-To: [EMAIL PROTECTED] If so, then your task is the following: Make SYSV semaphores less dumb about process wakeups. Currently whenever the semaphore state changes, all processes sleeping on the semaphore are woken, even if we only have released enough resources for one waiting process to claim. i.e. there is a thundering herd wakeup situation which destroys performance at high loads. Fixing this will involve replacing the wakeup() calls with appropriate amounts of wakeup_one(). Kris ___ freebsd-current@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-current To unsubscribe, send any mail to "[EMAIL PROTECTED]" - End forwarded message - -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] [EMAIL PROTECTED]: Progress on scaling of FreeBSD on 8 CPU systems]
Thought I'd pass this along, since the Linux vs FreeBSD performance question comes up fairly regularly... BTW, I've already asked about benchmarking with PostgreSQL, so please don't go over there making trouble. :) - Forwarded message from Kris Kennaway <[EMAIL PROTECTED]> - X-Spam-Checker-Version: SpamAssassin 3.1.6 (2006-10-03) on noel.decibel.org X-Spam-Level: X-Spam-Status: No, score=-0.9 required=5.0 tests=AWL,BAYES_50, FORGED_RCVD_HELO,SPF_PASS autolearn=no version=3.1.6 Date: Sat, 24 Feb 2007 16:31:11 -0500 From: Kris Kennaway <[EMAIL PROTECTED]> To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] User-Agent: Mutt/1.4.2.2i Cc: Subject: Progress on scaling of FreeBSD on 8 CPU systems Precedence: list Errors-To: [EMAIL PROTECTED] Now that the goals of the SMPng project are complete, for the past year or more several of us have been working hard on profiling FreeBSD in various multiprocessor workloads, and looking for performance bottlenecks to be optimized. We have recently made significant progress on optimizing for MySQL running on an 8-core amd64 system. The graph of results may be found here: http://www.freebsd.org/~kris/scaling/scaling.png This shows the graph of MySQL transactions/second performed by a multi-threaded client workload against a local MySQL database with varying numbers of client threads, with identically configured FreeBSD and Linux systems on the same machine. The test was run on FreeBSD 7.0, with the latest version of the ULE 2.0 scheduler, the libthr threading library, and an uncommitted patch from Jeff Roberson [1] that addresses poor scalability of file descriptor locking (using a new sleepable mutex primitive); this patch is responsible for almost all of the performance and scaling improvements measured. It also includes some other patches (collected in my kris-contention p4 branch) that have been shown to help contention in MySQL workloads in the past (including a UNIX domain socket locking pushdown patch from Robert Watson), but these were shown to only give small individual contributions, with a cumulative effect on the order of 5-10%. With this configuration we are able to achieve performance that is consistent with Linux at peak (the graph shows Linux 2% faster, but this is commensurate with the margin of error coming from variance between runs, so more data is needed to distinguish them), with 8 client threads (=1 thread/CPU core), and significantly outperforms Linux at higher than peak loads, when running on the same hardware. Specifically, beyond 8 client threads FreeBSD has only minor performance degradation (an 8% drop from peak throughput at 8 clients to 20 clients), but Linux collapses immediately above 8 threads, and above 14 threads asymptotes to essentially single-threaded levels. At 20 clients FreeBSD outperforms Linux by a factor of 4. We see this result as part of the payoff we are seeing from the hard work of many developers over the past 7 years. In particular it is a significant validation of the SMP and locking strategies chosen for the FreeBSD kernel in the post-FreeBSD 4.x world. More configuration details and discussion about the benchmark may be found here: http://people.freebsd.org/~kris/scaling/mysql.html Kris - End forwarded message - -- Jim C. Nasby, Database Architect[EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Postgres performance Linux vs FreeBSD
On 2007-02-21, Mark Kirkwood <[EMAIL PROTECTED]> wrote: > With respect to 'select count(*) from ...' being slower on FreeBSD, > there are a number of things to try to make FreeBSD faster for this sort > of query. Two I'm currently using are: > > - setting sysctl vfs.read_max to 16 or 32 > - rebuilding the relevant filesystem with 32K blocks and 4K frags Be aware that increasing the filesystem block size above 16k is _known_ to tickle kernel bugs - there is a workaround involving increasing BKVASIZE, but this isn't a config parameter and therefore you have to patch the sources. The symptom to look for is: largescale filesystem deadlocks with many processes (especially syncer) blocked in "nbufkv" state. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Opinions on Raid
On Tue, 2007-02-27 at 07:12, Joe Uhl wrote: > We have been running Postgres on a 2U server with 2 disks configured in > raid 1 for the os and logs and 4 disks configured in raid 10 for the > data. I have since been told raid 5 would have been a better option > given our usage of Dell equipment and the way they handle raid 10. Some controllers do no layer RAID effectively. Generally speaking, the cheaper the controller, the worse it's gonna perform. Also, some controllers are optimized more for RAID 5 than RAID 1 or 0. Which controller does your Dell have, btw? > I > have just a few general questions about raid with respect to Postgres: > > [1] What is the performance penalty of software raid over hardware raid? > Is it truly significant? We will be working with 100s of GB to 1-2 TB > of data eventually. For a mostly read system, the performance is generally pretty good. Older linux kernels ran layered RAID pretty slowly. I.e. RAID 1+0 was no faster than RAID 1. The best performance software RAID I found in older linux kernels (2.2, 2.4) was plain old RAID-1. RAID-5 was good at reading, but slow at writing. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Opinions on Raid
Hope you don't mind, Ron. This might be splitting hairs. On Tue, Feb 27, 2007 at 11:05:39AM -0500, Ron wrote: > The real CPU overhead when using SW RAID is when using any form of SW > RAID that does XOR operations as part of writes (RAID 5, 6, 50, ..., > etc). At that point, you are essentially hammering on the CPU just > as hard as you would on a dedicated RAID controller... ...and the > dedicated RAID controller probably has custom HW helping it do this > sort of thing more efficiently. > That being said, SW RAID 5 in this sort of scenario can be reasonable > if you =dedicate= a CPU core to it. So in such a system, your "n" > core box is essentially a "n-1" core box because you have to lock a > core to doing nothing but RAID management. I have an issue with the above explanation. XOR is cheap. It's one of the cheapest CPU instructions available. Even with high bandwidth, the CPU should always be able to XOR very fast. This leads me to the belief that the RAID 5 problem has to do with getting the data ready to XOR. With RAID 5, the L1/L2 cache is never large enoguh to hold multiple stripes of data under regular load, and the system may not have the blocks in RAM. Reading from RAM to find the missing blocks shows up as CPU load. Reading from disk to find the missing blocks shows up as system load. Dedicating a core to RAID 5 focuses on the CPU - which I believe to be mostly idle waiting for a memory read. Dedicating a core reduces the impact, but can't eliminate it, and the cost of a whole core to sit mostly idle waiting for memory reads is high. Also, any reads scheduled by this core will affect the bandwidth/latency for other cores. Hardware RAID 5 solves this by using its own memory modules - like a video card using its own memory modules. The hardware RAID can read from its own memory or disk all day and not affect system performance. Hopefully it has plenty of memory dedicated to holding the most frequently required blocks. > SW RAID 5 etc in usage scenarios involving far more reads than writes > and light write loads can work quite well even if you don't dedicate > a core to RAID management, but you must be careful about workloads > that are, or that contain parts that are, examples of the first > scenario I gave. If you have any doubts about whether you are doing > too many writes, dedicate a core to RAID stuff as in the first scenario. I found software RAID 5 to suck such that I only use it for backups now. It seemed that Linux didn't care to read-ahead or hold blocks in memory for too long, and preferred to read and then write. It was awful. RAID 5 doesn't seem like a good option even with hardware RAID. They mask the issues with it behind a black box (dedicated hardware). The issues still exist. Most of my system is RAID 1+0 now. I have it broken up. Rarely read or written files (long term storage) in RAID 5, The main system data on RAID 1+0. The main system on RAID 1. A larger build partition on RAID 0. For a crappy server in my basement, I've very happy with my software RAID performance now. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Opinions on Raid
At 08:12 AM 2/27/2007, Joe Uhl wrote: We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. The real CPU overhead when using SW RAID is when using any form of SW RAID that does XOR operations as part of writes (RAID 5, 6, 50, ..., etc). At that point, you are essentially hammering on the CPU just as hard as you would on a dedicated RAID controller... ...and the dedicated RAID controller probably has custom HW helping it do this sort of thing more efficiently. That being said, SW RAID 5 in this sort of scenario can be reasonable if you =dedicate= a CPU core to it. So in such a system, your "n" core box is essentially a "n-1" core box because you have to lock a core to doing nothing but RAID management. Religious wars aside, this actually can work well. You just have to understand and accept what needs to be done. SW RAID 1, or 10, or etc should not impose a great deal of CPU overhead, and often can be =faster= than a dedicated RAID controller. SW RAID 5 etc in usage scenarios involving far more reads than writes and light write loads can work quite well even if you don't dedicate a core to RAID management, but you must be careful about workloads that are, or that contain parts that are, examples of the first scenario I gave. If you have any doubts about whether you are doing too many writes, dedicate a core to RAID stuff as in the first scenario. [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. Many RAID controller manufacturers and storage product companies offer reasonable monitoring / management tools. 3ware AKA AMCC has a good reputation in this area for their cards. So does Areca. I personally do not like Adaptec's SW for this purpose, but YMMV. LSI Logic has had both good and bad SW in this area over the years. Dell, HP, IBM, etc's offerings in this area tend to be product line specific. I'd insist on some sort of "try before you buy" if the ease of use / quality of the SW matters to your overall purchase decision. Then there are the various CSSW and OSSW packages that contain this functionality or are dedicated to it. Go find some reputable reviews. (HEY LURKERS FROM Tweakers.net: ^^^ THAT"S AN ARTICLE IDEA ;-) ) Cheers, Ron ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Two hard drives --- what to do with them?
Just remember that batteries (in both RAID cards and UPSes) wear out and will eventually have to be replaced. It depends how critical your data is, but if you only have a UPS, you risk badness in the off chance that your power fails and you haven't replaced your UPS battery. On Feb 27, 2007, at 12:27 AM, Peter Kovacs wrote: I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out.
Re: [PERFORM] Opinions on Raid
Joe Uhl wrote: We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. this depends a lot on the raidcontroller (whether it has or not BBWC for example) - for some use-cases softwareraid is actually faster(especially for seq-io tests). [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. well the answer to that question depends on what you are using for your network monitoring as a whole as well as your Platform of choice. If you use say nagios and Linux it makes sense to use a nagios plugin (we do that here with a unified check script that checks everything from LSI-MPT based raid cards, over IBMs ServeRAID, HPs Smartarray,LSI MegaRAID cards and also Linux/Solaris Software RAID). If you are using another monitoring solution(OpenView, IBM Directory,...) your solution might look different. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Writting a "search engine" for a pgsql DB
Magnus Hagander wrote: > > Just as a datapoint, we did try to use mnogosearch for the > postgresql.org website+archives search, and it fell over completely. > Indexing took way too long, and we had search times several thousand > times longer than with tsearch2. > > That said, I'm sure there are cases when it works fine :-) There are - in fact before your time the site did use Mnogosearch. We moved to our own port of ASPSeek when we outgrew Mnogo's capabilities, and then to your TSearch code when we outgrew ASPSeek. When we outgrow PostgreSQL & Tsearch2, then, well, we'll need to stop pretending to be Google... /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Opinions on Raid
We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. I truly appreciate any assistance or input. As an additional question, does anyone have any strong recommendations for vendors that offer both consulting/training and support? We are currently speaking with Command Prompt, EnterpriseDB, and Greenplum but I am certainly open to hearing any other recommendations. Thanks, Joe ---(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] Writting a "search engine" for a pgsql DB
On Mon, Feb 26, 2007 at 04:24:12PM -0500, Charles Sprickman wrote: > On Mon, 26 Feb 2007, Madison Kelly wrote: > > >Hi all, > > > >I'd really like to come up with a more intelligent search engine that > >doesn't take two minutes to return results. :) I know, in the end good > >indexes and underlying hardware will be important, but a sane as possible > >query structure helps to start with. > > I'm not a programmer, so I can't comment on how good of an example this > is, but I've been pretty happy with mnogosearch: > > http://www.mnogosearch.com/ > > The *nix versions are free. Looking at the db structure gave me a bit of > an idea of what I'm guessing is the "right way" to search a huge amount of > documents. Just as a datapoint, we did try to use mnogosearch for the postgresql.org website+archives search, and it fell over completely. Indexing took way too long, and we had search times several thousand times longer than with tsearch2. That said, I'm sure there are cases when it works fine :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Two hard drives --- what to do with them?
On 2/27/07, Shane Ambler <[EMAIL PROTECTED]> wrote: Jeff Davis wrote: >> Sorry for for not being familar with storage techonologies... Does >> "battery" here mean battery in the common sense of the word - some >> kind of independent power supply? Shouldn't the disk itself be backed >> by a battery? As should the entire storage subsystem? >> > > Yes, a battery that can hold power to keep data alive in the write cache > in case of power failure, etc., for a long enough time to recover and > commit the data to disk. Just to expand a bit - the battery backup options are available on some raid cards - that is where you would be looking for it. I don't know of any hard drives that have it built in. Of cause another reason to have a UPS for the server - keep it running long enough after the clients have gone down so that it can ensure everything is on disk and shuts down properly. > So, a write cache is OK (even for pg_xlog) if it is durable (i.e. on > permanent storage or backed by enough power to make sure it gets there). > However, if PostgreSQL has no way to know whether a write is durable or > not, it can't guarantee the data is safe. > > The reason this becomes an issue is that many consumer-grade disks have > write cache enabled by default and no way to make sure the cached data > actually gets written. So, essentially, these disks "lie" and say they > wrote the data, when in reality, it's in volatile memory. It's > recommended that you disable write cache on such a device. From all that I have heard this is another advantage of SCSI disks - they honor these settings as you would expect - many IDE/SATA disks often say "sure I'll disable the cache" but continue to use it or don't retain the setting after restart. As far as I know, SCSI drives also have "write cache" which is turned off by default, but can be turned on (e.g. with the sdparm utility on Linux). The reason I am so much interested in how write cache is typically used (on or off) is that I recently ran our benchmarks on a machine with SCSI disks and those benchmarks with high commit ratio suffered significantly compared to our previous results "traditionally" obtained on machines with IDE drives. I wonder if running a machine on a UPS + 1 hot standby internal PS is equivalent, in terms of data integrity, to using battery backed write cache. Instinctively, I'd think that UPS + 1 hot standby internal PS is better, since this setup also provides for the disk to actually write out the content of the cache -- as you pointed out. Thanks Peter -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate