Search found 67 matches

by jmpoure
Fri Jan 04, 2008 9:06 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

Another issue is that PostgreSQL complains 10 times a minute that backslash are not escaped well. The query at stake is: SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_ip, s.session_viewonline FROM phpbb_users u, phpbb_sessions s WHERE ...
by jmpoure
Fri Jan 04, 2008 8:42 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

I am sorry but I do not see how the zebra table is taking advantage of the indexes you created because it is doing hash joins... My cut and paste is not very explicit. Indenting was not preserved. Here is a normal paste of the query plan: Hash Left Join (cost=18.63..67.71 rows=4 width=1095) (actual...
by jmpoure
Fri Jan 04, 2008 8:36 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

I turned debugging on and viewed a lot of queries.
Most of them run in less than 3 millisecond.

Seems okay for me.

Kind regards,
Jean-Michel
by jmpoure
Fri Jan 04, 2008 1:46 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

Here is a collection of SQL queries which run with * 008-01-04 14:44:43 CET LOG: instruction : SELECT u.*, s.* FROM phpbb_sessions s, phpbb_users u WHERE s.session_id = 'XXXXXXXXXXXXXXXXXXXXXXXX' AND u.user_id = s.session_user_id 2008-01-04 14:44:43 CET LOG: durée : 2.099 ms
by jmpoure
Fri Jan 04, 2008 10:55 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

Just for information, MySQL is crap. Developers wanting to investigate queries should run PostgreSQL. Just my 2 cents...
by jmpoure
Fri Jan 04, 2008 10:11 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

ISSUE #3: 2008-01-04 11:29:04 CET LOG: instruction : SELECT u.*, z.friend, z.foe, p.* FROM phpbb_users u, phpbb_posts p LEFT JOIN phpbb_zebra z ON (z.user_id = 104595 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('320002', '320006', '320014', '320019', '320022', '320025', '320050', '320088', '32...
by jmpoure
Fri Jan 04, 2008 9:45 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

Another one, ISSUE #2 2008-01-04 10:38:48 CET LOG: instruction : SELECT u.*, z.friend, z.foe, p.* FROM phpbb_users u, phpbb_posts p LEFT JOIN phpbb_zebra z ON (z.user_id = 1 AND z.zebra_id = p.poster_id) WHERE p.post_id IN ('428795', '428276', '419494', '419492', '416989', '415533', '414555', '41293...
by jmpoure
Fri Jan 04, 2008 9:24 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

After installing the patch: 2008-01-04 10:21:52 CET LOG: instruction : SELECT t.topic_id FROM phpbb_topics t WHERE t.forum_id = 26 AND t.topic_type IN (0, 1) AND t.topic_approved = 1 ORDER BY t.topic_type DESC, t.topic_last_post_time DESC LIMIT 50 OFFSET 0 2008-01-04 10:21:52 CET LOG: durée : 6.120 ...
by jmpoure
Fri Jan 04, 2008 7:04 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

Re: SQL optimisation

Thanks. I plan to continue debugging queries in postgresql debug mode.
by jmpoure
Thu Jan 03, 2008 11:16 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 50276

SQL optimisation

Dear Friends, I am using PhpBB 3.0 on a 400.000 messages bulletin board. I turned SQL query debugging on in PostgreSQL. Then I used a load testing tool to simulate 200 simultaneous queries. This resulted in the board becoming very slow. Then I looked a the log. Some remarks : 2008-01-03 23:47:39 CET...