Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
On Mon, Mar 20, 2006 at 02:15:22PM -0500, Vivek Khera wrote: I think FreeBSD has a hard upper limit on the total ram it will use for disk cache. I haven't been able to get reliable, irrefutable, answers about it, though. It does not. Any memory in the inactive queue is effectively your 'disk cache'. Pages start out in the active queue, and if they aren't used fairly frequently they will move into the inactive queue. From there they will be moved to the cache queue, but only if the cache queue falls below a certain threshold, because in order to go into the cache queue the page must be marked clean, possibly incurring a write to disk. AFAIK pages only go into the free queue if they have been completely released by all objects that were referencing them, so it's theoretically posisble for that queue to go to 0. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
On Mar 17, 2006, at 5:11 PM, Kenji Morishige wrote: In summary, my questions: 1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance? FreeBSD 6.x will definitely get you improvements. Many speedup improvements have been made to both the generic disk layer and the specific drivers. However, the current best of breed RAID controller is the LSI 320-x (I use 320-2X). I have one box into which this card will not fit (Thanks Sun, for making a box with only low-profile slots!) so I use an Adaptec 2230SLP card in it. Testing shows it is about 80% speed of a LSI 320-2x on sequential workload (load DB, run some queries, rebuild indexes, etc.) If you do put on FreeBSD 6, I'd love to see the output of diskinfo - v -t on your RAID volume(s). 2. Should I change SCSI controller config to use RAID 10 instead of 5? I use RAID10. 3. Why isn't postgres using all 4GB of ram for at least caching table for reads? I think FreeBSD has a hard upper limit on the total ram it will use for disk cache. I haven't been able to get reliable, irrefutable, answers about it, though. 4. Are there any other settings in the conf file I could try to tweak? I like to bump up the checkpoint segments to 256. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
If you do put on FreeBSD 6, I'd love to see the output of diskinfo - v -t on your RAID volume(s). Not directly related ... i have a HP dl380 g3 with array 5i controlled (1+0), these are my results [...] is this good enough? Is that on a loaded box or a mostly quiet box? Those number seem rather low for my tastes. For comparison, here are numbers from a Dell 1850 with a built-in PERC 4e/Si RAID in a two disk mirror. All numbers below are on mostly or totally quiet disk systems. amrd0 512 # sectorsize 73274490880 # mediasize in bytes (68G) 143114240 # mediasize in sectors 8908# Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 0.756718 sec =3.027 msec Half stroke: 250 iter in 0.717824 sec =2.871 msec Quarter stroke: 500 iter in 1.972368 sec =3.945 msec Short forward:400 iter in 1.193179 sec =2.983 msec Short backward: 400 iter in 1.322440 sec =3.306 msec Seq outer: 2048 iter in 0.271402 sec =0.133 msec Seq inner: 2048 iter in 0.271151 sec =0.132 msec Transfer rates: outside: 102400 kbytes in 1.080339 sec =94785 kbytes/sec middle:102400 kbytes in 1.166021 sec =87820 kbytes/sec inside:102400 kbytes in 1.461498 sec =70065 kbytes/sec And for the *real* disks In the following two cases, I used a Dell 1425SC with 1GB RAM and connected the controllers to the same Dell PowerVault 14 disk U320 array (one controller at a time, obviously). For each controller each pair of the mirror was on the opposite channel of the controller for optimal speed. disk 0 is a RAID1 of two drives, and disk 1 is a RAID10 of the remaining 12 drives. All running FreeBSD 6.0 RELEASE. First I tested the Adaptec 2230SLP and got these: aacd0 512 # sectorsize 36385456128 # mediasize in bytes (34G) 71065344# mediasize in sectors 4423# Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 2.288389 sec =9.154 msec Half stroke: 250 iter in 1.657302 sec =6.629 msec Quarter stroke: 500 iter in 2.756597 sec =5.513 msec Short forward:400 iter in 1.205275 sec =3.013 msec Short backward: 400 iter in 1.249310 sec =3.123 msec Seq outer: 2048 iter in 0.412770 sec =0.202 msec Seq inner: 2048 iter in 0.428585 sec =0.209 msec Transfer rates: outside: 102400 kbytes in 1.204412 sec =85021 kbytes/sec middle:102400 kbytes in 1.347325 sec =76002 kbytes/sec inside:102400 kbytes in 2.036832 sec =50274 kbytes/sec aacd1 512 # sectorsize 218307231744# mediasize in bytes (203G) 426381312 # mediasize in sectors 26541 # Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 0.856699 sec =3.427 msec Half stroke: 250 iter in 1.475651 sec =5.903 msec Quarter stroke: 500 iter in 2.693270 sec =5.387 msec Short forward:400 iter in 1.127831 sec =2.820 msec Short backward: 400 iter in 1.216876 sec =3.042 msec Seq outer: 2048 iter in 0.416340 sec =0.203 msec Seq inner: 2048 iter in 0.436471 sec =0.213 msec Transfer rates: outside: 102400 kbytes in 1.245798 sec =82196 kbytes/sec middle:102400 kbytes in 1.169033 sec =87594 kbytes/sec inside:102400 kbytes in 1.390840 sec =73625 kbytes/sec And the LSI 320-2X card: amrd0 512 # sectorsize 35999711232 # mediasize in bytes (34G) 70311936# mediasize in sectors 4376# Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. Seek times: Full stroke: 250 iter in 0.737130 sec =2.949 msec Half stroke: 250 iter in 0.694498 sec =2.778 msec Quarter stroke: 500 iter in 2.040667 sec =4.081 msec Short forward:400 iter in 1.418592 sec =3.546 msec Short backward: 400 iter in 0.896076 sec =2.240 msec Seq outer: 2048 iter in 0.292390 sec =0.143 msec Seq inner: 2048
Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
On Mar 20, 2006, at 6:04 PM, Miguel wrote: Umm, in my box i see better seektimes but worst transfer rates, does it make sense? i think i have something wrong, the question i cant answer is what tunning am i missing? Well, I forgot to mention I have 15k RPM disks, so the transfers should be faster. I did no tuning to the disk configurations. I think your controller is either just not supported well in FreeBSD, or is bad in general... I *really* wish LSI would make a low profile card that would fit in a Sun X4100... as it stands the only choice for dual channel cards is the adaptec 2230SLP... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
About a year ago we decided to migrate our central database that powers various intranet tools from MySQL to PostgreSQL. We have about 130 tables and about 10GB of data that stores various status information for a variety of services for our intranet. We generally have somewhere between 150-200 connections to the database at any given time and probably anywhere between 5-10 new connections being made every second and about 100 queries per second. Most of the queries and transactions are very small due to the fact that the tools were designed to work around the small functionality of MySQL 3.23 DB. Our company primarily uses FreeBSD and we are stuck on FreeBSD 4.X series due to IT support issues, but I believe I may be able to get more performance out of our server by reconfiguring and setting up the postgresql.conf file up better. The performance is not as good as I was hoping at the moment and it seems as if the database is not making use of the available ram. snapshot of active server: last pid: 5788; load averages: 0.32, 0.31, 0.28 up 127+15:16:08 13:59:24 169 processes: 1 running, 168 sleeping CPU states: 5.4% user, 0.0% nice, 9.9% system, 0.0% interrupt, 84.7% idle Mem: 181M Active, 2632M Inact, 329M Wired, 179M Cache, 199M Buf, 81M Free Swap: 4096M Total, 216K Used, 4096M Free PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 14501 pgsql 2 0 254M 242M select 2 76:26 1.95% 1.95% postgre 5720 root 28 0 2164K 1360K CPU0 0 0:00 1.84% 0.88% top 5785 pgsql 2 0 255M 29296K sbwait 0 0:00 3.00% 0.15% postgre 5782 pgsql 2 0 255M 11900K sbwait 0 0:00 3.00% 0.15% postgre 5772 pgsql 2 0 255M 11708K sbwait 2 0:00 1.54% 0.15% postgre Here is my current configuration: Dual Xeon 3.06Ghz 4GB RAM Adaptec 2200S 48MB cache 4 disks configured in RAID5 FreeBSD 4.11 w/kernel options: options SHMMAXPGS=65536 options SEMMNI=256 options SEMMNS=512 options SEMUME=256 options SEMMNU=256 options SMP # Symmetric MultiProcessor Kernel options APIC_IO # Symmetric (APIC) I/O The OS is installed on the local single disk and postgres data directory is on the RAID5 partition. Maybe Adaptec 2200S RAID5 performance is not as good as the vendor claimed. It was my impression that the raid controller these days are optimized for RAID5 and going RAID10 would not benefit me much. Also, I may be overlooking a postgresql.conf setting. I have attached the config file. In summary, my questions: 1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance? 2. Should I change SCSI controller config to use RAID 10 instead of 5? 3. Why isn't postgres using all 4GB of ram for at least caching table for reads? 4. Are there any other settings in the conf file I could try to tweak? # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_address, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. # data_directory = 'ConfigDir' # use data in another directory # hba_file = 'ConfigDir/pg_hba.conf'# the host-based authentication file # ident_file = 'ConfigDir/pg_ident.conf' # the IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. # external_pid_file = '(none)' # write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings -
Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
Here is my current configuration: Dual Xeon 3.06Ghz 4GB RAM Adaptec 2200S 48MB cache 4 disks configured in RAID5 FreeBSD 4.11 w/kernel options: options SHMMAXPGS=65536 options SEMMNI=256 options SEMMNS=512 options SEMUME=256 options SEMMNU=256 options SMP # Symmetric MultiProcessor Kernel options APIC_IO # Symmetric (APIC) I/O The OS is installed on the local single disk and postgres data directory is on the RAID5 partition. Maybe Adaptec 2200S RAID5 performance is not as good as the vendor claimed. It was my impression that the raid controller these days are optimized for RAID5 and going RAID10 would not benefit me much. I don't know whether 'systat -vmstat' is available on 4.x, if so try to issue the command with 'systat -vmstat 1' for 1 sec. updates. This will (amongst much other info) show how much disk-transfer you have. Also, I may be overlooking a postgresql.conf setting. I have attached the config file. You could try to lower shared_buffers from 3 to 16384. Setting this value too high can in some cases be counterproductive according to doc's I read. Also try to lower work_mem from 16384 to 8192 or 4096. This setting is for each sort, so it does become expensive in terms of memory when many sorts are being carried out. It does depend on the complexity of your sorts of course. Try to do a vacuum analyse in your crontab. If your aliases-file is set up correctly mails generated by crontab will be forwarded to a human being. I have the following in my (root) crontab (and mail to root forwarded to me): time /usr/local/bin/psql -d dbname -h dbhost -U username -c vacuum analyse verbose; In summary, my questions: 1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance? Going to 6.x would probably increase overall performance, but you have to try it out first. Many people report increased performance just by upgrading, some report that it grinds to a halt. But SMP-wise 6.x is a more mature release than 4.x is. Changes to the kernel from being giant-locked in 4.x to be fine-grained locked started in 5.x and have improved in 6.x. The disk- and network-layer should behave better. Linux, don't know. If your expertise is in FreeBSD try this first and then move to Linux (or Solaris 10) if 6.x does not meet your expectations. 3. Why isn't postgres using all 4GB of ram for at least caching table for reads? I guess it's related to the usage of the i386-architecture in general. If the zzeons are the newer noconas you can try the amd64-port instead. This can utilize more memory (without going through PAE). 4. Are there any other settings in the conf file I could try to tweak? max_fsm_pages and max_fsm_relations. You can look at the bottom of vacuum analyze and increase the values: INFO: free space map: 153 relations, 43445 pages stored; 45328 total pages needed Raise max_fsm_pages so it meet or exceed 'total pages needed' and max_fsm_relations to relations. This is finetuning though. It's more important to set work- and maintenance-mem correct. hth Claus ---(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] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
Kenji Morishige [EMAIL PROTECTED] writes: ... We generally have somewhere between 150-200 connections to the database at any given time and probably anywhere between 5-10 new connections being made every second and about 100 queries per second. Most of the queries and transactions are very small due to the fact that the tools were designed to work around the small functionality of MySQL 3.23 DB. You should think seriously about putting in some sort of connection-pooling facility. Postgres backends aren't especially lightweight things; the overhead involved in forking a process and then getting its internal caches populated etc. is significant. You don't want to be doing that for one small query, at least not if you're doing so many times a second. it seems as if the database is not making use of the available ram. Postgres generally relies on the kernel to do the bulk of the disk caching. Your shared_buffers setting of 3 seems quite reasonable to me; I don't think you want to bump it up (not much anyway). I'm not too familiar with FreeBSD and so I'm not clear on what Inact is: Mem: 181M Active, 2632M Inact, 329M Wired, 179M Cache, 199M Buf, 81M Free Swap: 4096M Total, 216K Used, 4096M Free If Inact covers disk pages cached by the kernel then this is looking reasonably good. If it's something else then you got a problem, but fixing it is a kernel issue not a database issue. #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each You almost certainly need to bump this way up. 2 is enough to cover dirty pages in about 200MB of database, which is only a fiftieth of what you say your disk footprint is. Unless most of your data is static, you're going to be suffering severe table bloat over time due to inability to recycle free space properly. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
4. Are there any other settings in the conf file I could try to tweak? One more thing :-) I stumbled over this setting, this made the db (PG 7.4.9) make use of the index rather than doing a sequential scan and it reduced a query from several minutes to some 20 seconds. random_page_cost = 2 (original value was 4). Another thing you ought to do is to to get the four-five most used queries and do an explain analyze in these. Since our website wasn't prepared for this type of statistics I simply did a tcpdump, grep'ed all select's, sorted them and sorted them unique so I could see which queries were used most. regards Claus ---(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] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
Thanks guys, I'm studying each of your responses and am going to start to experiement. Unfortunately, I don't have another box with similar specs to do a perfect experiment with, but I think I'm going to go ahead and open a service window to ungrade the box to FBSD6.0 and apply some other changes. It also gives me the chance to go from 8.0.1 to 8.1 series which I been wanting to do as well. Thanks guys and I will see if any of your suggestions make a noticable difference. I also have been looking at log result of slow queries and making necessary indexes to make those go faster. -Kenji On Sat, Mar 18, 2006 at 12:29:17AM +0100, Claus Guttesen wrote: 4. Are there any other settings in the conf file I could try to tweak? One more thing :-) I stumbled over this setting, this made the db (PG 7.4.9) make use of the index rather than doing a sequential scan and it reduced a query from several minutes to some 20 seconds. random_page_cost = 2 (original value was 4). Another thing you ought to do is to to get the four-five most used queries and do an explain analyze in these. Since our website wasn't prepared for this type of statistics I simply did a tcpdump, grep'ed all select's, sorted them and sorted them unique so I could see which queries were used most. regards Claus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
