Going big

[gobzer]

I've chosen Chevereto as a primary platform for my old pic-service.
After a long and painful migration I realised that script works too slow. Even without load.
I got 6M pics, 37K albums and 1M users.
Script responds to album requests or user's pics requests about 4-8 seconds.

My hardware:
2 x Intel Quad-Core Xeon E5620
32GB RAM
SSD 120GB

All pictures stored on extrenal storage.

I've red https://chevereto.com/docs/going-big
But caching wont speed up slow requests.

Any ideas?
I'm sure MySQL is a bottleneck.
How can we speed up that requests?

SELECT * FROM chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
WHERE image_user_id=:user_id
AND chv_images.image_nsfw = 0 AND (chv_albums.album_privacy NOT IN ('private','private_but_link','custom') OR chv_albums.album_privacy IS NULL OR chv_albums.album_user_id IS NULL) ORDER BY image_id DESC
LIMIT 2224,16

 

[gobzer]

First improvement:

ALTER TABLE `chv_images` ADD INDEX (`image_user_id`);

Second improvement:

ALTER TABLE `chv_albums` ADD INDEX (`album_user_id`);

More on albums:

ALTER TABLE `chv_images` ADD INDEX (`image_album_id`)

Not a super-improvement, but it speeds up something:

ALTER TABLE `chv_images` ADD INDEX (`image_category_id`), ADD INDEX (`image_storage_id`);

 

[gobzer]

Request on explore - all:

SELECT COUNT(*) AS total
FROM chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
ORDER BY image_id DESC

1. Why do you ORDER COUNT? COUNT is just an integer. You waste resources on ordering it (update: that's not true)
2. Why do you LEFT JOIN all that shit? you don't filter anything

 

[gobzer]

Next challenge: Explore - ALL, distant page

SELECT *
FROM chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
WHERE chv_images.image_nsfw = 0 AND (chv_albums.album_privacy NOT IN ('private','private_but_link','custom') OR chv_albums.album_privacy IS NULL OR chv_albums.album_user_id IS NULL) ORDER BY image_id DESC
LIMIT 5443376,16

 

[Rodolfo]

Actually the only really slow query that needs to be fixed is the user albums query which has an awful foreach. Problem with this query are only visible when your system has a huge number of connections, just noticed it few days ago when someone grant me access to his very crowded website.

I forgot to add the indexes for the foreign keys, but those don't make a huge difference. Like I said, the really important issue is the awful user albums query.

The counts will be optimized but don't expect an awesome improvement, the issue is elsewhere.

 

[Rodolfo]

table.

1. Why do you ORDER COUNT? COUNT is just an integer. You waste resources on ordering it
2. Why do you LEFT JOIN all that shit? you don't filter anything

1. It doesn't add anything. Is a sort in a single row return.
2. Because there are complex listing requests, like this one:

SELECT COUNT(*) as total FROM chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
WHERE image_user_id=:user_id
AND chv_images.image_nsfw = 0 AND (chv_albums.album_privacy NOT IN ('private','private_but_link','custom') OR chv_albums.album_privacy IS NULL OR chv_albums.album_user_id IS NULL) ORDER BY image_id ASC

Actually, now that I see the query profile, problem aren't the JOIN statements, is the implicit WHERE/ORDER clause, which should be: table.column. That saves 0.001s which is nothing to be honest. Like I said, the problem is elsewhere and I've already mentioned where. I will fix it, don't panic.

P.S. I will add the indexes and change the implicit clauses in the next version.

 

[gobzer]

To filter safe images faster:

ALTER TABLE `chv_images` ADD INDEX (`image_nsfw`);

I was wrong about wasting resources on ordering count.
Now the most of the problems lies around Explore feature. I think I'll just disable that until improvements will be made.

 

[Rodolfo]

I've noticed that in very large installations the non-indexed columns used either WHERE clauses or in ORDER statements impacts very badly the mysql performance.

So you are right in this, my fault was not testing the MySQL performance in an actual large website which I had the chance now (2.7 M images). The impact in a large installation is huge, no question about it.

ALTER TABLE `chv_albums` ADD INDEX (`album_user_id`), ADD INDEX (`album_privacy`);

ALTER TABLE `chv_images` ADD INDEX (`image_user_id`),
ADD INDEX (`image_album_id`),
ADD INDEX (`image_category_id`),
ADD INDEX (`image_storage_id`),
ADD INDEX (`image_nsfw`);

I will add the missing indexes and other MySQL improvements to the next release like an auto explicit fixer for any ->setWhere() clause. I've also improved the album slice query, instead of doing n query->exec now it will do just one query with an UNION ALL statement which is a notorious improvement.

For the record, I'm planning to add a MySQL file based cache for some queries for future releases, this will be great for those who have OPCache because the results will be instant and the MySQL server won't get stressed.

Thanks for your insights!