HELP!! MYSQL Query Time increase a lot !! (max 200 times)

[mwpmo]

After I upgraded my gallery to 1.5b4, I find that sometimes,
the MYSQL query time for some simple task suddenly increase a lot
(from 600ms to a max of 120,000ms, max 200 times!!!)

Hence It take long long time to display a photo page.



What is the cause of my problem ???

I have try delete everthing and rebuild the gallery from 1.5b4 full install, cannot help.

I am using mysql 3.23.56.

Due to other program, I cannot upgrade it to 4.X.

[Pekka]

Even 600ms sounds way too much, typically public list page should open in 5-30ms with author's order.

How many photos do you have in database? Remember that you MUST have at least 7 photos in EE for full speed (known issue).

Does this happen on public area or in editors?
Do you have only EE in the database?

It would make solving this very fast if you could mail me an EE Backup dump of the database to pekka AT photography-on-the DOT net

[mwpmo]

I just email the backup dump file to you.

In my EE, I have over 600 photos in 7 exhibitions.

In mysql, besides EE, I have another database for a forum. The traffic of forum is not much and I have use it with EE1.3 for a long time. No problem.

This happen on usually in public area, but sometimes in editors also?

I am using a sun netra x1, running Solaris 8 with 640M Ram.

Please visist my gallery and have a try

http://gallery.tower3a.com

Any idea?

Is the EE 1.5b4 is optimized just for mysql 4.x but not 3.x (that I am using)?

[Pekka]

mwpmo wrote:
I just email the backup dump file to you.

In my EE, I have over 600 photos in 7 exhibitions.

Number of photos do not affect EE speed per se. It affects only in way that SQL server needs to do more internal sorting and that will add some milliseconds to queries. But with e.g. Author's Order it is faster than e.g. with sorted by country, because it can optimize sorting by using indexes very efficiently.

Is the EE 1.5b4 is optimized just for mysql 4.x but not 3.x (that I am using)?


EE is a little faster in MySQL 4 because MySQL 4 is better optimized and has internal query cache which helps a lot. In EE there is no specific code to use v.4 is any specific way. The speed difference between MySQL 3 and 4 with EE 1.5 beta 4 is minimal.

And now to your issue.

I see you have deleted most of the countries in database. This has an interesting effect to database: as there are not enough country id's to be used for indexing any more, MySQL switches to a totally different query method and so the queries take much more time. Merely by replacing the country table with original EE country table the speed in my server doubled.

Whole summer I did speed tests and dug very deeply into MySQL index system, so although my next advice may sound weird it has some knowhow behind it:

To double the speed again, add a couple of new photographers to the database (no need to assign them to any photos).

With these two actions I increased the speed of database you sent me from ca. 220ms to ca. 35. I tested the effect in both MySQL 3 and 4 and results were identical.

I will add a section for speeding up EE into documentation, but there are two things which are most important to know:

1. More is faster. EE will speed up if you have more photos in more exhibitions. It's better to have more than one item in photographers, accessories, locations.... MySQL loves complexity but hates sparse data.

2. Do NOT touch EE database with any other tool than EE editor. You don't understand it how it works and what affects what, so don't touch it with PhpMyAdmin unless specifically instructed to do so by me as in owner bug in another thread.

Those moments of slowdown are perhaps another matter. I see that your MySQL has been instructed to log all activity internally - perhaps the slowdowns are due enormous logs being written (some admins don't clean their logs properly so they can grow to gigabytes). Let me know if above remedies helped in this situation - but I doubt EE is to be accused because EE does not do things differenly over time.

As you have a forum in same database it may affect index usage so that index RAM gets too limited (it is shared between forum and EE). EE 1.5 is much bigger than 1.3, so you will need to assign a bit more RAM for the indexes. Index RAM usage can be set in mysql.ini by system admin.

[Scho]

Pekka,

Thanks for the tips on speeding things up. I previously had only one photographer in the database and adding three more appeared to result in a significant speed gain.

[Pekka]

Scho wrote:
Pekka,

Thanks for the tips on speeding things up. I previously had only one photographer in the database and adding three more appeared to result in a significant speed gain.

Nice. I was initially thinking of fooling MySQL by adding few photos and photographers which only EE could see internally, but it would require a lot of coding to exclude them from every function and using dummy id's in existing EE systems would have meant more extra coding. So I think it is easier to just add this to documentation, even though it sounds weird.

[iamhives]

Wow. Adding a couple of photographers made a significant difference to the speed. Oh the squirks of databases/query languages..

[mwpmo]

I will restore the orginal country database and add a few photographers to see any improvement!


pekka wrote:

Those moments of slowdown are perhaps another matter. I see that your MySQL has been instructed to log all activity internally - perhaps the slowdowns are due enormous logs being written (some admins don't clean their logs properly so they can grow to gigabytes). Let me know if above remedies helped in this situation - but I doubt EE is to be accused because EE does not do things differenly over time.


From the output of phpmy admin, I have only enable bin log only - log, log updates, log slaves updates, log long queries are all OFF.

Am I really enable all logs? If that's is the case, how can I diable all log since I can only find log-bin option in my my.cnf file (I am using unix, so it is my.cnf, not my.ini)


As you have a forum in same database it may affect index usage so that index RAM gets too limited (it is shared between forum and EE). EE 1.5 is much bigger than 1.3, so you will need to assign a bit more RAM for the indexes. Index RAM usage can be set in mysql.ini by system admin.


I will increase it. Thanks for your advice!

[Alexis427]

i'm having the same problem

cpu times go very high with EE1.5 when EE1.3 is working perfectly with the same server& mysql installation.

i have tried to add photographers, cameras, etc, even added a dummy gallery with 150 pics to see if something helps, there's nothing to do, maybe it's a BIT better now, but mysql queries still take very long, and are using 95% cpu of the server...

i don't know what's happening here, you can see my test gallery here :
(removed since it's clogging the server...

if you need the path to my gallery, admin dir, or my database i'll gladly help

Alex

[Pekka]

alexis427 wrote:
i'm having the same problem

cpu times go very high with EE1.5 when EE1.3 is working perfectly with the same server& mysql installation.

i have tried to add photographers, cameras, etc, even added a dummy gallery with 150 pics to see if something helps, there's nothing to do, maybe it's a BIT better now, but mysql queries still take very long, and are using 95% cpu of the server...

i don't know what's happening here, you can see my test gallery here :
(removed since it's clogging the server...

if you need the path to my gallery, admin dir, or my database i'll gladly help

Alex


Yes, please mail the database and other info to pekka AT photography-on-the DOT net

What kind of server hardware do you use?

Have your tried optimize commands in editor?

[Alexis427]

Yes, please mail the database and other info to pekka AT photography-on-the DOT net
Have your tried optimize commands in editor?

i did the 2 optimise commands and now it works good, seems to work as good as it did with 1.3, i'll let you know and send you everything by email i it doesn't (or do you want all the info and db dump even if it works ?)

keep up the good work !

ps : any ccard payment option in progress ?

[Pekka]

alexis427 wrote:

Yes, please mail the database and other info to pekka AT photography-on-the DOT net
Have your tried optimize commands in editor?

i did the 2 optimise commands and now it works good, seems to work as good as it did with 1.3, i'll let you know and send you everything by email i it doesn't (or do you want all the info and db dump even if it works ?)

Great!

MySQL has a funny way of refusing to initialize indexes (index system is basically a very fast RAM cache for certain table rows) after table has been (re)created. Optimize command kicks indexes back alive. I normally do this optimizing automatically for example after EE Backup restore and installation scripts but as always there may be other applications is server which try to do maintenance (e.g. nighly backups or transparent restore by sysadmin after server crash) which may do silly things like keep indexes in sleeping mode.

In EE Backup's slow restore the index optimizing is not done, and that is fixed in next fix pack (which will include rethumbnailer, too, as I just finished coding it).

keep up the good work !

I certainly try to!

ps : any ccard payment option in progress ?

Always in progress... :)
Any good suggestions for card payment services? Kagi?

[mwpmo]

Peeka,

I have restored the orginal country database, added a few photographers and increase the Index RAM!

Now the typical SQL Query time for list pagehas been decrease from 600ms to 200ms.

What a big improvement !!

However, I got some problem in the "Display" (no of photo in a page) option together with "Listing type" of "Detail Listing".

For example, in a exhibition of 70 photos.

The SQL Query time for display XX Photos with "Detail Listing 1"

10 Photos - 170ms
20 Photos - 180ms
30 Photos - 200ms
40 Photos - 210ms
50 Photos - 260ms
100 Photos - wait for 20 mins, still loading...
500 Photos - wait for 20 mins, still loading...
All Photos - wait for 20 mins, still loading...

I think it is a problem when the no of Photos to display is more than the total no of photos in an exhibition.

Please try it as

http://gallery.tower3a.com/list.php?exhibition=6

What's worng?

[Pekka]

Hi,

I checked your page (16:45 Finnish time) and I got about 200-220 ms regardless of per page value. 100, 500 and all per page loaded also equally fast.

This is as expected, because the query for any listing page (regardless of output type selected) is identical. All exhibitions combined will show some slowdown because then each thumb path is fetched separately. Also, when you have forsale column on in detail list, the EE commerce data is fetched for each row (but this is extremely fast) by output template.

You server seems to be still on the slow side, but I think reason (for halts, too) is somewhere under server hood or configuration, not in EE, as e.g your gallery db runs on this server (P41.7GHz/512MB redhat/ensim, MySQL 3 at default settings) about 4 times faster (for example "Yoki" gallery main query takes 35- 54ms, with 500 per page and detail listing). I can give your URL to my test installation if you need to see it.

I will nevertheless investigate any possible ways to speed up EE is all servers, e.g. by reducing memory usage and if I can achieve improvements I'll of course notify you.

One thought: try using some categories - see if that makes any difference.

I think it is a problem when the no of Photos to display is more than the total no of photos in an exhibition.

It does not fetch any "empty" results. Only what query returns with current criteria. When perpage is at 100 it asks for 100 results, if there are less MySQL knows it and stops after there is no more available.

[mwpmo]

I agree that my server speed is on the slow side when it compare with a P4.

I am using a entry-level sun sparc server with 512M Ram, running Solaris 8, Apache 2, PHP 4.3.2 and MySQL 3.23.56.

The processing speed of this sun-sparc is just like a Pentium grade computer.

About 200ms query time is quite acceptable to me, cause the difference with a fast server (30-50ms) is just 150ms (Not much).

But I don't know why when I select the no of photos to display is larger than the number of Photos in the exhibition, it just "hang" when I select "Detail Listing"

(Just "Detail Listing", not happen in other "thumbnail" listing modes)

When you try my server, have you select "detail listing"?

[Pekka]

mwpmo wrote:
I agree that my server speed is on the slow side when it compare with a P4.

I am using a entry-level sun sparc server with 512M Ram, running Solaris 8, Apache 2, PHP 4.3.2 and MySQL 3.23.56.

The processing speed of this sun-sparc is just like a Pentium grade computer.

About 200ms query time is quite acceptable to me, cause the difference with a fast server (30-50ms) is just 150ms (Not much).

But I don't know why when I select the no of photos to display is larger than the number of Photos in the exhibition, it just "hang" when I select "Detail Listing"

(Just "Detail Listing", not happen in other "thumbnail" listing modes)

When you try my server, have you select "detail listing"?



Yes I tried it just now again : exhibition "yoki" 100 per page, detail listing 1. Of about 15 tries I got 12 240ms and couple of 440-490ms loads.

On thing that I now noticed: Apache 2. With PHP Apache 2 is NOT recommended - in fact PHP documentation says in http://www.php.net/manual/en/install.apache2.php :

"Do not use Apache 2.0 and PHP in a production environment neither on Unix nor on Windows. "

I'm sure hangups you encounter are http hangouts of Apache 2. Many Apache 2 related bugs are fixed in latest PHP versions, but it is still not a finished combination.

[mwpmo]

Pekka,

I just upgraded the PHP from 4.3.2 to 4.3.3.

The problem disapper !!!

In addition, I found that may be last time when I compile PHP,
I just used the --with-mysql option in "configure" without any path to mysql root
(as state in the above php manual link you provided)

This time I just use the same option in 4.3.3, I got a warning from "configure" that only the PHP build-in mysql support was used.
(that means the --with-mysql option is not enabled)

So I reconfigure again with --with-mysql=/usr/local/mysql

This time the warning disappered.
(that's means the Local MySQL installation was used instead of PHP build-in mysql support)

May be that is the cause of my problem.