Extremly slow gallery

[Lucifix]

With help of others I have managed to move my gallery on new server. Everything seemed to be good but when I open gallery for public disaster happened. My server went down for few minutes till I restart it and disable gallery for other members.

At first I thought that this might be problem with so many modifications in my gallery so I decided to install in new directory new 1.7.6 gallery and connect it to existent database. Effect was the same. I thought that this is the problem with server, but I decided to create new database and install new tables. Then everything is running smoothly.

So the problem most be in existent database (optimization etc didn't work). I think the only problem is that I made many many new rows in 4images_users, 4images_images tables.

Right now my gallery is closed and 6.000 members can't even login, because the server will crash. Did anyone else have the same problems as I and solved them?

I beg you to help me 

[kai]

On your old server the gallery worked well?

[Lucifix]

The problem started few months ago and it just keep bigger. Gallery was the main cause that we moved site to another server.

I'll try to remove different functions in gallery to eliminate cause. My site has 41 different categories and 26.000 photos - I think this might be the main cause. I'll try to delete some categories just to see if anything will be better, but I still doubt that my gallery would so easily crash my server.

[Lucifix]

The problem seems to be in few SQL queries which takes over 1-2 secunds.

here is one of them:

Code: [Select]

SELECT i.image_id, i.cat_id, i.user_id, i.image_name, i.image_date, i.image_purpose, i.image_glasovi, i.image_points, i.image_active, i.image_media_file, i.image_thumb_file, i.image_allow_comments, i.image_comments, i.image_votes, i.image_rating, i.image_hits, c.cat_name, u.user_name
FROM (
4images_images i, 4images_categories c
)
LEFT JOIN 4images_users u ON ( u.user_id = i.user_id )
WHERE i.image_active =1
AND c.cat_id = i.cat_id
AND i.cat_id !=24
AND i.cat_id NOT
IN ( 0 )
AND i.image_glasovi >=4
ORDER BY i.image_date DESC
LIMIT 0 , 28
Anyone have idea how to optimize it?

[KurtW]

Hi Lucifix,

4images have mass of .php files in different folders.
And you thing, every one now, where this code comes from 


Kurt

[Lucifix]

The problem here is not in PHP but in mysql query (tables)

This one is taken from index.php.

[Nicky]

hi Lucifix,

pls. add all your 4images files to a zip
make a backup of mysql database.

place it anywhere on your server and let me know, over PM, where i can download it.
i will test this on my server next days..

[Lucifix]

Okey I'll let you know when I'm done, but I can't give you data folder (over 5 GB) and 4images_users table (user privacy) - i'll send you only structure with few rows for testing.

[Nicky]

Lucifix,

okay, data folder i don't need...

4image_users i would like to have... to test it with 6000 users like you said...

copy this table to another table... than do following with new table...

Code: [Select]

UPDATE new_users_table SET user_password = '123456', user_email = '123@123.com';

and please add all rows from your database, to have 1:1 copy of it..

[Lucifix]

Got it! 

You might be my savior

[Lucifix]

Oh I just remembered that my 4images is integrated with Php-nuke so testing on other server won't work.

[Lucifix]

I would just like to let you know, that I solved the problem with my gallery. We change few indexes and now it looks like everything works like charm

[Nicky]

hi Lucifix,

GREAT to hear that!!!
because i visit your site sometimes due you have fine pics on it...