I really need help with MySQL - somethin just ain't right!

[rosey]

Ok I'm back again.  My forum is still having problems and I have tweaked everything I can think of to tweak.  Is there something glaringly obvious that I am missing?

http://www.thatboard.com/board/status.php
http://www.thatboard.com/board/phpinfo.php

I am using 1.1.RC2

I am on a VPS with 512MB guaranteed RAM, 2GB burst RAM.  Persistent connections is on.  Database driven sessions is enabled.  Attachments and attached avatars are disabled.

Do you have eAccelerator or another optimizer installed?  Yep, caching on at level1
Do you have a lot of posts?  If so, have you converted any of your tables to InnoDB? I did but that made it worse so I converted them back except for my search_log tables
Have MySQL's settings been tweaked to optimize memory usage and general efficiency?well I am trying, that is what I'm hoping for help with  my.cnf looks like this:

sort_buffer_size=2048K
read_buffer_size=2048K
max_connect_errors=99999  <-we just changed this, it was 10 before - is this why it kept locking up?
read_rnd_buffer_size=2048K
myisam_sort_buffer_size=32M
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=12288

[mysqldump]
quick
max_allowed_packet=32M
max_allowed_packet=32M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout



when I went to reboot the mysql this morning I got this error:

/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't
connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
(2)' Check that mysqld is running and that the socket:
'/var/lib/mysql/mysql.sock' exists! mysql has failed, please contact the
sysadmin (result was "Warning, no valid mysql.sock file found.mysql has
failed").

but then it started up anyway.  could this be my problem?  I am so confused and I feel like my head is going to explode.

[rosey]

Ok I think we have narrowed down the problem to locked queries.  For example, right now I have

127 (1 sleeping, 2 running, 124 locked)

whenever this happens the board slows to a crawl, once the locked queries stop, the board goes back to normal.

what causes locked queries and how do I a) find out which one is locking and b) fix it??

[Ben_S]

Mainly search, are your log_search_* tables InnoDB?

[rosey]

yeah, they are.  I converted the rest back to MyISAM b/c it was really slowing down the forum, but I left those.  I am wondering if I should convert them back too though.  I know InnoDB is supposed to help make it faster but it really seemed to do the opposite.

on this forum, sometimes when I search it says "due to high load on the server, search has been automatically and temporarily disabled" - it never says that on my forum.  where do I turn that on??

I turned off persistent connections to see if that would help with the locking, and right now I have 0 locked so maybe that was it.  I have tried so many things it is hard to know what is helping and what is hurting.  I am just at a loss as to why my forum is so slow and using up so many resources.  Could my databse be corrupt somehow??

[Ben_S]

Also try reducing these by half -

sort_buffer_size=2048K
read_rnd_buffer_size=2048K

These are per connection settings, you max used connections is 151 which means that there alone you were using 604MB ram.

Persistant connections should help as there is less overhead having to open connections all the time.

[Ben_S]

on this forum, sometimes when I search it says "due to high load on the server, search has been automatically and temporarily disabled" - it never says that on my forum.  where do I turn that on??

Run through phpMyAdmin

REPLACE INTO smf_settings
   (variable, value)
VALUES ('loadavg_enable', '1'),
   ('loadavg_forum', '10.0'),
   ('loadavg_auto_opt', '1.0'),
   ('loadavg_search', '2.5'),
   ('loadavg_allunread', '2.0'),
   ('loadavg_unreadreplies', '3.5'),
   ('loadavg_show_posts', '2.0');

You will want to tweak the load values to be appropriate for your server, set them too low and stuff will be disabled all the time, set them too high and it will be of no use. Set loadavg_forum too low and your forum will be offline - hence the reason there isn't an admin interface for this, forcing you to do it through phpMyAdmin means you will be able to rrun it off if you mess it up.

[rosey]

thanks. 

we'll see how it goes.