Thread: 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
Attachment
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 |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+