On Tue, Sep 22, 2009 at 9:54 AM, Shiva Raman <raman.shivag@xxxxxxxxx> wrote: > Dear all > > I am having a problem of high cpu loads in my postgres server during peak > time. Following are the > details of my setup (details as per the postgres wiki) . > > * PostgreSQL version > o Run "select pg_version();" in psql or PgAdmin III and provide the > full, exact output. > > > clusternode2:~ # rpm -qa | grep postgres > postgresql-devel-8.1.9-1.2 > postgresql-8.1.9-1.2 > postgresql-docs-8.1.9-1.2 > postgresql-server-8.1.9-1.2 > postgresql-libs-64bit-8.1.9-1.2 > postgresql-libs-8.1.9-1.2 > postgresql-jdbc-8.1-12.2 > postgresql-contrib-8.1.9-1.2 > > > * A description of what you are trying to achieve and what results you > expect. > > To keep the CPU Load below 10 , Now during peak times the load is nearing to > 40 > At that time , it is not possible to access the data. > > * The EXACT text of the query you ran, if any > > > * The EXACT output of that query if it's short enough to be reasonable to > post > o If you think the output is wrong, what you think should've been > produced instead > > * The EXACT error message you get, if there is one > > As of now , i am unable to locate the exact query, the load shoots up > abnormally during > peak time is the main problem . > > > * What program you're using to connect to PostgreSQL > > Jakarta Tomcat - Struts with JSP > > > * What version of the ODBC/JDBC driver you're using, if any > > postgresql-jdbc-8.1-12.2 > > * What you were doing when the error happened / how to cause the error. > Describe in as much detail as possible, step by step, including command > lines, SQL output, etc. > > When certain tables with more than 3 lakh items are concurrently accessed by > more than 300 > users, the CPU load shoots up . > > * Is there anything remotely unusual in the PostgreSQL server logs? > o On Windows these are in your data directory. On a default > PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log > (assuming you're using 8.4) > > The log file /var/log/postgresql has no data . > > o On Linux this depends a bit on distro, but you'll usually find > them in /var/log/postgresql/. > * Operating system and version > o Linux users: > + Linux distro and version > + Kernel details (run "uname -a" on the terminal) > > SLES 10 SP3 > clusternode2:~ # uname -a > Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007 > ppc64 ppc64 ppc64 GNU/Linux > > > > * What kind of hardware you have. > o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 > Duo" > o Amount and size of RAM installed, eg "2GB RAM" > > High Availability Cluster with two IBM P Series Server and one DS4700 > Storage > > IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card , 36 MB L3 > Cache ,16 GB of RAM, > 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . > > > > o Storage details (important for performance and corruption > questions) > + Do you use a RAID controller? If so, what type of > controller? eg "3Ware Escalade 8500-8" > # Does it have a battery backed cache module? > # Is write-back caching enabled? > + Do you use software RAID? If so, what software and what > version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686 > REGPARM gcc-4.1". > # In the case of Linux software RAID you can get the > details from the "modinfo md_mod" command > + Is your PostgreSQL database on a SAN? > # Who made it, what kind, etc? Provide what details you > can. > + How many hard disks are connected to the system and what > types are they? You need to say more than just "6 disks". At least give > maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS > disks". > + How are your disks arranged for storage? Are you using > RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks / > disk sets? What file system(s) are in use? > # eg: "Two disks in RAID 1, with all PostgreSQL data > and programs stored on one ext3 file system." > # eg: "4 disks in RAID 5 holding the pg data directory > on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the > temporary tablespace, and the sort scratch space, also on ext3.". > # eg: "Default Windows install of PostgreSQL" > + In case of corruption data reports: > # Have you had any unexpected power loss lately? > # Have you run a file system check? (chkdsk / fsck) > # Are there any error messages in the system logs? > (unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control > Panel -> Administrative Tools ) > > > IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10) > Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage partitions (One holding > Jakarata tomcat > application server and other holding Postgresql Database) . > Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN . > Hard disk rotational speed is 73 GB 15K IBM 2 GB Fibre channel > > No power loss, filesystem check also fine, No errors on /var/log/syslog > > Following is the output of TOP command during offpeak time. > > > top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37 > Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie > Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si, > 42.9%st > Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers > Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > > 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 postmaster > > 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 postmaster > > 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 postmaster > > 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 postmaster > > 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 postmaster > > 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 postmaster > > 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 postmaster > > 22485 postgres 16 0 2439m 230m 222m R 7 1.5 0:05.72 postmaster > > 22481 postgres 15 0 2436m 175m 169m S 7 1.1 0:04.44 postmaster > > 22435 postgres 17 0 2438m 371m 361m R 6 2.4 1:17.92 postmaster > > 22440 postgres 17 0 2445m 497m 483m R 5 3.2 1:44.50 postmaster > > 22486 postgres 17 0 2432m 84m 81m R 4 0.5 0:00.76 postmaster > > 3 root 34 19 0 0 0 R 0 0.0 1:47.50 ksoftirqd/0 > > 4726 root 15 0 29540 8776 3428 S 0 0.1 140:02.98 X > > 24950 root 15 0 0 0 0 S 0 0.0 0:30.96 pdflush > > 1 root 16 0 812 316 280 S 0 0.0 0:13.29 init > > 2 root RT 0 0 0 0 S 0 0.0 0:01.46 migration/0 > > 4 root RT 0 0 0 0 S 0 0.0 0:00.78 migration/1 > > 5 root 34 19 0 0 0 S 0 0.0 1:36.79 ksoftirqd/1 > > 6 root RT 0 0 0 0 S 0 0.0 0:01.46 migration/2 > > 7 root 34 19 0 0 0 R 0 0.0 1:49.83 ksoftirqd/2 > > 8 root RT 0 0 0 0 S 0 0.0 0:00.79 migration/3 > > 9 root 34 19 0 0 0 S 0 0.0 1:38.18 ksoftirqd/3 > > 10 root 10 -5 0 0 0 S 0 0.0 1:02.11 events/0 > > 11 root 10 -5 0 0 0 S 0 0.0 1:03.27 events/1 > > 12 root 10 -5 0 0 0 S 0 0.0 1:01.76 events/2 > > 13 root 10 -5 0 0 0 S 0 0.0 1:02.29 events/3 > > 14 root 10 -5 0 0 0 S 0 0.0 0:00.01 khelper > > 1016 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread > > 1054 root 10 -5 0 0 0 S 0 0.0 0:03.08 kblockd/0 > > 1055 root 10 -5 0 0 0 S 0 0.0 0:02.83 kblockd/1 > > 1056 root 10 -5 0 0 0 S 0 0.0 0:03.19 kblockd/2 > > > > > The CPU Load shoots upto 40 during peak time. > > Following is my postgresql.conf (without comments) > > hba_file = '/var/lib/pgsql/data/pg_hba.conf' > listen_addresses = '*' > port = 5432 > max_connections = 1800 > shared_buffers = 300000 > max_fsm_relations = 1000 > effective_cache_size = 200000 > log_destination = 'stderr' > redirect_stderr = on > log_rotation_age = 0 > log_rotation_size = 10240 > silent_mode = onlog_line_prefix = '%t %d %u ' > autovacuum = on > datestyle = 'iso, dmy' > lc_messages = 'en_US.UTF-8' > lc_monetary = 'en_US.UTF-8' > lc_numeric = 'en_US.UTF-8' > lc_time = 'en_US.UTF-8' > > User Access > Total Number of Users is 500 > Maximum number of Concurrent users will be 500 during peak time > Off Peak time the maximum number of concurrent user will be around 150 to > 200. > > > Please let me know your suggestions to improve the performance. The very first step is to determine if you are cpu bound or i/o bound. You need to monitor top or vmstat during high load period and report the results here. Is the DS4700 direct attached? Sometimes using a SAN can throw the iowait numbers off a bit. I bet you are simply underpowered in I/O department. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
