corrupt smf_sessions table and slow forum

[shakin]

Most importantly, I keep getting this error: Can't open file: 'smf_sessions.MYI'. (errno: 145)

So far I've got that error twice today, both times fixed by repairing the table. The forum has worked perfectly since March, but since yesterday I have had several times more traffic than usual and I had to increase my MySQL max_connections from 100 to 250 and also disable persistent connections (needed to free up connections quicker).

What I'm wondering is if there is a way to prevent the smf_sessions table from becoming corrupt. Is the problem due to lots of visitors (200+ on one forum and 100+ on a separate SMF installation using the same server), though I see the Simple Machines forum has more than that? Could it have to do with the connection changes I made yesterday? The forum does seem slow, which could be because of the high traffic or because I disabled persistent connections. Is there a better way to handle high traffic than what I did?

Thanks.

[Ben_S]

root@shankly [~]# perror 145
MySQL error:  145 = Table was marked as crashed and should be repaired


Is this a dedicated server of shared hosting, and what spec is the server. Tables crashing are usually as a result of MySQL being stopped incorrectly.

I'd leave persistant connections enabled and make sure that your apache max clients is set to lower than your max mysql connections.

Also can you upload this file and post the url? http://www.simplemachines.org/community/index.php?topic=43207.msg312161#msg312161

[shakin]

root@shankly [~]# perror 145
MySQL error:  145 = Table was marked as crashed and should be repaired


Is this a dedicated server of shared hosting, and what spec is the server. Tables crashing are usually as a result of MySQL being stopped incorrectly.

I'd leave persistant connections enabled and make sure that your apache max clients is set to lower than your max mysql connections.

Thanks for the reply. This is an in-house server that runs two SMF forums and one low traffic web site, with MySQL on the same server. There is no indication that MySQL has crashed or been shut down in any way. The smf_sessions table is the only one to have any problems.

The server is a P3 1ghz with 768 MB RAM. I will look into the Apache max clients and related settings, but it's more than a bit scary when a table becomes corrupt twice within a couple of hours. I'm really worried about that. There were 586 records in the table when I did the repair.

[Ben_S]

It could be that MySQL was so overwhelmed with the traffic it crashed itself.

I edited my post whilst you were trying, take a look at the status script I linked too.

What version of MySQL are you running, if it's 3.x then you should upgrade as SMF is highly optimized for 4.x. Also do a search for InnoDB on these forums, you can get some major performance gains by switching certain tables to InnoDB (search for the list of ones to change).

[Ben_S]

You may want to gives these a try.

ALTER TABLE smf_topics
ADD INDEX numReplies (numReplies),
ADD INDEX numViews (numViews);

DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) TYPE=HEAP;

ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;

REPLACE INTO smf_settings
   (variable, value)
VALUES ('disableTemplateEval', '1'),
   ('cache_enable', '1');

And, use a PHP accelerator like  eAccelerator or Turck MMCache.

-[Unknown]

[[Unknown]]

The output of the status.php script can tell us a lot about your server and what might be causing slowdowns.

-[Unknown]

[bjp]

You may want to gives these a try.

ALTER TABLE smf_topics
ADD INDEX numReplies (numReplies),
ADD INDEX numViews (numViews);

DROP TABLE smf_log_floodcontrol;
CREATE TABLE smf_log_floodcontrol (
  ip char(16) NOT NULL,
  logTime int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (ip),
  KEY logTime (logTime)
) TYPE=HEAP;

ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;

REPLACE INTO smf_settings
   (variable, value)
VALUES ('disableTemplateEval', '1'),
   ('cache_enable', '1');

And, use a PHP accelerator like  eAccelerator or Turck MMCache.

-[Unknown]

I am very interesting. Can we do this with smf 1.1 ?
An other question, can we empty 'smf_sessions (not delete) ?
And last : what is the benefit with InnoDB

[[Unknown]]

That works with 1.1, yes.

Emptying the table will cause temporary problems for everyone currently using the forum, but said issues will quickly dissappear.  But, it's not recommended.

InnoDB is slower to read, faster to write and read concurrently.  Meaning, if I have some data that no one is going to write to, I want it MyISAM... but if anyone's going to write to it, it may be better as InnoDB because with InnoDB people can still read from (parts of it) while it's being written to.

Think of it like this... InnoDB needs glasses, but also has more slender hands to write with.  MyISAM has much more oafish hands, but eyes like a hawk.  It's just a trade-off in either case.

-[Unknown]

[shakin]

Sorry for the delay, but here's my status.php: http://chat.mapleleafs.com/dave/status.php [nofollow]

I've been tuning mySQL max_connections and Apache's max clients and have achieved moderate success. It looks like I need to adjust mySQL's table_cache, key_buffer_size and thread_cache_size. The forum is slow now, so now is a good time to look.

I'm only marginally familiar with mySQL tuning (done more DB2 work) and am looking into how to modify these values. I'm running two reasonably busy SMF forums on this server (150+ users on each right now).

I've also been getting so many corrupted smf_sessions tables that I'm considering switching that away from the DB.

Thanks

[[Unknown]]

The load averages are terrible: 28.34, 29.93, 34.92.  MySQL likely did crash, indeed.

Your table_cache could definitely be higher.  You're not using any swap yet, so there's room for more memory use.  I would set it to *at least* 512.
I would set your thread_cache_size to at least 32.
I would set query_cache_size to 32M or 48M.
I would set key_buffer_size to 12M.

But, while MySQL isn't perfectly configured, that's not even the most of the problem.  The process usage is mostly Apache:

[httpd] (132) 137.9%, [mysqld] (209) 11.0%, (other) (36) 0.0%

This tells me you don't have an accelerator installed, although I may be wrong.  Can you post a phpinfo?  Have you heard of eAccelerator?  Do you have root access to the server?

-[Unknown]

[shakin]

The load averages are terrible: 28.34, 29.93, 34.92.  MySQL likely did crash, indeed.

Your table_cache could definitely be higher.  You're not using any swap yet, so there's room for more memory use.  I would set it to *at least* 512.
I would set your thread_cache_size to at least 32.
I would set query_cache_size to 32M or 48M.
I would set key_buffer_size to 12M.

But, while MySQL isn't perfectly configured, that's not even the most of the problem.  The process usage is mostly Apache:

[httpd] (132) 137.9%, [mysqld] (209) 11.0%, (other) (36) 0.0%

This tells me you don't have an accelerator installed, although I may be wrong.  Can you post a phpinfo?  Have you heard of eAccelerator?  Do you have root access to the server?

-[Unknown]

Thanks. I've made the suggested changes and installed eAccelerator. I had phpAccelerator installed, but disabled it months ago due to stability problems. I rebooted and the server appears to be working much better, although I will still need to tweak the mySQL settings a bit. It'll require some monitoring before I can tell for sure if the performance problems have been taken care of.

I only wish I had done the mySQL and eAccelerator changes separately so I could tell what impact each change had. I will test that another time.

Many thanks.

[[Unknown]]

I'm sure your forum is probably less active now than it was before, as it only has 135 users online just now (half, I assume, what it was earlier?) but it seems to be doing significantly better.

As compared to being dangerously high at 30, the load averages are now 0.45, 0.65, 0.81.  And, considering it's only a P3 966 with 512 megs of ram, that's doing pretty well .

Currently, it appears as if the table_cache (of 512) was overdoing it.  However, it's quite possible that when it gets back to peak time you'll want that extra cache.  I'd check back and see if "Table cache usage" is in the described range then.

The thread_cache_size could probably be bumped up to 36 or even 40 to benefit, but I'd have to see it at peak times to give a better estimate.  It's probably fine where it is now.

Increasing tmp_table_size to 48M may also be a good idea, but shouldn't be necessary and won't make hardly as much a difference.

Does the forum typically see heavy usage at 8:00 pm?

-[Unknown]

[bjp]

Thanks a lot [Unknown] for this advice.

In My.cnf there is :

Code Select Expand


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
#key_buffer = 256M
key_buffer=150M
thread_stack=128K
max_allowed_packet = 1M

table_cache = 512
#sort_buffer_size = 1M
#read_buffer_size = 1M
myisam_sort_buffer_size = 64M
#thread_cache = 8
#query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
#thread_concurrency = 8

query_cache_limit=1048576
query_cache_size=26214400
query_cache_type=1

wait_timeout=600
interactive_timeout=1200
max_connections=256
max_connect_errors=10000

------------------

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/keopanel/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/keopanel/mysql/var/
#innodb_log_arch_dir = /usr/keopanel/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
My serveur is a P4 3Ghz, 1Gig ram
Can you give me some advice please for the lines I sould uncomment ang the goods values ?

[[Unknown]]

#key_buffer = 256M
key_buffer=150M

You might be able to get away with the higher number if you have a gigabyte of ram (I assume that's what you meant because the thought of a server in production with a single megabyte scares the goodness out of me.)  How much of it is generally used at a time?  Without context, it's hard to make recommendations.

#thread_cache = 8

For some, this is a rather unpopular setting.  The thing is, it affects how many processes of MySQL are running at once.  However, it's generally a good idea to let this number be a bit high because in the long run it means less forking.

-[Unknown]

[[Unknown]]

You're using Turck MMCache.  Personally, I've had very good experience with its successor, eAccelerator (basically just a version of Turkc MMCache that is still being updated.)  However, that's good news to begin with .  You're also using a recent version of PHP (although you are behind one version.)

Because of your open_basedir settings, I can't see much about load averages or other server information.

If this server is dedicated, you might try using persistent connections in Admin -> Server Settings.

All that said, your forum isn't currently large enough to make any clear judegements I'm afraid.

-[Unknown]

[bjp]

Sorry, anglish is not my native language.
I do not understand what you mean here :

Code Select Expand

You might be able to get away with the higher number if you have a gigabyte of ram (I assume that's what you meant because the thought of a server in production with a single megabyte scares the goodness out of me.)  How much of it is generally used at a time?  Without context, it's hard to make recommendations.

[bjp]

For the moment it' a test forum. On my main forum phpbb that will be convert in a few days, I have 100 000 new messages every 6 month.

[bjp]

Sorry to ask again, but what  must I do with this ?
Whitch line i must uncomment, and what are the goods values ?
Thank you !

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/keopanel/mysql/var/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/keopanel/mysql/var/
#innodb_log_arch_dir = /usr/keopanel/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50