473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Performace question


Dear list,

First of all I want to say sory, if my question was answered somewhere.
Or if it is my fault. If so, please, give me link/hint. My own search fails
:( I have experimence with MySql, MsSql and Oracle (and MS Access huch :) I
am new to PgSql.

We are running server - OpenBSD 2.9, latest apache, latest PHP, latest
PgSql, latest mod_ssl. No other related packages are used. All programs are
compiled 'normal' way, including PgSql - except unicode support. There are
no special runtime conditions. Server is Intel P3 800/intel MB, 512M Ram,
plenty HDD, etc. We have web application, thin client type (mozzila/ie).
Users are connecting throw HTTPS to Apache, where runs PHP scritps talking
by TCP/IP to local Postgres DB (native PgSql support in PHP). PgSql uses
default settings from source package. System is huge, many tables, but
nothing special. I hope it is enough info about this. Postmaster options: -h
127.0.0.1 -i -p 5432, postgresql.conf is 'empty' (no default value override
used)...

Let's say:

SELECT
id,parent,alias,aliasfull,name,comment,type,typefl ags,flags,cluster,viewprio
r FROM dtditems WHERE cluster IN (42)

(ohhh, what a complicated example :)

My problem is performance. Sometimes SQL statement takes ~20 ms,
sometimes (the same) takes 200ms, sometimes 2.000 ms (!). I am sure it is
not because of CPU/memory (both plenty avilable at the moment). Average is
~600ms. 'dtditems' is table, without _any_ foreign key/indexes, etc. It have
592 rows. 'cluster' is integer. Don't tell me to create indexes, or foreign
keys. I know they helps, but for table with ~600 rows, where ~15% is
selected, difference will be small, not 10x.

This SQL statements takes 982ms, after few 'refresh' it takes 604ms,
after another few refresh 56, and after another few 12480ms. I didn't find
any conditions why these times are soo different. My experimence says, that
this kind of SQL and row count can be done at given CPU somewhat around
~5-10ms. Above statement hits 113 rows.

When I run the system connected to another DB, this problem doesn't
arise. I think (90% :) the problem is somewhere in PgSql (maybe related to
system).

Initially, _ALL_ SQL's takes hundreds of ms. I created ONE index (for
testing) and the problem was over. But was over for ALL tables. Seems PgSql
have some trouble with indexes.

I allready tried create indexes (for table in above SQL statement), but
the problem wasn't solved. So, I remove indexes again.

Please, anyone can give me hint where I should try to find what causes
the problem ? Why is PgSql (or it is PHP/Apache bug ?) soooo slow ? And
mainly, WHY there is 10x difference between executing the same SQL statement
? Is there any way, how to 100% say if the problem is @ php or pgsql ?

I understand because of multitasking OS we can't measure 'exactly', so,
if one requst will be 10ms, next 12,8,14,20,12,40,34,... it will be fine.
But something like 50, 500, 2000 makes me crazy....

I have added 'log' - part of pgsql log coresponding to above SQL.

Feel free to have any Q about sysconfig/programs config.

And sorry again if I miss something.

Thank you !

Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
5 1519
Firstly, have you run ANALYZE across the database?

Secondly, the queries that are varying so much, can you post the EXPLAIN
ANALYZE output so we can see what is actually going on.

Note also that the query log can be very helpful in finding out if the delay
is in the database or not.

Hope this helps,

On Wed, Oct 29, 2003 at 02:28:51PM +0100, Lada 'Ray' Lostak wrote:

Dear list,

First of all I want to say sory, if my question was answered somewhere.
Or if it is my fault. If so, please, give me link/hint. My own search fails
:( I have experimence with MySql, MsSql and Oracle (and MS Access huch :)I
am new to PgSql.

--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good
men to do nothing." - Edmond Burke
"The penalty good people pay for not being interested in politics is to be
governed by people worse than themselves." - Plato


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/n9N/Y5Twig3Ge+YRAoSjAJ4wGFeRlTjdtiZTk5++PD63uaSocwCfXo mv
dvIi8QBgLhfkERZ1kX839YU=
=NanS
-----END PGP SIGNATURE-----

Nov 12 '05 #2
Firstly, have you run ANALYZE across the database? ANALYZE not, only EXPLAIN. And it looks pretty normal :( There is basically
nothing interested in soo simple SQL.
Secondly, the queries that are varying so much, can you post the EXPLAIN
ANALYZE output so we can see what is actually going on. Thx, ANALYZE was good idea. Here it comes - right now, there is index on
'cluster' (BTREE) @ dtditems. But it was not used - I guess because reading
& seraching will cost more than pure seq scan on 'few' items...

EXPLAIN SELECT
id,parent,alias,aliasfull,name,comment,type,typefl ags,flags,cluster,viewprio
r FROM dtditems WHERE cluster IN (42)

QUERY PLAN Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84)
Filter: ("cluster" = 42)

QUERY PLAN Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84)
(actual time=440.10..472.00 rows=113 loops=1) Filter: ("cluster" = 42)
Total runtime: 519.86 msec

Current 'top' output (while running test)

load averages: 1.31, 1.11, 0.78
50 processes: 1 running, 47 idle, 2 stopped
CPU states: 1.4% user, 0.0% nice, 2.2% system, 0.2% interrupt, 96.3%
idle

There also more than 200M free memory.

Just to compare - the same SQL executed by Ms Access database (uch, it hurt
to type that name !) takes ~12 ms. Also MySql takes similar time...

There is no differences between variations on WHERE - like using = instead
of IN etc.
Note also that the query log can be very helpful in finding out if the delayis in the database or not.

I added to email log from PgSql (hope it arrives well last time, coz I am
sitting @ outlook@nt4 :) and there you can see, that it really takes 500 ms
to select 100 records from ~500 rows table... Let me know, if log was
damaged. But the time coresponds....

What can I do (or where is some document regarding this topic ?) speed up
PgSql ? I really think, half second for selecting ~100 rows from ~600 rows
table it pretty slow. Commodore 64 (1 mHz 6510) will do it faster :)

Any hints ?
Thanks,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--------------------------------------------------------------------------
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3
"Lada 'Ray' Lostak" <ra*@unreal64.net> writes:
EXPLAIN SELECT
id,parent,alias,aliasfull,name,comment,type,typefl ags,flags,cluster,viewprio
r FROM dtditems WHERE cluster IN (42) QUERY PLAN Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84)
Filter: ("cluster" = 42)


Um ... what is the datatype of the "cluster" column? I'm betting it's
int8 or int2, not int4 which is what your constant is ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4
On Wed, Oct 29, 2003 at 05:42:30PM +0100, Lada 'Ray' Lostak wrote:
Secondly, the queries that are varying so much, can you post the EXPLAIN
ANALYZE output so we can see what is actually going on.

Thx, ANALYZE was good idea. Here it comes - right now, there is index on
'cluster' (BTREE) @ dtditems. But it was not used - I guess because reading
& seraching will cost more than pure seq scan on 'few' items...


Hmm... have you tried running VACUUM periodically?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #5
> > >Secondly, the queries that are varying so much, can you post the
EXPLAIN
ANALYZE output so we can see what is actually going on.

Thx, ANALYZE was good idea. Here it comes - right now, there is index on
'cluster' (BTREE) @ dtditems. But it was not used - I guess because reading & seraching will cost more than pure seq scan on 'few' items...


Hmm... have you tried running VACUUM periodically?

Sure.... But I will try 'full'....

Joy ! You hit the right problem... Thank you !

Seq Scan on dtditems (cost=0.00..16.40 rows=113 width=82) (actual
time=0.78..3.30 rows=113 loops=1) Filter: ("cluster" = 42) Total
runtime: 3.84 msec

It required 'full' vacuum.. My bad I guess.... Now is the execution time
'constant' :)

ps: my MAJOR problem was: WHY are execution time soo different ?

4 sequential execs:

Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84) (actual
time=1534.52..1566.37 rows=113 loops=1) Filter: ("cluster" = 42) Total
runtime: 1566.95 msec

Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84) (actual
time=29.27..32.29 rows=113 loops=1) Filter: ("cluster" = 42) Total
runtime: 32.81 msec

Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84) (actual
time=1695.69..1735.83 rows=113 loops=1) Filter: ("cluster" = 42) Total
runtime: 1736.36 msec

Seq Scan on dtditems (cost=0.00..253.40 rows=150 width=84) (actual
time=29.27..32.29 rows=113 loops=1) Filter: ("cluster" = 42) Total
runtime: 53.12 msec

Any hint why there was this difference ?

R.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Stephan Br?nnimann | last post by:
I know there's a lot of literature around ... however I'm usually mobile and do not want to carry all the books with me. Therefore my question: Does anybody a web page that provides the...
3
by: Stevey | last post by:
I have the following XML file... <?xml version="1.0"?> <animals> <animal> <name>Tiger</name> <questions> <question index="0">true</question> <question index="1">true</question> </questions>
7
by: nospam | last post by:
Ok, 3rd or is it the 4th time I have asked this question on Partial Types, so, since it seems to me that Partial Types is still in the design or development stages at Microsoft, I am going to ask...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
1
by: Sam-I-Am | last post by:
Hi There I would like to create a Perfomance Counter to monitor Joins on a website per minute. I have previously used the RateOfCountsPerSecond32 and a timer component. How can I measure per...
7
by: Joe | last post by:
Not sure what group this question is better suited for so I sent it to both. I have a DataTable which contains 1545 rows. I have a method that returns a DataTable of distinct rows based on one...
19
by: Tony Johansson | last post by:
Hello!! Which have best performance between i++ or ++i. Is it exact the same or is it some very small difference in performace betwwwn these two. //Tony
1
by: yoni | last post by:
Hi All, I have a complex C# application (winforms) that I am taking over. I do maintenance and add small features here and there. what I need is: 1. a tool that will help with testing. that is,...
7
by: Peted | last post by:
Hi, im hoping someone cane provide or point to a definitive accurate explantion of dotnet compilation when run and the best way to optimise peformace when dotnet code is run first time and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.