Hi,

Once I realised that the standard Joomla cache wasn't really effective and that one of the reasons that my VPS was not as fast as I expected it to be was the slowness of accessing MySql, it became obvious that I needed something like Page Cache. It is very effective, but since my day job is designing using Oracle I'm left wondering whether the standard out of the box Joomla database access is tuned effectively.

I need to learn a lot more about MySQL but there seems to be little on the web about tuning MySQL for use with Joomla. I know from experience that one can make really dramatic improvements to SQL statements run time in Oracle predominantly through the use of indexes. However what little I have found by googling about MySql performance seems to concentrate on altering MySql variables on the server. You can do a similar thing to Oracle based systems but that is a something you do after you've already tuned using indexes (and varying the way the SQL statement is constructed).

Am I missing something or is it possible to see which SQL statements are running slowly on a Joomla installation and is it possible to easily add indexes. I have read about 'Explain', which is similar to Oracle's 'Explain Plan', but I can't see how to query for existing indexes or how to create them.

I'll continue to investigate this issue because it seems that there may be scope for improving MySQL perfomance.

As an aside is Postgres any better from the tuning perspective?

Regards,
Chris

Well, I'll tell you this from my experience with MySQL... Joomla's queries (from the core), are pretty well tuned. Take a look at http://dev.mysql.com/doc/refman/5.0/en/explain.html. Turn on debuging, and you'll get a list of every query that's run. Then just put explain infront of it and run it. To see the table details, use http://dev.mysql.com/doc/refman/5.0/en/describe.html. From my experience, the only inefficient queries are done via OpenSEF (or any SEF component for that matter). Add these to lines under [mysqld] in my.cnf to log slow queries (will log all queries that take more than 1 second to execute.

Code:

log-slow-queries long_query_time = 1

The more common problem is not the queries themselves, but the number of them. The best thing to tweak is the number of queries that are run...

Hi,

Thanks for that, I obviously came to the right place to ask the question!

Thanks,
Chris

Glad to hear it. Let me know if there is anything else you would like to know...

Hi there,

i just came across this thread.

you mentioned that OpenSEF has inefficient quieries like many other SEF component.
Have you tried sh404SEF? http://extensions.siliana.net/en/sh404SEF-and-url-rewriting/sh404SEF-URL-rewriting-for-Joomla- Virtuemart-SEF-URL-SEO.html

they advertise that it barely adds sql queries compared to the joomla core SEF

what is your thoughts on this one?

julien

Well, I need to test it. I am slowly (with the help of a few other "unnamed" people) getting a new performance site off the ground. Believe me, I will be reviewing this component thoroughly...

great, that's nice.

becuase so, every time people talk about performance, it's just words. So who to believe????
but you came and gave result and shown graph. and that becomes almost undeniable. I mean unless you would start lying on the number.....but where are we going if can't trust !!!

So if your site becomes some kind of benchmark and/or test for component, that would be very cool.

you can start a standard like: "passed the ircmaxell test".
lolol but seriously, what do you think

I'm not going to comment until the details are all worked out...

lololololol

"WE CAN MAKE YOU TALK IRCMAXELL !!!" just kidding. no problem, i understand.

let's build up some curiosity around this

cheers