MySQL Performance / my.cnf suggestions

Voodootool

Joined: 2006-12-02
Posts: 14
Posted: Fri, 2007-03-30 19:18

Hi,

i run gallery2 on a virtual dedicated server with 512MB fix, no burst/swap, ~1.4GHZ Pentium D as standalone site with about 1800 albums and about 150000 items. Its not hard to find out that mysql is a real cpu time and memory eater so i ask if someone has a gallery2 optimized mysql config to maximize performance. Finding the latest 100 images takes about 5 seconds with one single user online. Thats imho way to slow. ZendOptimizer and eAccelerator are installed.

Any suggestions are very welcome!

Here is my current my.cnf (100th version :)
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
bind-address = 127.0.0.1
skip-locking
skip-bdb
skip-networking
skip-name-resolve
skip-external-locking
skip-show-database

key_buffer = 64M
table_cache = 128
sort_buffer_size = 2M
record_buffer=1M

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 64M
sort_buffer_size = 64M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M

[mysqlhotcopy]
interactive-timeout

Here is phpinfo: http://www.celebrityimagedatabase.com/phpinfo.php

Here the stuff gallery2 spits out:
Gallery version = 2.2-rc-2 core 1.1.30
PHP version = 5.2.1 apache
Webserver = Apache/1.3.37 (Unix) PHP/5.2.1 mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 mod_ssl/2.8.28 OpenSSL/0.9.7a
Database = mysqlt 4.1.21-standard, lock.system=flock
Toolkits = ArchiveUpload, Exif, ImageMagick, LinkItemToolkit, SquareThumb, Thumbnail
Acceleration = full/21600, full/900
Operating system = Linux cid 2.6.9-023stab040.1-smp #1 SMP Tue Jan 16 00:54:22 MSK 2007 i686
Default theme = matrix
gettext = enabled
Locale = en_US
Browser = Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3
Rows in GalleryAccessMap table = 398
Rows in GalleryAccessSubscriberMap table = 149708
Rows in GalleryUser table = 2
Rows in GalleryItem table = 149670
Rows in GalleryAlbumItem table = 1902
Rows in GalleryCacheMap table = 4753

 
joe7rocks
joe7rocks's picture

Joined: 2004-10-07
Posts: 560
Posted: Tue, 2007-04-03 21:57

How do you search for latest 100 images?

My gallery 2 about dogs

 
Voodootool

Joined: 2006-12-02
Posts: 14
Posted: Wed, 2007-04-04 16:37

@joe7rocks:
Dynamic Albums
http://codex.gallery2.org/Gallery2:Modules:dynamicalbum

@G2 Coders:
Maybe i am already weird due to my fantastrillion tries to speed up g2 without any help from anyone but is it correct that g2 caches completely rendered pages into the database to "speed up things"? Doesn´t that stress the database more than speeding up anything? My gallery should have about 10000 individual pages but its impossible to render and cache all within an acceptable expiration time. Anyway, does it make sense to turn on caching on large galleries? On the other side, G2 caches database data on disk to speed up things. I am somehow lost here...

 
joe7rocks
joe7rocks's picture

Joined: 2004-10-07
Posts: 560
Posted: Wed, 2007-04-04 16:54
Quote:
Doesn´t that stress the database more than speeding up anything? :

Shouldn't, works for me with 0,5M+ pics :)
It could/would be complicated to store pagelevel cache with timestamp, identifiers/keys, etc on disk, that is what db for usually.

Quote:
My gallery should have about 10000 individual pages but its impossible to render and cache all within an acceptable expiration time:

That's why it's not a goal here. Cached files are stored once they were generated anyway, thus after a hit to a specific page (with specific user,language,etc combination).

Quote:
Anyway, does it make sense to turn on caching on large galleries?

It worth a try, especially if your <number of visits in interval>/<updates or inserts in interval> is high enough, and that is the fact most of the times :)
(I recommend you to try full with 24/48H or even more)

Quote:
On the other side, G2 caches database data on disk to speed up things

On the other hand it makes sense to store data on _disk_ which would be grabbed from db very often and otherwise easy to manage on disk too.

 
joe7rocks
joe7rocks's picture

Joined: 2004-10-07
Posts: 560
Posted: Wed, 2007-04-04 17:07

Ok..let's share some general advice ;)

First of all:
Forget about settings like:
key_buffer = 64M
table_cache = 128
sort_buffer_size = 2M
record_buffer=1M

Gallery2 uses INNODB tables, thus these variables won't really help you out.
Look up innodb_* variables, and play with them (there are quite a few guides out there+ mysql docs)

5seconds for 100 images is pretty long, agreed.
With that amount of pics -and probably dozens of hits/sec- I'd consider introducing some caching.
Dynamic Album doesn't have builting caching (yet).

Other: Have u taken a look at http://codex.gallery2.org/Gallery2:Performance_Tips ?
Other2: Are you sure you are the only one on your site? Eg. aren't there many robots/others hitting dynamic pages like this?

My gallery 2 about dogs

 
Voodootool

Joined: 2006-12-02
Posts: 14
Posted: Wed, 2007-04-04 18:11

Oh i see, you have to be rude to get some attention :) Well, thanks alot for this hint with innodb-tables. Will try to find usefull infos out of hundrets of different statements about each setting of mysql. Iam not alone on my site so i can not benchmark currently. If Dynamic Album doesn´t cache, does rss? I have a few readers getting the latest 32 updated albums+sub-ablums with ttl=600. Ill go and try tweaking, heard G2 runs quiet fast on NEC´s Earth Simluator :)

Yes i read the performace tipps. "MySQL, PostgreSQL, Oracle, and DB2 have all various configuration settings like buffer size etc. And one can improve performance considerably if the DB server is properly configured. " helped alot ;)

Uhmm what i read about InnoDB so far, its more about security / fault tolerance than performance. Makes things not a bit easier.

 
joe7rocks
joe7rocks's picture

Joined: 2004-10-07
Posts: 560
Posted: Wed, 2007-04-04 20:49
Quote:
Uhmm what i read about InnoDB so far, its more about security / fault tolerance than performance. Makes things not a bit easier.

Then you might want to check out the first 3-5 sites of these:
http://www.google.com/search?q=innodb+performance&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:hu:official&client=firefox-a

 
Voodootool

Joined: 2006-12-02
Posts: 14
Posted: Wed, 2007-04-04 21:09

I installed my gallery using mysqlt. (mysql 3+). I run php5 compiled with mysqli support. That is needed to use innodb tables but i have not found any mysqli.so module inside my installation, so i canceled this and got some pizza.
Is there any way to convert to innodb without becoming a linux-guru with long hair? I hate linux, i f***ing hate it... ;)

 
Voodootool

Joined: 2006-12-02
Posts: 14
Posted: Sat, 2007-04-07 07:21

I converted the database tables to innodb but i can not tell anything about performance because i had to delete all caches for a sql dump. Will come back if i have some results. Thanks for your help, joe!

edit:
Looks like converting to innodb gave a big performance boost. I also turned of ImageFrames because it looks like apache has to serve about 10 requests for each thumbnail on the page, that caused heavy load too. I am still adjusting my robots.txt to prevent google scanning urls like "/popular?" and "/key?" etc.

So far....

 
joe7rocks
joe7rocks's picture

Joined: 2004-10-07
Posts: 560
Posted: Wed, 2008-09-17 16:29

Bump! :)
If you read this and hosting millions of images with G2, it's your place to share Gallery2 performance tips and special Mysql/other tuning advices. Let's rock :>

Paprika | Dog Photos <-G2