At What Point to make the switch to MyISAM?

[phinsup]

I'm just curious where the breaking point is. My ibftopics table is currently 3.3 gigs in size, is it time for me to switch at least that table to InnoDB? What other tables should I switch if I should switch?

Thanks!

[Clickfinity]

I'm currently wondering the same.

Can we switch ALL tables (with the exception of sessions) to InnoDB?

Cheers,
Shaun :D

[MarcusInMd]

Check your mysql-slow log. If you are having lots of slow queries on the posts and topics tables then it's probably time.

[blair]

Might want to read Brandon's comments here.

I have a 9.2 GB posts table running sucessfully (and swiftly) with MyISAM.

[MarcusInMd]

Might want to read Brandon's comments [url="http://community.invisionpower.com/topic/306225-your-database-engine-of-choice-for-posts-and-topics-tables/page__view__findpost__p__1924007"]here[/url].

I have a 9.2 GB posts table running sucessfully (and swiftly) with MyISAM.

I would have to say that your site must not get slammed a lot then but that is a very large posts table. How many servers are you running?

What kind of traffic do you see and what is your typical i/o on your primary ethernet device?

[blair]

One server, older quad-core (AMD 1354 2.2ghz). 4GB RAM, SAS (sa-scsci) drives RAID1. Nothing special.

Avergage ~1,500+ users online. Record online at once ~8,500 (Aug 2010). Load avg 1.56 over the last month, peak 6.22 (nightly backups).

I/O:


My point is that a decision about whether or not to use InnoDB should be based on more than a table's size.

[Jinkler]

Might want to read Brandon's comments [url="http://community.invisionpower.com/topic/306225-your-database-engine-of-choice-for-posts-and-topics-tables/page__view__findpost__p__1924007"]here[/url].

I have a 9.2 GB posts table running sucessfully (and swiftly) with MyISAM.

What version of IPB are you running out of interest?

[blair]

3.1.2

[MarcusInMd]

One server, older quad-core (AMD 1354 2.2ghz). 4GB RAM, SAS (sa-scsci) drives RAID1. Nothing special.

Avergage ~1,500+ users online. Record online at once ~8,500 (Aug 2010). Load avg 1.56 over the last month, peak 6.22 (nightly backups).

I/O:

My point is that a decision about whether or not to use InnoDB should be based on more than a table's size.

OK, you peaked my interest now.

We peaked at 1725 earlier this year and my single (the DB server is presently offline) Dual Quad Xeon (8 cores) 16GB server with 15k SAS drives raid 10 struggled and this is running litespeed web server too. The server was less than a year old at the time. When I say struggled we would get frequent spikes to 50 to 60 load during very heavy traffic. I would say that all 1700 people were reading in 1 or 2 topics and the rest were posting along with mods deleting lots of crap posts.

What OS are you running, what version of Mysql etc.

I just don't understand why this would happen to us and not you. Our hardware is leaps and bounds ahead of what you have (Not a dig).

When I delete a post right now it takes about 15 seconds for it to actually do the deletion and this is during a non-busy time.

[Clickfinity]

There's something crazy wrong if it takes 15 seconds to delete on that hardware!!!!

How big is your posts table (posts/GB)?

Cheers,
Shaun :D

[MarcusInMd]

That will give you an idea of what our server load is like on any given day. The spikes are during "severe" weather events.


Here is the forum traffic during the same time frame.

[MarcusInMd]

There's something crazy wrong if it takes 15 seconds to delete on that hardware!!!!

How big is your posts table (posts/GB)?

Cheers,

Shaun :D

Tell me about it. IBP has been all over my server this past winter and could not come up with any ideas.

The odd thing is with IPB 2.x I only had slowdowns on our old server which was just a dual AMD server with 2GB. I never had the server spikes like I am seeing now. Running newer versions of Mysql, CentOS too.

[phinsup]

I had HUGE issues with posting and couldn't delete posts, it would just time out. Switched to InnoDB and posts and delete's are almost instant.

[Clickfinity]

The logged-in graph looks odd - like everyone is logging in and out on the same hourly basis, or am I reading this wrong?

Cheers,
Shaun :D

[MarcusInMd]

The logged-in graph looks odd - like everyone is logging in and out on the same hourly basis, or am I reading this wrong?

Cheers,

Shaun :D

You are reading it wrong. The Registered field are members who are registered. Guests are people just viewing the forum but not signed in. It is a snapshot of the forum traffic every 5 minutes.

[MarcusInMd]

I had HUGE issues with posting and couldn't delete posts, it would just time out. Switched to InnoDB and posts and delete's are almost instant.

We don't have those kinds of issues. It's just when we have serious traffic. Right now our forum is flying and usually does, even with 12 to 1400 online. It's just during the very busy periods when we have a ton of people hitting just a few topics at once.

[phinsup]

We don't have those kinds of issues. It's just when we have serious traffic. Right now our forum is flying and usually does, even with 12 to 1400 online. It's just during the very busy periods when we have a ton of people hitting just a few topics at once.

Don't get me wrong man I'm not doubting you at all. I've learned one thing in tweaking mysql... results vary for everyone!

[MarcusInMd]

Don't get me wrong man I'm not doubting you at all. I've learned one thing in tweaking mysql... results vary for everyone!

I did a quick test tonight. I deleted a post from a 1 post topic and the deletion was instant. I then proceeded to delete 1 post from a topic with over 400 replies. It took about 15 seconds to delete. That has got to mean something!!!???

[blair]

What OS are you running, what version of Mysql etc.

64-bit CentOS 5.5, MySQL 5.0.91, PHP 5.2, Litespeed.

When I delete a post right now it takes about 15 seconds for it to actually do the deletion and this is during a non-busy time.

I had HUGE issues with posting and couldn't delete posts, it would just time out. Switched to InnoDB and posts and deletes are almost instant.

We have a slight lag when deleting posts. I haven't timed it, but I'd guess 2-3 seconds at most.

Really the only place I've noticed a lag, was already mentioned above. Click the "Posts" tab in the profile of someone with over 10K posts, and there will be a noticeable lag. We have a member with over 40K posts and it's probably a 20+ sec lag. However, loads barely rise and are very manageable, so I haven't been too concerned. Nightly backups to a local drive also cause load and posting issues for 5-10 minutes (InnodB would likely fix that).

Don't get me wrong, I'm not anti-InnoDB. In fact, I have been considering it for some time. I just haven't been forced into it. There are a few reasons I haven't changed. 1) I'm guessing it will take 4-8+ hours downtime to convert. 2) My SAS drives (10K) are 74GB and if my posts table triples in size, free space becomes an issue. Not to mention the backup drive, CDP offsite backup space, etc. 3) I'm just not familiar with it. I know with MyISAM it's very difficult to damage a table beyond repair (even though it may take a long time). I have no idea how to restore an InnoDB table from a crash, or if it's ever needed. :ph34r:

When I say struggled we would get frequent spikes to 50 to 60 load during very heavy traffic

You can do a lot of good with my.cnf (MySQL config), and to a lesser extent httpd.conf. But you can also do a lot of harm. With loads that high I have to assume you are running out of memory and hitting swap.

[MarcusInMd]

64-bit CentOS 5.5, MySQL 5.0.91, PHP 5.2, Litespeed.

We have a slight lag when deleting posts. I haven't timed it, but I'd guess 2-3 seconds at most.

Really the only place I've noticed a lag, was already mentioned above. Click the "Posts" tab in the profile of someone with over 10K posts, and there will be a noticeable lag. We have a member with over 40K posts and it's probably a 20+ sec lag. However, loads barely rise and are very manageable, so I haven't been too concerned. Nightly backups to a local drive also cause load and posting issues for 5-10 minutes (InnodB would likely fix that).

Don't get me wrong, I'm not anti-InnoDB. In fact, I have been considering it for some time. I just haven't been forced into it. There are a few reasons I haven't changed. 1) I'm guessing it will take 4-8+ hours downtime to convert. 2) My SAS drives (10K) are 74GB and if my posts table triples in size, free space becomes an issue. Not to mention the backup drive, CDP offsite backup space, etc. 3) I'm just not familiar with it. I know with MyISAM it's very difficult to damage a table beyond repair (even though it may take a long time). I have no idea how to restore an InnoDB table from a crash, or if it's ever needed. :ph34r:

You can do a lot of good with my.cnf (MySQL config), and to a lesser extent httpd.conf. But you can also do a lot of harm. With loads that high I have to assume you are running out of memory and hitting swap.

Our sql and litespeed are tweaked about as far as they can go. iowait is not the issue either. With 16gb of ram it's hard to start to to hit the disks. It's something else for sure.