Re(2): db server is too slow, real slow
Re(2): db server is too slow, real slow
От:
"pgsql-admin" <pgsql-admin@fc.emc.com.ph>
Дата:
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
>>
>
Re: Re(2): db server is too slow, real slow
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
On Fri, 20 Oct 2000, pgsql-admin wrote: > The postgres server was started with the command: > /usr/bin/postmaster -i -S -D/var/lib/pgsql Have you tried changing the values of -B to see if increasing the number of buffers helps? > 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. How many rows does the query actually give you, and how many rows in office_application have active true and how many rows in office have deleted false? I don't know too much about this, but with the way the estimates are done, I'm not sure that an index will actually end up helping you even if you're selecting the lesser of the non-NULL values. > 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) Well the plan looks reasonable, sequence scan is probably the fastest way to get the entire table unordered. How long is it taking to do this query via odbc and from psql on the local machine?