Обсуждение: performance problems ... 100 cpu utilization
Hi, I've got a java based web application that uses PostgreSQL 8.0.2. PostgreSQL runs on its own machine with RHEL 3, ia32e kernel, dual Xeon processor, 4 Gb ram. The web application runs on a seperate machine from the database. The application machine has three tomcat instances configured to use 64 database connections each using DBCP for pooling. Most of the data access is via Hibernate. The database itself is about 100 meg in size. We're perf testing the application with Loadrunner. At about 500 virtual users hitting the web application, the cpu utilization on the database server is at 100%, PostgreSQL is on its knees. The memory usage isn't bad, the I/O isn't bad, only the CPU seems to be maxed out. checking the status of connections at this point ( ps -eaf | grep "postgres:") where the CPU is maxed out I saw this: 127 idle 12 bind 38 parse 34 select Hibernate is used in the application and unfortunately this seems to cause queries not to get logged. (see http://archives.postgresql.org/pgsql-admin/2005-05/msg00241.php) I know there has been discussion about problems on Xeon MP systems. Is this what we are running into? Or is something else going on? Is there other information I can provide that might help determine what is going on? Here are the postgresql.conf settings: # The maximum number of connections. max_connections = 256 # Standard performance-related settings. shared_buffers = 16384 max_fsm_pages = 200000 max_fsm_relations = 10000 fsync = false wal_sync_method = fsync wal_buffers = 32 checkpoint_segments = 6 effective_cache_size = 38400 random_page_cost = 2 work_mem = 16384 maintenance_work_mem = 16384 # TODO - need to investigate these. commit_delay = 0 commit_siblings = 5 max_locks_per_transaction = 512
"Dennis" <dennis@works4me.com> writes > > checking the status of connections at this point ( ps -eaf | grep > "postgres:") where the CPU is maxed out I saw this: > > 127 idle > 12 bind > 38 parse > 34 select > Are you sure 100% CPU usage is solely contributed by Postgresql? Also, from the ps status you list, I can hardly see that's a problem because of problem you mentioned below. > > I know there has been discussion about problems on Xeon MP systems. Is > this what we are running into? Or is something else going on? Is there > other information I can provide that might help determine what is going on? > Here is a talk about Xeon-SMP spinlock contention problem: http://archives.postgresql.org/pgsql-performance/2005-05/msg00441.php Regards, Qingqing
Qingqing Zhou wrote: >Are you sure 100% CPU usage is solely contributed by Postgresql? Also, from >the ps status you list, I can hardly see that's a problem because of problem >you mentioned below. > > The postgreSQL processes are what is taking up all the cpu. There aren't any other major applications on the machine. Its a dedicated database server, only for this application. It doesn't seem to make sense that PostgreSQL would be maxed out at this point. I think given the size of the box, it could do quite a bit better. So, what is going on? I don't know. Dennis
What is the load average on this machine? Do you do many updates? If you do a lot of updates, perhaps you haven't vacuumed recently. We were seeing similar symptoms when we started load testing our stuff and it turned out we were vacuuming too infrequently. David Dennis wrote: > Qingqing Zhou wrote: > >> Are you sure 100% CPU usage is solely contributed by Postgresql? Also, >> from >> the ps status you list, I can hardly see that's a problem because of >> problem >> you mentioned below. >> >> > The postgreSQL processes are what is taking up all the cpu. There aren't > any other major applications on the machine. Its a dedicated database > server, only for this application. > > It doesn't seem to make sense that PostgreSQL would be maxed out at this > point. I think given the size of the box, it could do quite a bit > better. So, what is going on? I don't know. > > Dennis > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- David Mitchell Software Engineer Telogis
David Mitchell wrote: > What is the load average on this machine? Do you do many updates? If > you do a lot of updates, perhaps you haven't vacuumed recently. We > were seeing similar symptoms when we started load testing our stuff > and it turned out we were vacuuming too infrequently. The load average at the 100% utilization point was about 30! A vacuum analyze was done before the test was started. I believe there are many more selects than updates happening at any one time. Dennis
If your table has got into this state, then vacuum analyze won't fix it. You will have to do a vacuum full to get it back to normal, then regularly vacuum (not full) to keep it in good condition. We vacuum our critical tables every 10 minutes to keep them in good nick. David Dennis wrote: > David Mitchell wrote: > >> What is the load average on this machine? Do you do many updates? If >> you do a lot of updates, perhaps you haven't vacuumed recently. We >> were seeing similar symptoms when we started load testing our stuff >> and it turned out we were vacuuming too infrequently. > > > The load average at the 100% utilization point was about 30! A vacuum > analyze was done before the test was started. I believe there are many > more selects than updates happening at any one time. > > Dennis > > ---------------------------(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 > > -- David Mitchell Software Engineer Telogis
David Mitchell wrote: > If your table has got into this state, then vacuum analyze won't fix > it. You will have to do a vacuum full to get it back to normal, then > regularly vacuum (not full) to keep it in good condition. We vacuum > our critical tables every 10 minutes to keep them in good nick. So should I have vacuum run during the load test? At what level of updates should it run every ten minutes? Dennis