help tweaking MySQL

[rosey]

512MB guaranteed RAM
2GB burst RAM

(Overseer has a lower tier plan (256M/1GB burst) - his forum is a lot bigger and he isn't having these problems.)

[Ben_S]

How much traffic are you getting, looking @ big boards http://www.big-boards.com/board/1650/ you were doing about 4k posts a day which is presumably a lot of visitors.

Have you installed eAccelerator or APC? If so have you enabled caching - Admin > Server Settings > Caching? If you havent, you should.

[rosey]

I did install eAccelerator, but when I go to server -> caching it tells me "SMF has not been able to detect a compatible accelerator on your server."

here is my phpinfo.php

we usually have about 100 users logged in, up to 170 at peak times

[Ben_S]

eAccelerator isn't installed according to that, how did you install it, did you add the required stuff to php.ini?

[rosey]

I installed it using these directions as well as the eAccelerator directions on this site.  I added this section to the end of my php.ini:

zend_extension="/usr/lib/php4/eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/tmp/eaccelerator"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="1"
eaccelerator.compress_level="9"

per step 5 in the wiki instructions...

thank you so much for helping me.  small fish -> big pool -> deep end!

[rosey]

I added that section to /usr/local/lib/php.ini
but now I see there is also a /usr/local/cpanel/3rdparty/etc/php.ini - should I have added it to that one instead?

ugh and a /usr/local/cpanel/3rdparty/lib/php.ini

that is confusing enough...

[rosey]

ok when I run

$ php -v

it says:

Failed loading /usr/lib/php4/eaccelerator.so:  /usr/lib/php4/eaccelerator.so: cannot open shared object file: No such file or directory
PHP 4.4.1 (cli) (built: Mar  3 2006 11:05:12)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
    with Zend Extension Manager v1.0.8, Copyright (c) 2003-2005, by Zend Technologies
    with Zend Optimizer v2.5.10, Copyright (c) 1998-2005, by Zend Technologies

so uh... that doesn't seem good.  lol.

[rosey]

allllll righty.  I got eAccelerator properly installed and have enabled Level 1 caching.  I turned off "database driven sessions" because I don't think I have a load balanced server.  After reading all 4 pages of the InnoDB thread I am considering converting my tables back to MyIsam...

the caching does seem to be helping.  but the load is still cycling.  fast, slower, slow like molasses, better, fast again.  lather, rinse, repeat! 

[Ben_S]

I'd re-enable database sessions in my experience they made a huge difference in performance. As for InnoDB, you deffinatly want to leave the log_search* tables as InnoDB