Thread: how to configure my new server
I've a new configuration for our web server Processor Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2 Memoiry 1 Go DDR SDRAM Disk1 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk2 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm I will install a Mandrake 8.2 It's an application server that runs things other than just postgresql. It also runs: apache + Php , bigbrother, log analyser. At the moment, on my old server, there's postgresql 7.2.3 my database takes 469M and there are approximatively 5 millions query per day what values should I use for linux values: kernel.shmmni = 4096 kernel.shmall = 32000000 kernel.shmmax = 256000000 postgresql values: shared_buffers max_fsm_relations max_fsm_pages wal_buffers wal_files sort_mem vacuum_mem any other advices are welcome thanks in advance --------------------- Philip johnson 01 64 86 83 00 http://www.atempo.com
pgsql-performance-owner@postgresql.org wrote: > Objet : [PERFORM] how to configure my new server > Importance : Haute > > > I've a new configuration for our web server > > Processor Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2 > Memoiry 1 Go DDR SDRAM > Disk1 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk2 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > > I will install a Mandrake 8.2 > > It's an application server that runs things other than just > postgresql. It also runs: apache + Php , bigbrother, log analyser. > > > At the moment, on my old server, there's postgresql 7.2.3 > my database takes 469M and there are approximatively > 5 millions query per day > > > what values should I use for > > linux values: > kernel.shmmni = 4096 > kernel.shmall = 32000000 > kernel.shmmax = 256000000 > > postgresql values: > shared_buffers > max_fsm_relations > max_fsm_pages > wal_buffers > wal_files > sort_mem > vacuum_mem > > > any other advices are welcome > > thanks in advance > > > > --------------------- > Philip johnson > 01 64 86 83 00 > http://www.atempo.com > > ---------------------------(end of > broadcast)--------------------------- TIP 4: Don't 'kill -9' the > postmaster Someone is able to help me ?
Phillip, First, a disclaimer: my advice is without warranty whatsoever. You want a warranty, you gotta pay me. > I've a new configuration for our web server > > Processor Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2 > Memoiry 1 Go DDR SDRAM > Disk1 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk2 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm No RAID, though? Think carefully about which disks you put things on. Ideally, the OS, the web files, the database files, the database log, and the swap partition will all be on seperate disks. With a large database you may even think about shifting individual tables or indexes to seperate disks. > linux values: > kernel.shmmni = 4096 > kernel.shmall = 32000000 > kernel.shmmax = 256000000 These are probably too high, but I'm ready to speak authoritatively on that. > postgresql values: > shared_buffers > max_fsm_relations > max_fsm_pages > wal_buffers > wal_files > sort_mem > vacuum_mem Please visit the archives for this list. Setting those values is a topic of discussion for 50% of the threads, and there is yet no firm agreement on good vs. bad values. Also, you need to ask youself more questions before you start setting values: 1. How many queries does my database handle per second or minute? 2. How big/complex are those queries? 3. What is the ratio of database read activity vs. database writing activity? 4. What large tables in my database get queried simultaneously/together? 5. Are my database writes bundled into transactions, or seperate? etc. Simply knowing the size of the database files isn't enough. -- Josh Berkus Aglio Database Solutions San Francisco
pgsql-performance-owner@postgresql.org wrote:
> Phillip,
>
> First, a disclaimer: my advice is without warranty whatsoever. You
> want a warranty, you gotta pay me.
>
>> I've a new configuration for our web server
>>
>> Processor Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2
>> Memoiry 1 Go DDR SDRAM Disk1 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm
>> Disk2 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>> Disk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>> Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>> Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>
> No RAID, though?
Yes no Raid, but will could change soon
>
> Think carefully about which disks you put things on. Ideally, the
> OS, the web files, the database files, the database log, and the swap
> partition will all be on seperate disks. With a large database you
> may even think about shifting individual tables or indexes to
> seperate disks.
how can I put indexes on a seperate disk ?
>
>> linux values:
>> kernel.shmmni = 4096
>> kernel.shmall = 32000000
>> kernel.shmmax = 256000000
>
> These are probably too high, but I'm ready to speak authoritatively
> on that.
I took a look a the performance archive, and it's not possible to find
real info on how to set these 3 values.
>
>> postgresql values:
>> shared_buffers
>> max_fsm_relations
>> max_fsm_pages
>> wal_buffers
>> wal_files
>> sort_mem
>> vacuum_mem
>
> Please visit the archives for this list. Setting those values is a
> topic of discussion for 50% of the threads, and there is yet no firm
> agreement on good vs. bad values.
>
I'm surprised that there's no spreadsheet to calculate those values.
There are many threads, but it seems that no one is able to find a rule
to define values.
> Also, you need to ask youself more questions before you start setting
> values:
>
> 1. How many queries does my database handle per second or minute?
can't say now
> 2. How big/complex are those queries?
Not really complex and big as you can see
SELECT qu_request.request_id, qu_request.type, qu_request_doc.ki_status,
qu_request_doc.ki_subject, qu_request_doc.ki_description,
qu_request_doc.ki_category, qu_request_doc.rn_description_us,
qu_request_doc.rn_status_us, quad_config_nati.nati_version_extended
FROM qu_request left join quad_config_nati on qu_request.quad_server_nati =
quad_config_nati.nati_version
left join qu_request_doc on qu_request.request_id =
qu_request_doc.request_id
WHERE qu_request.request_id = '130239'
select sv_inquiry.inquiry_id, sv_inquiry.quad_account_inquiry_id
,to_char(sv_inquiry.change_dt, 'YYYY-MM-DD HH24:MI') as change_dt ,
to_char(sv_inquiry.closed_dt, 'YYYY-MM-DD HH24:MI') as closed_dt
,sv_inquiry.state, sv_inquiry.priority, sv_inquiry.type,
account_contact.dear as contact , account_contact2.dear as contact2,
sv_inquiry.action, sv_inquiry.activity ,
substr(sv_inq_txt.inquiry_txt, 1, 120) as inquiry_txt from sv_inquiry left
join sv_inq_txt on sv_inquiry.inquiry_id = sv_inq_txt.inquiry_id
left join account_contact on sv_inquiry.account_contact_id =
account_contact.account_contact_id left join account_contact
account_contact2
on sv_inquiry.account_contact_id2 = account_contact2.account_contact_id
where sv_inquiry.account_id=3441833 and
sv_inquiry.state not in ('Closed', 'Classified') ORDER BY
sv_inquiry.inquiry_id DESC
> 3. What is the ratio of database read activity vs. database writing
> activity?
There are more insert/update than read, because I'm doing table
synchronization
from an SQL Server database. Every 5 minutes I'm looking for change in SQL
Server
Database.
I've made some stats, and I found that without user acces, and only with the
replications
I get 2 millions query per day
> 4. What large tables in my database get queried simultaneously/together?
why this questions ?
> 5. Are my database writes bundled into transactions, or seperate?
bundle in transactions
> etc.
>
> Simply knowing the size of the database files isn't enough.
is it better like this ?
I do not agree with the advice to dedicate one disc to every table. Imagine 10 disks, 10 tables, and 10 users accessing the same table. This would mean 1 disk really busy and 9 idling all the way. If you use a RAID array, the random access is hopefully split to all disks, giving a much better performance. There are suggestions about direct disk access (using O_DIRECT file flags), versus using the OS' caching mechanisms. This is quite the same, in hardware. Today's hardware is designed to make the best of it, give it a chance! PostgreSQL's transaction logs are probably being written sequentially. This situation is different because the access pattern is predictable, dedicated disks might be useful since head movement is reduced to near zero, but if there's no high write volume it's wasted performance. If your system is so small that web and database are running on the same machine, you can consider page access being quite like table access, so I'd put it on the same big array. Your favorite *.html or *.php will be cached either. Swap space may be on a dedicated disk, but it's better for a server if swap is never used. Put enough RAM into that machine! Swapping is quite a desaster on a server. So you could put swap just where you like it: if your server is sane, it's never accessed under load. Same about OS files: is there really heavy traffic on them? There's a white paper at www.microsoft.com about tuning MSSQL 7.0. If read carefully, some advice will be applicable to PostgreSQL too. So as my general rule, valid for >99 % of users: use as much disks as possible in one big RAID array. Let the hardware do the data scattering, you'll be better off. For a production system, you will need disk redundancy either (my experience says one failed disk per year for 20 in use). Until your system is really heavily loaded, and you're using >10 disks, don't think about dedicated disks. If you want extra performance for no cost, you can put the most accessed partition on the outer cylinders of the disk array (probably corresponding to the outer cylinders of the disk) since throughput is highest there. Regards, Andreas
Andreas, > I do not agree with the advice to dedicate one disc to every table. Nobody gave any such advice. What are you talking about? -Josh
On Thu, 06 Feb 2003 22:43:23 +0100 in message <3E42D6FB.9000605@web.de>, Andreas Pflug <Andreas.Pflug@web.de> wrote: > I do not agree with the advice to dedicate one disc to every table. > > Imagine 10 disks, 10 tables, and 10 users accessing the same table. This > would mean 1 disk really busy and 9 idling all the way. If you use a > RAID array, the random access is hopefully split to all disks, giving a > much better performance. There are suggestions about direct disk access > (using O_DIRECT file flags), versus using the OS' caching mechanisms. > This is quite the same, in hardware. Today's hardware is designed to > make the best of it, give it a chance! Unfortunately, today's hardware still has rotational latency. You aren't goign to get much more than 300 seeks per sec onthe best single drive. Putting them together in a way that requires half to all of them to seek for a given read or writeis a performance killer. The only way around this is high end raid cards with backup batteries and ram. I've been doing some tests using pgbench (which aren't written up yet) on the topic of low budget performance. So far, usinglinux 2.4.20 md software raid where applicable, I've seen against a baseline of one ide disk: running on a rocketraid card (kernel thinks it's scsi) is faster than onboard controllers mirrored is negligably slower striped is much slower splitting WAL and Data on two drives gives a 40+% speed boost having data in ram cache is good for ~ 100% speed boost. Essentially, disk activity goes from evenly split reading and writingto all writing The only pg settings that show any correlation with pgbench performance are the # of WAL logs, generally corresponding tothe interval between flushing wal logs to the data store. Buffers don't change much over a 64-8192 range, Sort mem doesn'tchange much. (Note that that may be due to the query types in this benchmark. My app certainly needs the sortmem) As a somewhat on topic thought, it would be really neat to have a pci card that was one slot for ram, one for compact flash,a memory/ide controller and battery. Fill the ram and cf with identical sized units, and use it as a disk for WAL.if the power goes off, dump the ram to cf. Should be able to do thousands of writes per sec, effectivley moving the bottlenecksomewhere else. It's probably $20 worth of chips for the board, but it would probably sell for thousands. eric
In message <137343446.1167578047@[4.42.179.151]>, eric soroos writes:
As a somewhat on topic thought, it would be really neat to have a
pci card that was one slot for ram, one for compact flash, a
memory/ide controller and battery. Fill the ram and cf with
identical sized units, and use it as a disk for WAL. if the power
goes off, dump the ram to cf. Should be able to do thousands of
writes per sec, effectivley moving the bottleneck somewhere else.
It's probably $20 worth of chips for the board, but it would
probably sell for thousands.
How is this not provided by one of the many solid state disks?
http://www.storagesearch.com/ssd.html
I have never puchased one of these due to cost ($1 per MB or more) but
I always assumed this was a direct fit. The solid state disk people
claim so as well on their marketing literature. One of the drives
claims 700MB/s bandwidth.
-Seth Robertson
seth@sysd.com
In message <137343446.1167578047@[4.42.179.151]>, eric soroos writes:
As a somewhat on topic thought, it would be really neat to have a
pci card that was one slot for ram, one for compact flash, a
memory/ide controller and battery. Fill the ram and cf with
identical sized units, and use it as a disk for WAL. if the power
goes off, dump the ram to cf. Should be able to do thousands of
writes per sec, effectivley moving the bottleneck somewhere else.
It's probably $20 worth of chips for the board, but it would
probably sell for thousands.
How is this not provided by one of the many solid state disks?
http://www.storagesearch.com/ssd.html
I have never puchased one of these due to cost ($1 per MB or more) but
I always assumed this was a direct fit. The solid state disk people
claim so as well on their marketing literature. One of the drives
claims 700MB/s bandwidth.
-Seth Robertson
pgsql-performance@sysd.com
On Thu, 06 Feb 2003 19:07:30 -0500 in message <200302070007.h1707Us06536@winwood.sysdetect.com>, Seth Robertson <seth@sysd.com>wrote: > > In message <137343446.1167578047@[4.42.179.151]>, eric soroos writes: > > As a somewhat on topic thought, it would be really neat to have a > pci card that was one slot for ram, one for compact flash, a > memory/ide controller and battery. Fill the ram and cf with > identical sized units, and use it as a disk for WAL. if the power > goes off, dump the ram to cf. Should be able to do thousands of > writes per sec, effectivley moving the bottleneck somewhere else. > It's probably $20 worth of chips for the board, but it would > probably sell for thousands. > > How is this not provided by one of the many solid state disks? $20 worth of chips. The selling for thousands is what is provided by SSDs. A pci interface. > http://www.storagesearch.com/ssd.html Solid state disks are sold by companies targeting the military and don't have prices on the website. That scares me. The pci board would need about the same circuitry as a north+southbridge on an everyday motherboard, current chip cost isin the tens of dollars. Assuming that the demand is low, call it $100 or so for a completed board. support pc-100 (muchfaster than a pci bus) and cf type 2 (3? allow microdrives) and you've got something like 512mb of storage for $300.Which is almost exactly my WAL size. Compared to a raid card + 2 ide drives, price is a wash and performance is limitedby the pci bus. There's one board like this w/o the flash, but it's something like $500-1000 depending on ultimate capacity, without ram. > I have never puchased one of these due to cost ($1 per MB or more) but > I always assumed this was a direct fit. The solid state disk people > claim so as well on their marketing literature. One of the drives > claims 700MB/s bandwidth. I was seeing prices start in the low thousands and go up from there. Out of my budget I'm afraid. I'm in the process of spending~ $500 on a drive system. The desire is to switch from raid card + 4 ide drives to my wish card + a raid card +2 drives. (note that I'm talking in mirrored drives). I'm guessing that I could move the bottleneck to either the processoror pci bus if these cards existed. eric
On Thu, 6 Feb 2003, eric soroos wrote: > running on a rocketraid card (kernel thinks it's scsi) is faster than > onboard controllers How many transactions per second can you get on a single RAID or stripe system using this card? I found that the write performance for large writes on an Escalade 7850 was great, but I couldn't coax more than about 120 writes per second out of the thing in any configuration (even writing to separate disks in JBOD mode), which made it very disappointing for database use. (The individual disks on the controller, modern IBM 60 GB IDEs, could do about 90 writes per second.) cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
On Fri, 7 Feb 2003 10:17:43 +0900 (JST) in message <Pine.NEB.4.51.0302071013180.7356@angelic.cynic.net>, Curt Sampson <cjs@cynic.net>wrote: > On Thu, 6 Feb 2003, eric soroos wrote: > > > running on a rocketraid card (kernel thinks it's scsi) is faster than > > onboard controllers > > How many transactions per second can you get on a single RAID or stripe > system using this card? My current test setup is using a rocketraid 404 card, + 2 WD Caviar 80G SE (8 meg cache), and a 2 yr old 7200 rpm ide ibmon the mb controller channel as the os/log drive. The rocketraid is a 4 channel card, I'm going to fill the other twochannels with mirror drives. I'm _not_ using hw mirroring, since I want the flexibility of sw mirroring for now. (I don'tthink their driver supports breaking and reestablishing the mirror with live drive usage) This is on a single p3-733, 640 mb ram. Processor is apparently never redlined normally running at 50-75% as reported byvmstat. > I found that the write performance for large writes on an Escalade 7850 > was great, but I couldn't coax more than about 120 writes per second > out of the thing in any configuration (even writing to separate disks > in JBOD mode), which made it very disappointing for database use. (The > individual disks on the controller, modern IBM 60 GB IDEs, could do > about 90 writes per second.) Using a data/wal split, I see peaks around 135 t/s (500 transactions, 10 concurrent clients). Sustained (25000 transactions)that goes down to the 80 range due to the moving of data from the WAL to the data directories. these numbersare all with the kernel's data caches full of about 1/2 gig of data, I'm seeing 300 blks/s read and 3000 write Peaks for striped are @ 80, peaks for single are ~ 100, peaks for mirror are around 100. I'm curious if hw mirroring wouldhelp, as I am about a 4 disk raid 5. But I'm not likely to have the proper drives for that in time to do the testing,and I like the ablity to break the mirror for a backup. For comparison, that same system was doing 20-50 withoutthe extra 512 stick of ram and on the internal single drive. eric
Hello @ all, Josh wrote: >> With a large database you may even think about >> shifting individual tables or indexes to seperate disks. OK, I admit it was a bit provoking. It was my intention to stir things up a little bit ;-) IMHO, thinking about locatingdata on dedicated files is a waste of time on small servers. Let the hardware do the job for you! It is "good enough". Eric wrote: >>Unfortunately, today's hardware still has rotational latency. You aren't goign to get much >> more than 300 seeks per sec on the best single drive. Putting them together in a way that >> requires half to all of them to seek for a given read or write is a performance killer. >> The only way around this is high end raid cards with backup batteries and ram. You're right, 300 seeks is best you can expect from a state-of-the-art HD. But the average disk request will certainly notbe performed over several disks. Usual block size for RAID is 32kb or 64kb, while most requests will be only some kb (assumingyou're not doing full table scans all the time). Thus, the usual request will require only one disk to be accessedon read. This way, a 10-disk array will be capable of up to 3000 requests/second (if the controller allows this). Actually, I don't trust software RAID. If I'm talking about RAID, I mean mature RAID solutions, using SCSI or similar professionalequipment. More RAM, ideally with backup power, is desirable. For small servers, a RAID controller < 1000 $ usuallywill do. IDE RAID, uhm eh... I never did like it, and I doubt that IDE RAID controller are doing a good job optimizingfor this kind of traffic. IMHO, they are meant for workstation, leave them there. And remember, if we talk aboutaccess time, typical latency for a SCSI disk is half of fast IDE disks, giving double speed for typical DB access patterns.You may use IDE if speed means MB/s, but for us it's seeks/s. I don't think solid state disks are a way out (unless you don't know where to bury your money :-). Maybe the gurus can tellmore about PostgreSQL's caching, but for my opinion if enough RAM is available after some time all of the DB should bein cache eliminating the need to access the disks for read access. For writing, which is typically less than 10 % of totalload, an optimizing caching disk controller should be sufficient. Andreas
On Thu, 6 Feb 2003, eric soroos wrote: > > I found that the write performance for large writes on an Escalade > > 7850 was great, but I couldn't coax more than about 120 writes > > per second out of the thing in any configuration (even writing to > > separate disks in JBOD mode), which made it very disappointing for > > database use. (The individual disks on the controller, modern IBM 60 > > GB IDEs, could do about 90 writes per second.) > ... > Peaks for striped are @ 80, peaks for single are ~ 100, peaks for > mirror are around 100. I'm curious if hw mirroring would help, as I am > about a 4 disk raid 5. But I'm not likely to have the proper drives > for that in time to do the testing, and I like the ablity to break the > mirror for a backup. For comparison, that same system was doing 20-50 > without the extra 512 stick of ram and on the internal single drive. Hm. That's still very low (about the same as a single modern IDE drive). I'm looking for an IDE RAID controller that would get me up into the 300-500 reads/writes per second range, for 8K blocks. This should not be a problem when doing striping across eight disks that are each individually capable of about 90 random 8K reads/writes per second. (Depending on the size of the data area you're testing on, of course.) See http://randread.sourceforge.net for some tools to help measure this. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
On Fri, 7 Feb 2003, Andreas Pflug wrote: > Actually, I don't trust software RAID. If I'm talking about RAID, I > mean mature RAID solutions, using SCSI or similar professional > equipment. Funny you should mention that. A buddy running a "professional" level card had it mark two out of three drives in a RAID 5 bad and wouldn't let him reinsert the drives no matter what. Had to revert to backups. I'll take Linux's built in kernel raid any day over most pro cards. I've been using it in production for about 3 years and it is very mature and stable, and lets you do what you want to do (which can be good if your smart, but very bad if you do something dumb... :-) I've had good and bad experiences with pro grade RAID boxes and controllers, but I've honestly had nothing but good from linux's kernel level raid. Some early 2.0 stuff had some squirreliness that meant I had to actually reboot for some changes to take affect. Since the 2.2 kernel came out the md driver has been rock solid. I've not played with the volume manager yet, but I hear equally nice things about it. Keep in mind that a "hardware raid card" is nothing more than software raid burnt into ROM and stuffed on a dedicated card, there's no magic pixie dust that decrees doing such makes it a better or more reliable solution.
Folks,
I'm going to be setting up a Linux software RAID this weekend, and in my
research I cam across the following document:
http://www.hpl.hp.com/techreports/2002/HPL-2002-352.html
It says that Linux software RAID is slower than XP Software RAID on the
same hardware. If this is the case, wouldn't it follow that hardware RAID
has a really good chance of beating Linux software RAID? Or does the
problem that affects the software raid affect all Linux disk IO? I'm not
really knowledgeable enough to tell.
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
scott.marlowe
Sent: Friday, February 07, 2003 8:13 AM
To: Andreas Pflug
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] how to configure my new server
On Fri, 7 Feb 2003, Andreas Pflug wrote:
> Actually, I don't trust software RAID. If I'm talking about RAID, I
> mean mature RAID solutions, using SCSI or similar professional
> equipment.
Funny you should mention that. A buddy running a "professional" level
card had it mark two out of three drives in a RAID 5 bad and wouldn't let
him reinsert the drives no matter what. Had to revert to backups.
I'll take Linux's built in kernel raid any day over most pro cards. I've
been using it in production for about 3 years and it is very mature and
stable, and lets you do what you want to do (which can be good if your
smart, but very bad if you do something dumb... :-)
I've had good and bad experiences with pro grade RAID boxes and
controllers, but I've honestly had nothing but good from linux's kernel
level raid. Some early 2.0 stuff had some squirreliness that meant I had
to actually reboot for some changes to take affect. Since the 2.2 kernel
came out the md driver has been rock solid. I've not played with the
volume manager yet, but I hear equally nice things about it.
Keep in mind that a "hardware raid card" is nothing more than software
raid burnt into ROM and stuffed on a dedicated card, there's no magic
pixie dust that decrees doing such makes it a better or more reliable
solution.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Andreas, > Josh wrote: > >> With a large database you may even think about > >> shifting individual tables or indexes to seperate disks. > > OK, I admit it was a bit provoking. It was my intention to stir things up a > little bit ;-) IMHO, thinking about locating data on dedicated files is a > waste of time on small servers. Let the hardware do the job for you! It is > "good enough". Aha, by "large databases" I mean "several million records". In the odd case where you have a database which has one or two tables which are larger than the rest of the database combined, you can get a performance boost by putting those tables, and/or their indexes, on a seperate spindle. Frankly, for small servers, a pair of mirrored IDE drives is adequate. And, of course, if you have a RAID 1+0 controller, that's better than trying to directly allocate different files to different disks ... except the WAL log. > I don't think solid state disks are a way out (unless you don't know where > to bury your money :-). Maybe the gurus can tell more about PostgreSQL's > caching, but for my opinion if enough RAM is available after some time all > of the DB should be in cache eliminating the need to access the disks for > read access. For writing, which is typically less than 10 % of total load, > an optimizing caching disk controller should be sufficient. Depends on the database. I've worked on DBs where writes were 40% of all queries ... and 90% of the system resource load. For those databases, moving the WAL log to a RAMdisk might mean a big boost. Also, I'm currently working to figure out a solution for some 1U machines which don't have any *room* for an extra drive for WAL. A PCI ramdisk would be just perfect ... -- Josh Berkus Aglio Database Solutions San Francisco
I've always had very good performance with Linux's kernel raid, though I've never compared it to Windows, just to hardware raid cards running in linux. I can get aggregate reads of about 48 Megs a second on a pair of 10k 18 gig UW scsi drives in RAID1 config. I'm not saying there's no room for improvement, but for what I use it for, it gives very good performance. Some hardware cards will certainly be faster than the linux kernel raid software, but it's not a given that any hardware card WILL be faster. I'm quite certain that you could outrun most older cards using 33 MHz I960 for checksum calculations with a dual 2.4Ghz machine doing software. The only way to be sure is to test it. On Fri, 7 Feb 2003, Peter Darley wrote: > Folks, > I'm going to be setting up a Linux software RAID this weekend, and in my > research I cam across the following document: > http://www.hpl.hp.com/techreports/2002/HPL-2002-352.html > It says that Linux software RAID is slower than XP Software RAID on the > same hardware. If this is the case, wouldn't it follow that hardware RAID > has a really good chance of beating Linux software RAID? Or does the > problem that affects the software raid affect all Linux disk IO? I'm not > really knowledgeable enough to tell. > Thanks, > Peter Darley > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of > scott.marlowe > Sent: Friday, February 07, 2003 8:13 AM > To: Andreas Pflug > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] how to configure my new server > > > On Fri, 7 Feb 2003, Andreas Pflug wrote: > > > Actually, I don't trust software RAID. If I'm talking about RAID, I > > mean mature RAID solutions, using SCSI or similar professional > > equipment. > > Funny you should mention that. A buddy running a "professional" level > card had it mark two out of three drives in a RAID 5 bad and wouldn't let > him reinsert the drives no matter what. Had to revert to backups. > > I'll take Linux's built in kernel raid any day over most pro cards. I've > been using it in production for about 3 years and it is very mature and > stable, and lets you do what you want to do (which can be good if your > smart, but very bad if you do something dumb... :-) > > I've had good and bad experiences with pro grade RAID boxes and > controllers, but I've honestly had nothing but good from linux's kernel > level raid. Some early 2.0 stuff had some squirreliness that meant I had > to actually reboot for some changes to take affect. Since the 2.2 kernel > came out the md driver has been rock solid. I've not played with the > volume manager yet, but I hear equally nice things about it. > > Keep in mind that a "hardware raid card" is nothing more than software > raid burnt into ROM and stuffed on a dedicated card, there's no magic > pixie dust that decrees doing such makes it a better or more reliable > solution. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
> > Peaks for striped are @ 80, peaks for single are ~ 100, peaks for > > mirror are around 100. I'm curious if hw mirroring would help, as I am > > about a 4 disk raid 5. But I'm not likely to have the proper drives > > for that in time to do the testing, and I like the ablity to break the > > mirror for a backup. For comparison, that same system was doing 20-50 > > without the extra 512 stick of ram and on the internal single drive. Upon some further poking around, I have determined that there were procedural errors that make the data inconsistent. I believethat half of the ram cache may not have been in the state that I thought it was in for all of the tests. > Hm. That's still very low (about the same as a single modern IDE drive). > I'm looking for an IDE RAID controller that would get me up into the > 300-500 reads/writes per second range, for 8K blocks. This should not > be a problem when doing striping across eight disks that are each > individually capable of about 90 random 8K reads/writes per second. > (Depending on the size of the data area you're testing on, of course.) Running some further tests, I'm seeing software striping in the 125 tps peak/100 sustained range, which is about what I'mgetting from the split WAL/Data mode right now. I'm still seeing about 10% reads, so there's probably some more to begained with additional system ram. eric
scott.marlowe wrote: >I can get aggregate reads of about 48 Megs a second on a pair of 10k 18 >gig UW scsi drives in RAID1 config. I'm not saying there's no room for >improvement, but for what I use it for, it gives very good performance. > > > Scott, as most people talking about performance you mean throughput, but this is not the most important parameter for databases. Reading the comments of other users with software and IDE RAID, it seems to me that indeed these solutions are only good at this discipline. Another suggestion: You're right, a hardware RAID controller is nothing but a stripped down system that does noting more than a software RAID would do either. But this tends to be the discussion that Intels plays for years now. There were times when Intel said "don't need an intelligent graphics controller, just use a fast processor". Well, development went another direction, and it's good this way. Same with specialized controllers. They will take burden from the central processing unit, which can concentrate on the complicated things, not just getting some block from disk. Look at most Intel based servers. Often, CPU Speed is less than workstations CPUs, RAM technology one step behind. But they have sophisticated infrastructure for coprocessing. This is the way to speed things up, not pumping up the CPU. If you got two of three HDs bad in a RAID5 array, you're lost. That's the case for all RAID5 solutions, because the redundancy is just one disk. Better solutions will allow for spare disks that jump in as soon as one fails, hopefully it rebuilds before the next fails. Regards, Andreas
On Fri, 7 Feb 2003, Andreas Pflug wrote: > scott.marlowe wrote: > > >I can get aggregate reads of about 48 Megs a second on a pair of 10k 18 > >gig UW scsi drives in RAID1 config. I'm not saying there's no room for > >improvement, but for what I use it for, it gives very good performance. > > > > > > > Scott, > > as most people talking about performance you mean throughput, but this > is not the most important parameter for databases. Reading the comments > of other users with software and IDE RAID, it seems to me that indeed > these solutions are only good at this discipline. Well, I have run bonnie across it and several other options as well, and the RAID cards I've test (Mega RAID 428 kinda stuff, i.e. 2 or 3 years old) were no better than Linux at any of the tests. In some cases much slower. > Another suggestion: > You're right, a hardware RAID controller is nothing but a stripped down > system that does noting more than a software RAID would do either. But > this tends to be the discussion that Intels plays for years now. There > were times when Intel said "don't need an intelligent graphics > controller, just use a fast processor". Well, development went another > direction, and it's good this way. Same with specialized controllers. > They will take burden from the central processing unit, which can > concentrate on the complicated things, not just getting some block from > disk. Look at most Intel based servers. Often, CPU Speed is less than > workstations CPUs, RAM technology one step behind. But they have > sophisticated infrastructure for coprocessing. This is the way to speed > things up, not pumping up the CPU. Hey, I was an Amiga owner, I'm all in favor of moving off the CPU that you can. But, that's only a win if you're on a machine that will be CPU/interrupt bound. If the machine sits at 99% idle with most of the waiting being I/O, and it has 4 CPUs anyway, then you may or may not gain from moving the work onto another card. while SSL et. al. encryption is CPU intensive, but generally the XOring needed to be done for RAID checksums is very simple to do quickly on modern architectures, so there's no great gain the that department. I'd imagine the big gain would come from on board battery backed up write through / or behind cache memory. I think the fastest solutions have always been the big outboard boxes with the RAID built in, and the PCI cards tend to be also rans in comparison. But the one point I'm sure we'll agree on in this is that until you test it with your workload, you won't really know which is better, if either. > If you got two of three HDs bad in a RAID5 array, you're lost. That's > the case for all RAID5 solutions, because the redundancy is just one > disk. Better solutions will allow for spare disks that jump in as soon > as one fails, hopefully it rebuilds before the next fails. The problem was that all three drives were good. He moved the server, cable came half off, the card marked the drives as bad, and wouldn't accept them back until it had formatted them. This wasn't the first time I'd seen this kind of problem with RAID controllers either, as it had happened to me in testing one a few years earlier. Which is one of the many life experiences that makes me like backups so much.
scott.marlowe wrote: >The problem was that all three drives were good. He moved the server, >cable came half off, the card marked the drives as bad, and wouldn't >accept them back until it had formatted them. This wasn't the first time >I'd seen this kind of problem with RAID controllers either, as it had >happened to me in testing one a few years earlier. Which is one of the >many life experiences that makes me like backups so much. > > > Ok, had this kind of problems too. Everytime you stop and start a server, rest a second and say a little prayer :-) 80 % of HDs I've seen dying didn't start again after a regular maintenance power down. Or a controller finds some disk to be faulty for some nonreproduceable reason, until you kick the disk out of the array and rebuild it. Bad surprise, if TWO disks are considered bad. Stop and start again, and say a real GOOD prayer, then get out the backup tape... I had this especially with Adaptec controllers. I had to learn they cannot build reliable RAID, and they don't know what maintenance of products is either (three generations of AAA controllers in two years, all being incompatible, only partial supported for new OS. I'm cured!) Some years ago they bought PMD, which is a lot better. Andreas
pgsql-performance-owner@postgresql.org wrote:
> Objet : Re: [PERFORM] how to configure my new server
>
>
> pgsql-performance-owner@postgresql.org wrote:
>> Phillip,
>>
>> First, a disclaimer: my advice is without warranty whatsoever. You
>> want a warranty, you gotta pay me.
>>
>>> I've a new configuration for our web server
>>>
>>> Processor Processeur Intel Xeon 2.0 Ghz / 512 Ko de cache L2
>>> Memoiry 1 Go DDR SDRAM Disk1 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk2 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>> Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
>>
>> No RAID, though?
>
> Yes no Raid, but will could change soon
>
>>
>> Think carefully about which disks you put things on. Ideally, the
>> OS, the web files, the database files, the database log, and the swap
>> partition will all be on seperate disks. With a large database you
>> may even think about shifting individual tables or indexes to
>> seperate disks.
>
> how can I put indexes on a seperate disk ?
>
>>
>>> linux values:
>>> kernel.shmmni = 4096
>>> kernel.shmall = 32000000
>>> kernel.shmmax = 256000000
>>
>> These are probably too high, but I'm ready to speak authoritatively
>> on that.
> I took a look a the performance archive, and it's not possible to find
> real info on how to set these 3 values.
>
>>
>>> postgresql values:
>>> shared_buffers
>>> max_fsm_relations
>>> max_fsm_pages
>>> wal_buffers
>>> wal_files
>>> sort_mem
>>> vacuum_mem
>>
>> Please visit the archives for this list. Setting those values is a
>> topic of discussion for 50% of the threads, and there is yet no firm
>> agreement on good vs. bad values.
>>
>
> I'm surprised that there's no spreadsheet to calculate those values.
> There are many threads, but it seems that no one is able to find a
> rule to define values.
>
>
>> Also, you need to ask youself more questions before you start
>> setting values:
>>
>> 1. How many queries does my database handle per second or minute?
>> can't say now
>
>> 2. How big/complex are those queries?
> Not really complex and big as you can see
>
> SELECT qu_request.request_id, qu_request.type,
> qu_request_doc.ki_status, qu_request_doc.ki_subject,
> qu_request_doc.ki_description, qu_request_doc.ki_category,
> qu_request_doc.rn_description_us, qu_request_doc.rn_status_us,
> quad_config_nati.nati_version_extended FROM qu_request left join
> quad_config_nati on qu_request.quad_server_nati =
> quad_config_nati.nati_version left join qu_request_doc on
> qu_request.request_id = qu_request_doc.request_id
> WHERE qu_request.request_id = '130239'
>
>
> select sv_inquiry.inquiry_id, sv_inquiry.quad_account_inquiry_id
> ,to_char(sv_inquiry.change_dt, 'YYYY-MM-DD HH24:MI') as change_dt ,
> to_char(sv_inquiry.closed_dt, 'YYYY-MM-DD HH24:MI') as closed_dt
> ,sv_inquiry.state, sv_inquiry.priority, sv_inquiry.type,
> account_contact.dear as contact , account_contact2.dear as contact2,
> sv_inquiry.action, sv_inquiry.activity ,
> substr(sv_inq_txt.inquiry_txt, 1, 120) as inquiry_txt from sv_inquiry
> left join sv_inq_txt on sv_inquiry.inquiry_id = sv_inq_txt.inquiry_id
> left join account_contact on sv_inquiry.account_contact_id =
> account_contact.account_contact_id left join account_contact
> account_contact2
> on sv_inquiry.account_contact_id2 =
> account_contact2.account_contact_id where
> sv_inquiry.account_id=3441833 and sv_inquiry.state not in ('Closed',
> 'Classified') ORDER BY sv_inquiry.inquiry_id DESC
>
>
>> 3. What is the ratio of database read activity vs. database writing
>> activity?
> There are more insert/update than read, because I'm doing table
> synchronization
> from an SQL Server database. Every 5 minutes I'm looking for change
> in SQL Server
> Database.
> I've made some stats, and I found that without user acces, and only
> with the replications
> I get 2 millions query per day
>
>> 4. What large tables in my database get queried
>> simultaneously/together? why this questions ?
>
>> 5. Are my database writes bundled into transactions, or seperate?
>> bundle in transactions
>
>> etc.
>>
>> Simply knowing the size of the database files isn't enough.
>
> is it better like this ?
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
someone could come back to first request ?
Philip,
>
> someone could come back to first request ?
>
Insistent, aren't you? ;-)
> > Yes no Raid, but will could change soon
Adding RAID 1+0 could simplify your job enormously. It would prevent you
from having to figure out what to put on each disk. If it were my machine,
and I knew that the database was more important than the other services, I'd
build it like this:
Array 1: Disk 1: 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm
Disk2 : 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
Contains: Linux, Apache, Swap
Array 2:
Di:sk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
Contains: PostgreSQL and databases
Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm
Contains: Postgresql log, backup partition.
Alternately:
Put all of the above on one *smart* RAID5 controller, with on-controller
memory and battery. Might give you better performance considering your disk
setup.
> > how can I put indexes on a seperate disk ?
Move the index object (use the oid2name package in /contrib to find the index)
to a different location, and symlink it back to its original location. Make
sure that you REINDEX at maintainence time, and don't drop and re-create the
index, as that will have the effect of moving it back to the original
location.
> >>> linux values:
> >>> kernel.shmmni = 4096
> >>> kernel.shmall = 32000000
> >>> kernel.shmmax = 256000000
> > I took a look a the performance archive, and it's not possible to find
> > real info on how to set these 3 values.
Yeah. Personally, I just raise them until I stop getting error messages from
Postgres. Perhaps someone on the list could speak to the danger of setting
any of these values too high?
> > I'm surprised that there's no spreadsheet to calculate those values.
> > There are many threads, but it seems that no one is able to find a
> > rule to define values.
That's correct. There is no rule, because there are too many variables, and
the value of many of those variables is a matter of opinion. As an
*abbreviated* list:
1) Your processors and RAM; 2) Your drive setup and speed; 3) the frequency
of data reads; 4) the frequency of data writes; 5) the average complexity
of queries; 6) use of database procedures (functions) for DML; 7) your
maintainence plan (e.g. how often can you run VACUUM FULL?); 8) the expected
data population of tables (how many rows, how many tables); 9) your ability
to program for indexed vs. non-indexed queries; 10) do you do mass data
loads? ; 11) is the server being used for any other hihg-memory/networked
applications? ; 12) the expected number of concurrent users; 13) use of large
objects and/or large text fields; etc.
As a result, a set of values that work really well for me might crash your
database. It's an interactive process. Justin Clift started a project to
create an automated interactive postgresql.conf tuner, one that would
repeatedly test the speed of different queries against your database,
overnight while you sleep. However, he didn't get very far and I haven't
had time to help.
> >> 1. How many queries does my database handle per second or minute?
> >> can't say now
This has a big influence on your desired sort_mem and shared_buffer settings.
Make some estimates.
> >>
> >> 2. How big/complex are those queries?
> >
> > Not really complex and big as you can see
OK, so nothing that would require you to really jack up your sort or shared
memory beyond levels suggested by other factors. However, you don't say how
many rows these queries usually return, which has a substantial effect on
desired sort_mem.
A good, if time-consuming, technique for setting sort_mem is to move it up and
down (from, say 512 to 4096) seeing at what level your biggest meanest
queries slow down noticably ... and then set it to one level just above that.
> > There are more insert/update than read, because I'm doing table
> > synchronization
> > from an SQL Server database. Every 5 minutes I'm looking for change
> > in SQL Server
> > Database.
> > I've made some stats, and I found that without user acces, and only
> > with the replications
> > I get 2 millions query per day
In that case, making sure that your WAL files (the pg_xlog directory) is
located on a seperate drive which *does nothing else* during normal operation
is your paramount concern for performance. You'll also need to carefully
prune your indexes down to only the ones you really need to avoid slowing
your inserts and updates.
> >> 4. What large tables in my database get queried
> >> simultaneously/together? why this questions ?
If you're not using RAID, it would affect whether you should even consider
moving a particular table or index to a seperate drive. If you have two
tables, each of which is 3 million records, and they are quried joined
together in 50% of data reads, then one of those tables is a good candidate
for moving to another drive.
Good luck!
--
Josh Berkus
Aglio Database Solutions
San Francisco
pgsql-performance-owner@postgresql.org wrote: > Philip, > >> >> someone could come back to first request ? >> > > Insistent, aren't you? ;-) > >>> Yes no Raid, but will could change soon > > Adding RAID 1+0 could simplify your job enormously. It would > prevent you from having to figure out what to put on each disk. If > it were my machine, and I knew that the database was more important > than the other services, I'd build it like this: > > Array 1: Disk 1: 18Go Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk2 : 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 > Ktpm > > Contains: Linux, Apache, Swap > > Array 2: > Di:sk3 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > Disk4 18Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > > Contains: PostgreSQL and databases > > Disk5 36Go Hot Plug Ultra 3 (Ultra 160) SCSI 15 Ktpm > > Contains: Postgresql log, backup partition. > > Alternately: > Put all of the above on one *smart* RAID5 controller, with > on-controller memory and battery. Might give you better performance > considering your disk setup. > >>> how can I put indexes on a seperate disk ? > > Move the index object (use the oid2name package in /contrib to find > the index) to a different location, and symlink it back to its > original location. Make sure that you REINDEX at maintainence time, > and don't drop and re-create the index, as that will have the effect > of moving it back to the original location. > >>>>> linux values: >>>>> kernel.shmmni = 4096 >>>>> kernel.shmall = 32000000 >>>>> kernel.shmmax = 256000000 >>> I took a look a the performance archive, and it's not possible to >>> find real info on how to set these 3 values. > > Yeah. Personally, I just raise them until I stop getting error > messages from Postgres. Perhaps someone on the list could speak to > the danger of setting any of these values too high? > >>> I'm surprised that there's no spreadsheet to calculate those values. >>> There are many threads, but it seems that no one is able to find a >>> rule to define values. > > That's correct. There is no rule, because there are too many > variables, and the value of many of those variables is a matter of > opinion. As an > *abbreviated* list: > 1) Your processors and RAM; 2) Your drive setup and speed; 3) the > frequency of data reads; 4) the frequency of data writes; 5) the > average complexity of queries; 6) use of database procedures > (functions) for DML; 7) your maintainence plan (e.g. how often can > you run VACUUM FULL?); 8) the expected data population of tables > (how many rows, how many tables); 9) your ability to program for > indexed vs. non-indexed queries; 10) do you do mass data loads? ; > 11) is the server being used for any other hihg-memory/networked > applications? ; 12) the expected number of concurrent users; 13) use > of large objects and/or large text fields; etc. > > As a result, a set of values that work really well for me might crash > your database. It's an interactive process. Justin Clift started > a project to create an automated interactive postgresql.conf tuner, > one that would repeatedly test the speed of different queries against > your database, overnight while you sleep. However, he didn't get > very far and I haven't had time to help. > >>>> 1. How many queries does my database handle per second or minute? >>>> can't say now > > This has a big influence on your desired sort_mem and shared_buffer > settings. Make some estimates. > >>>> >>>> 2. How big/complex are those queries? >>> >>> Not really complex and big as you can see > > OK, so nothing that would require you to really jack up your sort or > shared memory beyond levels suggested by other factors. However, you > don't say how many rows these queries usually return, which has a > substantial effect on desired sort_mem. > > A good, if time-consuming, technique for setting sort_mem is to move > it up and down (from, say 512 to 4096) seeing at what level your > biggest meanest queries slow down noticably ... and then set it to > one level just above that. > >>> There are more insert/update than read, because I'm doing table >>> synchronization from an SQL Server database. Every 5 minutes I'm >>> looking for change in SQL Server Database. >>> I've made some stats, and I found that without user acces, and only >>> with the replications I get 2 millions query per day > > In that case, making sure that your WAL files (the pg_xlog directory) > is located on a seperate drive which *does nothing else* during > normal operation is your paramount concern for performance. You'll > also need to carefully prune your indexes down to only the ones you > really need to avoid slowing your inserts and updates. > >>>> 4. What large tables in my database get queried >>>> simultaneously/together? why this questions ? > > If you're not using RAID, it would affect whether you should even > consider moving a particular table or index to a seperate drive. If > you have two tables, each of which is 3 million records, and they are > quried joined together in 50% of data reads, then one of those tables > is a good candidate for moving to another drive. > > Good luck! thanks very much
Josh Berkus wrote: > > > how can I put indexes on a seperate disk ? > > Move the index object (use the oid2name package in /contrib to find the index) > to a different location, and symlink it back to its original location. Make > sure that you REINDEX at maintainence time, and don't drop and re-create the > index, as that will have the effect of moving it back to the original > location. I believe reindex will create a new file and hence remove the symlink, at least in 7.3. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian wrote:
> Josh Berkus wrote:
>
>>>>how can I put indexes on a seperate disk ?
>>
>>Move the index object (use the oid2name package in /contrib to find the index)
>>to a different location, and symlink it back to its original location. Make
>>sure that you REINDEX at maintainence time, and don't drop and re-create the
>>index, as that will have the effect of moving it back to the original
>>location.
>
> I believe reindex will create a new file and hence remove the symlink,
> at least in 7.3.
Yep, it's a complete pain. You can't stabily have indexes moved to
different drives, or even different partitions on the same drive (i.e.
fastest disk area), as they're recreated in the default data location at
index time.
This was one of the things I was hoping would _somehow_ be solved with
namespaces, as in high transaction volume environments it would be nice
to have frequently used indexes [be practical] on separate drives from
the data. At present, that's not very workable.
Regards and best wishes,
Justin Clift
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi