Re(2): db server is too slow, real slow
От | pgsql-admin |
---|---|
Тема | Re(2): db server is too slow, real slow |
Дата | |
Msg-id | fc.000f56720075f400000f56720075f400.75f419@fc.emc.com.ph обсуждение исходный текст |
Ответы |
Re: Re(2): db server is too slow, real slow
|
Список | pgsql-admin |
The postgres server was started with the command: /usr/bin/postmaster -i -S -D/var/lib/pgsql Here is just one query executed from Embperl pages with Perl DBI: officedata=> explain SELECT * FROM office, office_application officedata-> WHERE office.code = office_application.office_code officedata-> AND office_application.active officedata-> AND NOT office.deleted; NOTICE: QUERY PLAN: Hash Join (cost=5524.86 rows=644 width=262) -> Seq Scan on office_application (cost=4261.49 rows=9767 width=245) -> Hash (cost=908.88 rows=854 width=17) -> Seq Scan on office (cost=908.88 rows=854 width=17) note: office.code and office_application.office_code are serial data types. office_application.active and office.deleted are boolean. others are char, inet, oid (for lobj), and date data types. Here is another query executed from ms excel (pgsql-odbc): workdata=> explain select * from consol; NOTICE: QUERY PLAN: Seq Scan on consol (cost=1133.48 rows=8560 width=256) note: consol table contains no index, no sequence, no primary key (just char, date, money, and inet) sszabo@megazone23.bigpanda.com writes: > >We'll probably need some additional information: > >What settings did you start the server with, what queries are you running >that are slow (with schema, index information), what does explain show for >the queries? > >Stephan Szabo >sszabo@bigpanda.com > >On Thu, 19 Oct 2000, pgsql-admin wrote: > >> I'm using >> PostgreSQL 6.5.3 on i686-pc-linux-gnu. >> I have a backup of our database (sql dump and large object) >> backup.tar.bz2 with the size of 10121985. >> We have users accessing our database from http and >> odbc (ms excel). >> >> Can someone pls tell how to solve this problem? >> >> Thank you very much. >> Sherwin >> >> Here are the processes: >> 6:08pm up 12 days, 23:39, 2 users, load average: 12.99, 12.70, 11.29 >> 74 processes: 57 sleeping, 17 running, 0 zombie, 0 stopped >> CPU states: 24.9% user, 74.2% system, 0.0% nice, 0.7% idle >> Mem: 387032K av, 184556K used, 202476K free, 48872K shrd, 49308K >> buff >> Swap: 265032K av, 5284K used, 259748K free 73596K >> cached >> >> PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME >COMMAND >> 11345 postgres 9 0 2604 2544 1928 R 0 13.6 0.6 1:23 >> postmaster >> 11220 postgres 9 0 2604 2544 1924 R 0 13.4 0.6 3:10 >> postmaster >> 11213 postgres 7 0 2604 2544 1928 R 0 13.0 0.6 3:16 >> postmaster >> 10860 postgres 6 0 5552 5492 2068 R 0 12.8 1.4 14:48 >> postmaster >> 10862 postgres 8 0 2588 2528 1896 R 0 12.8 0.6 14:17 >> postmaster >> 10996 postgres 8 0 4404 4344 2072 R 0 12.8 1.1 8:28 >> postmaster >> 11168 postgres 6 0 3552 3492 2072 R 0 12.8 0.9 3:51 >> postmaster >> 10966 postgres 5 0 4652 4592 2064 R 0 12.6 1.1 10:04 >> postmaster >> 10957 postgres 7 0 4800 4740 2064 R 0 12.4 1.2 10:51 >> postmaster >> 10861 postgres 5 0 5396 5336 2064 R 0 12.2 1.3 14:15 >> postmaster >> 10867 postgres 9 0 5332 5272 2060 R 0 12.2 1.3 13:53 >> postmaster >> 10892 postgres 5 0 5084 5024 2060 R 0 11.8 1.2 12:33 >> postmaster >> 10995 postgres 5 0 4416 4356 2072 R 0 11.8 1.1 8:33 >> postmaster >> 11163 postgres 5 0 2612 2552 1924 R 0 11.4 0.6 4:01 >> postmaster >> 11170 postgres 5 0 2428 2368 1844 R 0 10.6 0.6 1:07 >> postmaster >> 10888 postgres 5 0 2584 2524 1896 R 0 10.2 0.6 12:47 >> postmaster >> >
В списке pgsql-admin по дате отправления: