oh no! its slower than molasses ;(

[wkitty42]

i don't understand... i've only got 13 photos in one exhibit and only that one exhibit but i can't get into the exhibit or view any of the photos even though the counter for the photo gets updated.

running "mysqladmin processlist" shows the following [trimmed for space]...
+------+---------+------+------------+----------------------------------------------------------------------------------------------------+
| Id | Command | Time | State | Info |
+------+---------+------+------------+----------------------------------------------------------------------------------------------------+
| 1508 | Query | 2478 | statistics | SELECT ee_photo.ee_photo_id, ee_photo_eng.ee_photo_header, ee_photo.ee_photo_exif_datetime, ee_len |
| 1509 | Query | 2151 | statistics | SELECT ee_photo.ee_photo_id, ee_photo_eng.ee_photo_header, ee_photo.ee_photo_exif_datetime, ee_len |
| 1510 | Query | 1830 | statistics | SELECT ee_photo.ee_photo_id, ee_photo_eng.ee_photo_header, ee_photo.ee_photo_exif_datetime, ee_len |
| 1511 | Query | 1553 | statistics | SELECT ee_photo.ee_photo_id, ee_photo_eng.ee_photo_header, ee_photo.ee_photo_exif_datetime, ee_len |
| 1535 | Query | 198 | Locked | SELECT ee_photo.ee_photo_id, ee_photo_eng.ee_photo_header, ee_photo.ee_photo_exif_datetime, ee_len |
| 1537 | Query | 273 | Locked | UPDATE ee_counter SET ee_counter_value = ee_counter_value+1 WHERE ee_counter.ee_count |
| 1540 | Query | 140 | Locked | SELECT SUM(ee_counter_value) AS hits FROM ee_counter |
| 1542 | Query | 52 | Locked | SELECT SUM(ee_counter_value) AS hits FROM ee_counter |
| 1548 | Query | 0 | | show processlist |
+------+---------+------+------------+----------------------------------------------------------------------------------------------------+

those times leave a lot to be desired... 2400 seconds is 40 minutes :!: in searching out this trouble, i find numerous references to the long periods in statistics status are related to indexes (possible lack of) and/or where mysql is in its "optimize join" phase where it is trying to determine the best way to gather the rows of data... in this phase, mysql will look at every possible way of connecting/relating the data to choose what it thinks is the best way to pull the data with the most restricted output... that's not a good thing if you're pulling data from numerous different places...

everything i've been reading points to using more/better indexes and/or using JOIN to tell mysql how you want the data joined relationally rather than letting it figure it out by itself... its all saying to be specific in what you tell it as well as being specific in what you ask it...

here's a quote from a message in another forum i found thru google...
Brian> Last night our mysql server ate up all the CPU on our machine. A
Brian> processlist revealed about 90 processes with a status of 'statistics'. What
Brian> does that status mean? I assume it means that mysqladmin status or
Brian> mysqladmin extended-status is being run. That could be. Is that what it
Brian> means, and why did it hoe mysql. I gave it 30 minutes to work it out and
Brian> then had to kill the process.

Hi!

'statistics' means that MySQL is in the join optimizer; This may take
a long time if you have MANY tables in a query and no proper indexes.

there's also this article in Linux Magazine (http://www.linux-mag.com/2001-06/mysql_04.html) that talks about these kinds of optimizations.

and this one at databasejournal (http://www.databasejournal.com/features/mysql/article.php/1382791) that goes into a bit more detail on optimizing the database for the queries being performed... i especially like their explanation of using the EXPLAIN command to see what, exactly, a query will do and how the TYPE field in the EXPLAIN results tell us what "level" of performance we'll get...


EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

So what are all these things?

* table shows us which table the output is about (for when you join many tables in the query)
* type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
* possible_keys Shows which possible indexes apply to this table
* key And which one is actually used
* key_len give us the length of the key used. The shorter that better.
* ref Tells us which column, or a constant, is used
* rows Number of rows mysql believes it must examine to get the data
* extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Looks like our query is a shocker, the worst of the worst! There are no possible keys to use, so MySQL has to go through all the records (only 2 in this example, but imagine a really large table).

FWIW: i've seen this is numerous other mysql applications... EE is not the only one afflicted with this particular "lack of speed" disease... a recipe database app that i have suffers greatly from this exact same problem but its reason is very different than EE's... i've not yet dug into the code to try to locate where JOIN may be used or if other indexes would be better for solving this problem...

Pekka, you know your code... i'd have to learn it and your style to be able to solve this in a timely fashion... i don't know if you are using temporary databases or arrays or what... this is also a problem that i see in v1.22 and it can make things slow enough that on 10mb and 100mb internal LANs that folk drop the connect and go on to other pages... witness the processlist chart i posted at the top of the message... v1.22 doesn't have the databases to dig thru that 1.5 does so 1.22 is not as afflicted with this problem as 1.5 but they are both still afflicted...

if you can solve this, your application will be a whole lot snappier and faster... very definitely on older and slower equipment than the bleeding edge stuff out there today... everything you can do to make the app as fast as possible is better than features and eye candy... don't get me wrong, features and eye candy are good too but no one wants a porche that only goes 15kph :wink:

PS: please don't think that i'm attacking, whining or anything similar... i hope that the above are helpful and don't come across childlike or belittling... that is not my intent... my intent is to assist you in creating the best application that you can create... i've been doing this stuff for 25+ years now... coding, developing, and all that, but i'm still a young man however i get very burned out these days from way too much time behind the screen... weeks of work packed into days can do that to a person :shock:

keep up the excellent work! oh! and no worries on you sleeping while i'm awake... i'll catch up with you some time and we'll both be awake at the same time until i cycle on around again :wink: :wink:

[wkitty42]

whoa! :shock: :shock:

out of frustration, i went and ran the optimize functions from the input index... the database server churned for less than a minute for each of the buttons and returned OK for all tables... then i went and accessed my main index, no problem (wasn't before) and then selected an exhibit to view (where the problem was) and BANG!! there it was... fully loaded and viewable... selecting a pic to view is also very fast...

i don't know what the problem was before unless it was just the databases being out of whack or something :oops: no clue...

in any case, i do hope that my previous message is of some assistance... especially when databases are sitting in the "join optimizer"...

/me whispers: "why does the wierd stuff always happen to me?"

[Pekka]

Well, the short reply to this is: that is why the optimize tools are in EE :)

Usually new install does not have indexes working before optimize. One way to kickstart them is to use EE's optimize commands, one is to do backup/restore in EE Backup. If you put EE in debug mode (misc settings) it will show index usage on list page. I spent most of the summer optimizing list page and the result is now quite satisfactory to me. Only thing left as side effect of "super-optimizing" the total weirdness of MySQL when EE has 1-6 photos: MySQL chokes in lack of indexes.

Note also that to accelerate EE you'll need to have few photographers, workflows, lenses, cameras, locactions, categories, exhibitions in database - the more the merrier. Usually e.g. 5 of each is needed to gain full speed (i.e list page main query takes about 9-15ms, 10 photos per page). This is so because to take full advantage of index system MySQL needs data.

You can see the main query timer on index page when you put your mouse on EE logo.

Next I will concentrate on optimizing photo page (not much to do there perhaps) and index page (lots of slow code there).

[wkitty42]

Well, the short reply to this is: that is why the optimize tools are in EE :)
ahhh... i don't recall this option in EE v1.22... guess i'll have to have a go at the sources and implement it... v1.22 is very slow on my server...

Usually new install does not have indexes working before optimize.
wierd... i've done database work for many years and not seen this problem... maybe it is specific to mySQL or just SQL stuff... i dunno... most of my work is/was in the old borland dBase stuff... i'm slowly working into SQL stuff...

One way to kickstart them is to use EE's optimize commands, one is to do backup/restore in EE Backup. If you put EE in debug mode (misc settings) it will show index usage on list page. I spent most of the summer optimizing list page and the result is now quite satisfactory to me. Only thing left as side effect of "super-optimizing" the total weirdness of MySQL when EE has 1-6 photos: MySQL chokes in lack of indexes.
that is interesting... you've done excellent work on the package, that's for sure... i'm glad to have found it and others because they assist me in my learning endevors...

Note also that to accelerate EE you'll need to have few photographers, workflows, lenses, cameras, locactions, categories, exhibitions in database - the more the merrier. Usually e.g. 5 of each is needed to gain full speed (i.e list page main query takes about 9-15ms, 10 photos per page). This is so because to take full advantage of index system MySQL needs data.
so i've found... the more i do with mySQL, the more i learn but that's normal anyway ;)

You can see the main query timer on index page when you put your mouse on EE logo.
interesting... i wasn't aware of that... will have to look...

Next I will concentrate on optimizing photo page (not much to do there perhaps) and index page (lots of slow code there).
cool... i'm eagerly awaiting the conversion code to move from v1.22 to the latest... manually reentering all the stuff for all the pictures i currently have is a bit of a PITA... i am currently having the old version open in one window and the new version in another and then copying and pasting the text for each... one of my "exhibits" is a gallery of pics from an engine rebuild... there's some 60 or so pictures in there alone... another is a gallery of my fish and pets and friends pets... quite a few in there, as well... my biggest (and so far most ignored) project is pics from the local flooding at the river nearby...

i'm sure glad this is a hobby for me... lord knows that i don't make any money at it 8) that's never been my intention, anyway... at least, not with this particular site...

question: why does the license for the latest say that it cannot be operated in a production environment? to me that means that i can't allow my visitors access to it... am i misunderstanding this?