Обсуждение: Slow response of PostgreSQL
Hello,
I m checking Postgresql and MS-SQl database server for our new development. On a very first query Postresql is out
performedand I think it is very disappointing. My query consists on a single table only on both machines.
Table Structure
Table "inv_detail"
Attribute | Type | Modifier
--------------+-----------------------+--------------------
inv_no | integer | not null
unit_id | character(4) | not null
item | character varying(90) | not null
qty | double precision | not null default 0
rate | double precision | not null default 0
unit | character varying(20) | not null
vl_ex_stax | double precision | not null default 0
stax_prc | double precision | not null default 0
adl_stax_prc | double precision | not null default 0
package | character varying(12) |
Having 440,000 Records.
My Query
--------
select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
on both databases.
PostgreSQL return result in 50 sec every time.
MS-SQL return result in 2 sec every time.
MS-SQL Machine
**************
Athlon 600Mhz. (Unbranded)
256 MB Ram. ( 133 Mhz)
40 GB Baracude NTFS File System.
Windows 2000 Server Enterprise.
MS-SQL 2000 Enterprise. (Default Settings)
PostgreSQL Machine
******************
P-III 600Mhz (Dell Precision 220)
256 MB Ram (RD Ram)
40 GB Baracuda Ext2 File System.
RedHat 7.2
PostgreSQL 7.1.3-2
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 327680
sort_mem = 10485760
max_connections = 64
shared_buffers = 512
wal_buffers = 1024
NOTICE: QUERY PLAN:
********************
Aggregate (cost=inf..inf rows=44000 width=28)
-> Group (cost=inf..inf rows=440000 width=28)
-> Sort (cost=inf..inf rows=440000 width=28)
-> Seq Scan on inv_detail (cost=0.00..11747.00 rows=440000 width=28)
EXPLAIN
Even if I dont compare postgres with any other database server the time taken is alarmingly high. These settings are
notgood I know, but the Postgres result is very un-acceptable. I m looking forward for comments to change the conf
settingfor acceptable results.
And I have two more questions :
1- How can I lock a single record so that other users can only read it. ??
2- one user executes a query it will be process and when another user executes the same query having the same result
shouldnot again go for processing. The result should be come from the cache. Is this possible in postgres ??
Saleem
pgsql-performance@postgresql.org
> select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit; > on both databases. > > PostgreSQL return result in 50 sec every time. > MS-SQL return result in 2 sec every time. > My PostgreSQL Conf is > ********************* > log_connections = yes > syslog = 2 > effective_cache_size = 327680 > sort_mem = 10485760 > max_connections = 64 > shared_buffers = 512 > wal_buffers = 1024 This is a shockingly bad postgresql.conf. I'm not surprised you have performance problems. Change this: effective_cache_size = 4000 sort_mem = 4096 shared_buffers = 1000 wal_buffers = 8 Also, you need a LOT more RAM in your PostgreSQL machine, at least half a gig for a basic database server. > 1- How can I lock a single record so that other users can only read it. ?? You cannot do that in PostgreSQL. > 2- one user executes a query it will be process and when another user executes the same query having the same result shouldnot again go for processing. The result should be come from the cache. Is this possible in postgres ?? No, implement it in your application. Prepared queries and stored procedures might help you here. Chris
On Tue, 17 Feb 2004, Saleem Burhani Baloch wrote: > select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit; > on both databases. > PostgreSQL Machine > ****************** > P-III 600Mhz (Dell Precision 220) > 256 MB Ram (RD Ram) > 40 GB Baracuda Ext2 File System. > RedHat 7.2 > PostgreSQL 7.1.3-2 Besides the comments on the conf file already sent, 7.1.3 is many versions behind the current version and definately has some deficiencies either fully or partially corrected in later versions. All in all, I'd suggest going all the way to 7.4.1 since the hash aggregate stuff might help the queries you're running.
"Saleem Burhani Baloch" <peseek@khi.wol.net.pk> writes:
> PostgreSQL 7.1.3-2
Aside from the config issues Chris mentioned, I'd recommend trying
a somewhat less obsolete version of Postgres. I believe the poor
performance with grouped aggregates should be fixed in 7.4 and later.
(Red Hat 7.2 is a bit long in the tooth as well.)
regards, tom lane
I changed the conf as you wrote. But now the time is changed from 50 sec to 65 sec. :( I have not more 256 MB ram now. When I execute the query the Postmaster takes about 1.8 MB Postgres session takes 18 MB ram only. & psql takes 1.3 MB. After the query finishes the Postgres session reducess memeory and just uses 10 MB ram. I have a question why MS-SQL with 256 MB RAM gives result in 2 sec ?? If I have low memory Postgres should give result in10 sec as compared to MS-SQL. Looking forward for comments & suggestions Saleem > This is a shockingly bad postgresql.conf. I'm not surprised you have > performance problems. Change this: > > effective_cache_size = 4000 > sort_mem = 4096 > shared_buffers = 1000 > wal_buffers = 8 > > Also, you need a LOT more RAM in your PostgreSQL machine, at least half > a gig for a basic database server. > > > Chris
Saleem Burhani Baloch wrote: > I changed the conf as you wrote. But now the time is changed from 50 sec to 65 sec. :( > I have not more 256 MB ram now. > When I execute the query the > Postmaster takes about 1.8 MB > Postgres session takes 18 MB ram only. > & psql takes 1.3 MB. > > After the query finishes the > Postgres session reducess memeory and just uses 10 MB ram. Can you post explain analyze result for the query? Shridhar
Christopher Kings-Lynne wrote: >> 1- How can I lock a single record so that other users can only read >> it. ?? > > You cannot do that in PostgreSQL. How about SELECT ... FOR UPDATE? -- Bill Moran Potential Technologies http://www.potentialtech.com
"Saleem Burhani Baloch" <peseek@khi.wol.net.pk> writes:
> I have a question why MS-SQL with 256 MB RAM gives result in 2 sec ?? If I have low memory Postgres should give
resultin 10 sec as compared to MS-SQL.
Are you still running 7.1?
regards, tom lane
Easy two step procedure for speeding this up: 1: Upgrade to 7.4.1 2: Read this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
>>> 1- How can I lock a single record so that other users can only read >>> it. ?? >> >> >> You cannot do that in PostgreSQL. > > > How about SELECT ... FOR UPDATE? No, because users cannot read the locked row in that case. Chris
On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote: > >>> 1- How can I lock a single record so that other users can only read > >>> it. ?? > >> > >> > >> You cannot do that in PostgreSQL. > > > > > > How about SELECT ... FOR UPDATE? > > No, because users cannot read the locked row in that case. I just tested it (within transactions) and it appeared that I could still view the rows selected for update.
scott.marlowe wrote: > On Wed, 18 Feb 2004, Christopher Kings-Lynne wrote: > >>>>>1- How can I lock a single record so that other users can only read >>>>>it. ?? >>>> >>>> >>>>You cannot do that in PostgreSQL. >>> >>> >>>How about SELECT ... FOR UPDATE? >> >>No, because users cannot read the locked row in that case. > > I just tested it (within transactions) and it appeared that I could still > view the rows selected for update. Thank you. I was just about to test it myself. The user's guide, section 9.3.2 states that this is the case: i.e. select for update will prevent concurrent updating of the row, while allowing queries utilizing that row to succeed. -- Bill Moran Potential Technologies http://www.potentialtech.com
>>>How about SELECT ... FOR UPDATE? >> >>No, because users cannot read the locked row in that case. > > > I just tested it (within transactions) and it appeared that I could still > view the rows selected for update. Ah, true. My mistake. OK, well you can do it in postgres then... Chris
Hi, Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh 9 require a lot of lib's) and set the configurationsent by Chris. Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 process aggregate slowly.Anyway. I still have to go for 2 sec result and now I m thinking to go for Free BSD 5.2. The record locking by "select ..... update for" is working fine, but a situation arrises when the second user goes for lockingthe record, the task will wait untill the first user ends up his work. IS their is a way that we can know or geta message that the row/record is already locked by some one else. Thanks for help. Saleem
On Thursday 19 February 2004 14:31, Saleem Burhani Baloch wrote: > Hi, > > Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( rh > 9 require a lot of lib's) and set the configuration sent by Chris. Now the > query results in 6.3 sec waooo. I m thinking that why the 7.1 process > aggregate slowly. Anyway. > > I still have to go for 2 sec result and now I m thinking to go for Free BSD > 5.2. Before that you can try something with kernel 2.6.3. I think it could make the difference you are looking at. > The record locking by "select ..... update for" is working fine, but a > situation arrises when the second user goes for locking the record, the > task will wait untill the first user ends up his work. IS their is a way > that we can know or get a message that the row/record is already locked by > some one else. Right now, it is hotly debated on HACKERS about adding a NOWAIT clause to SELECT FOR UPDATE. If you think your application deployment is away for months and can try CVS head, you can expect some action on it in coming few days. As a side bonus you would be benefitted by performance and scalability additions that won't make there way in 7.4 stream. HTH Shridhar
> Thanks every one for helping me. I have upgraded to 7.4.1 on redhat 8 ( > rh 9 require a lot of lib's) and set the configuration sent by Chris. > Now the query results in 6.3 sec waooo. I m thinking that why the 7.1 > process aggregate slowly. Anyway. I'm glad we could help you Saleem :) We knew PostgreSQL wasn't that slow :P Chris
Saleem Burhani Baloch kirjutas N, 19.02.2004 kell 11:01: > Hi, > > Thanks every one for helping me. I have upgraded to 7.4.1 on > redhat 8 ( rh 9 require a lot of lib's) and set the configuration > sent by Chris. Now the query results in 6.3 sec waooo. I m thinking > that why the 7.1 process aggregate slowly. Anyway. > > I still have to go for 2 sec result What is the plan now ? ---------------- Hannu
Shridhar Daithankar <shridhar@frodo.hserus.net> writes: > Right now, it is hotly debated on HACKERS about adding a NOWAIT > clause to SELECT FOR UPDATE. If you think your application > deployment is away for months and can try CVS head, you can expect > some action on it in coming few days. You can also try using the statement_timeout configuration variable that is already included with 7.4. It's not exactly "don't wait for locks", but should approximate that behavior well enough. http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-CLIENT -Neil