Обсуждение: problems with postgresql speed

Hi.

I've serious problems with the speed of my database.
If I execute any query like this;

SELECT d.gallery_id, e.subevent_id, d.result_type_id,
d.result_type_name, e.language_id FROM ((SELECT b.gallery_id,
b.element_id AS result_type_id, c.name AS result_type_name FROM
gallery_element b, result_type c WHERE ((c.id = b.element_id) AND
(b.element_type_id = 10))) d JOIN (SELECT b.gallery_id, b.element_id AS
subevent_id, c.language_id FROM gallery_element b, view_subevent_info c
WHERE ((c.subevent_id = b.element_id) AND (b.element_type_id = 9))) e ON
((d.gallery_id = e.gallery_id)));

The result take a minute, or more, to show the results.
The postgresql.conf I used is attached to this e-mail.
The table "gallery_element" have 8644 rows, and it have got 25932
registers.
The machine has 512 Mb RAM and 1.4 PIV processor.

Thank you very much.

--
Manuel Trujillo         manueltrujillo@dorna.es
Technical Engineer      http://www.motograndprix.com
Dorna Sports S.L.       +34 93 4702864

On Mon, 3 Dec 2001, Manuel Trujillo wrote:

> I've serious problems with the speed of my database.
> If I execute any query like this;
>
> SELECT d.gallery_id, e.subevent_id, d.result_type_id,
> d.result_type_name, e.language_id FROM ((SELECT b.gallery_id,
> b.element_id AS result_type_id, c.name AS result_type_name FROM
> gallery_element b, result_type c WHERE ((c.id = b.element_id) AND
> (b.element_type_id = 10))) d JOIN (SELECT b.gallery_id, b.element_id AS
> subevent_id, c.language_id FROM gallery_element b, view_subevent_info c
> WHERE ((c.subevent_id = b.element_id) AND (b.element_type_id = 9))) e ON
> ((d.gallery_id = e.gallery_id)));
>
> The result take a minute, or more, to show the results.
> The postgresql.conf I used is attached to this e-mail.
> The table "gallery_element" have 8644 rows, and it have got 25932
> registers.
> The machine has 512 Mb RAM and 1.4 PIV processor.

Have you run vacuum analyze recently on the database?
Can you send the schema for the tables/views involved and the
explain output?

I have got the same problem.
I am working on it about a month :(.
It is not hot :-)

Two machines:
1. Pentium3 400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)
2. Celeron  400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)

The same database, the same postgres Version 7.1.3, the same configuration.
Only processor is diferent, ...

Queryes from client application on Windows over ODBC:
1. 40sec.
2. 90sec.

Radmi

---
Odchozí zpráva neobsahuje viry.
Zkontrolováno antivirovým systémem AVG (http://www.grisoft.cz).
Verze: 6.0.303 / Virová báze: 164 - datum vydání: 24.11.2001

On Monday, December 3, 2001, at 04:30 AM, Ludva Radomír (KM) wrote:
>
> Two machines:
> 1. Pentium3 400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)
> 2. Celeron  400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)
>
> The same database, the same postgres Version 7.1.3, the same
> configuration.
> Only processor is diferent, ...
>
> Queryes from client application on Windows over ODBC:
> 1. 40sec.
> 2. 90sec.

This may be due to the difference between the cache sizes of the two
processors.  IIRC, the celeron has only 128k of cache while the P3 has
512k.  Even though the P3 cache runs slower (I think the celeron runs
at full clock while the P3 runs at 1/2 clock), the larger size may
prevent
a lot more cache misses which makes it much faster.

Databases are extremely I/O bound, so anything you can
stuff into cache is probably worth it.

--Jeremy

I know someone already pointed out the cache differences between the
processors, and that is likely to contribute to the differences you have
observed.

As was stated in the message about cache, databases are extremely IO
bound.  It is worth noting that Celeron's have a Front Side Bus speed of
66Mhz, while P3's have a FSB of 100Mhz or 133Mhz.  If you are running a
133MHz FSB, that is twice the data rate between your RAM and your CPU than
the Celeron.

Also, you seem to imply the systems are identical other than the CPU, but
Disk IO is nearly always the bottleneck in any system.  If one disk is
slower than the other, great differences in speed can be noticed even in
the same system.

Finally, my guess is that the P3's greater cache and faster memory bus
are what give over twice the performance on the P3.

-J

On Mon, 3 Dec 2001, [iso-8859-2] Ludva Radom�r (KM) wrote:

> I have got the same problem.
> I am working on it about a month :(.
> It is not hot :-)
>
> Two machines:
> 1. Pentium3 400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)
> 2. Celeron  400Mhz, hdd 8Gb, 64Mb RAM, kernel 2.2.14-5.0 (RedHat 6.2)
>
> The same database, the same postgres Version 7.1.3, the same configuration.
> Only processor is diferent, ...
>
> Queryes from client application on Windows over ODBC:
> 1. 40sec.
> 2. 90sec.
>
> Radmi
>
> ---
> Odchoz� zpr�va neobsahuje viry.
> Zkontrolov�no antivirov�m syst�mem AVG (http://www.grisoft.cz).
> Verze: 6.0.303 / Virov� b�ze: 164 - datum vyd�n�: 24.11.2001
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

On Mon, 3 Dec 2001, Manuel Trujillo wrote:

> SELECT d.gallery_id, e.subevent_id, d.result_type_id,
> d.result_type_name, e.language_id FROM ((SELECT b.gallery_id,
> b.element_id AS result_type_id, c.name AS result_type_name FROM
> gallery_element b, result_type c WHERE ((c.id = b.element_id) AND
> (b.element_type_id = 10))) d JOIN (SELECT b.gallery_id, b.element_id AS
> subevent_id, c.language_id FROM gallery_element b, view_subevent_info c
> WHERE ((c.subevent_id = b.element_id) AND (b.element_type_id = 9))) e ON
> ((d.gallery_id = e.gallery_id)));

Eww.

After expanding it, and rewriting it to be a self-join:

SELECT gm.gallery_id, gm.element_id as result_type_id,
       gs.element_id AS subevent_id, t.name, v.language_id
  FROM gallery_element gm, gallery_element gs,
       result_type t, view_subevent_info v
 WHERE gm.gallery_id = gs.gallery_id
   AND gm.element_type_id = 10
   AND gs.element_type_id = 9
   AND t.id = gm.element_id
   AND v.subevent_id = gs.element_id

This should not only work the same way, but be much faster.  Then again,
when you have to resort to something as disgusting as a self-join, I'd
want to guess the schema needs to be denormalized slightly.  A certain
amount of replication can intensely speed up and simplify queries.

Then again, I just don't like circular referencing things anyhow, but
that's just me.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Programmer              |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : hamster.lee.net                                              |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+