MySQL: MyISAM or INNODB?

[Shamil]

Recently, vBulletin suggested that we convert our tables and engines to INNODB, slating many performance improvements, such as: faster query times and fewer table-locking issues.

Now, INNODB doesn't support FULLTEXT searching, which means that an external searching mechanism needs to be used.

What do you recommend and what schema will XF be using?

 

[HannahP]

I presume the only way to do a backup on an InnoDB is to do it via an SQL dump?

I believe that's what our admins reverted to after initially having problems with their normal backup routine due to locked rows/tables. We debated not allowing InnoDB for that reason and some other technical reason that I don't recall, but the marketing guys wouldn't let us do it.

I should point out that I work for a host that specializes in ASP.NET hosting, and we only recently started offering MySQL again (we do have an old MySQL server that has a few hundred databases on it, but it's really old, running a version prior to MySQL enabling InnoDB by default). The backup problems started to occur almost immediately after we started selling MySQL 5.x.

That's really backwards. If someone has a pending transaction, the backup could simply not backup the pending transaction. It depends on isolation level, though yes you could see a row/page-level lock here.

Right, but that's what I'm saying - when you are backing up thousands of databases, you can't simply skip any with pending transactions. You wind up with incomplete backups (or no backups ever for a busy database). And if you wait, the backup time extends beyond a certain window and the entire process stops.

But again, these are problems related to large-scale hosting, and the only reason I replied to the thread at all was to point out that hosts usually have a good reason for saying "no."

 

[yavuz]

But again, these are problems related to large-scale hosting, and the only reason I replied to the thread at all was to point out that hosts usually have a good reason for saying "no."

They are exacly doing that actually. I was told that backups in total must not exeed 2 GB in order to benefit from the full backup feature of cPanel. I don't know if it's related to cPanel's functionality but it bothers me. Now I'm on a host that doesn't allow (or limits) me to perform a database repair & analyse altough my database is 400 MB of size.

 

[HannahP]

Is that cPanel with the new R1 backup integration?

We don't use cPanel, but we used to use R1. Or tried to use it. They put all their development time into that cPanel integration and let their core product sit unfixed for over a year so we had to abandon it.

 

[yavuz]

It says cPanel Pro: 1.0 (RC1) if that's what you mean. I'll try out some hosts until the end of January. They also have the strangest problem with user permisions. You can't upgrade or update a wordpress plugin because the user rights needs to be set to "nobody". Anyway, back to the topic. I'll look for a more suitable host.

 

[feldon30]

It's not a stupid limitation.

They don't fully support MySQL. Ditch and make the switch.

Most hosts do not impose limitations for no reason.

Last time I looked, 1&1 has a maximum MySQL database size of 100MB.

 

[HannahP]

Last time I looked, 1&1 has a maximum MySQL database size of 100MB.

1&1? Ha ha. Okay, whatever you say. Way to make a lowest-common-denominator point.

I thought I was addressing an audience that knew the difference between serious web hosting and mass market fast food containers for "click-n-build" web sites like 1&1 or GoDaddy. My mistake.

 

[MGSteve]

1&1? Ha ha. Okay, whatever you say. Way to make a lowest-common-denominator point.

I thought I was addressing an audience that knew the difference between serious web hosting and mass market fast food containers for "click-n-build" web sites like 1&1 or GoDaddy. My mistake.

Yeah, I had to double take when I saw that post. Something happened though before I could post & forgot to!

1&1, I wouldn't use them for a static HTML site. Sadly, I know quite a few design companies that use them to host clients websites. Scarey.

 

[yavuz]

I have a dedicated server for only one host on 1und1. They have though very silly features for webhosting, good for html but not for the big guys.

 

[Floren]

We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.

Each table type has its own benefits. For example, MyISAM is held back by table-level locking on writes whereas InnoDB only has to lock the particular row that is being written. However, if you can avoid locking, MyISAM is hugely faster for data writes than InnoDB. So it's not as simple as saying that one particular table type is better than another.

Horses for courses and all that.

You can easily eliminate table locks by simply pushing all hungry SELECT's to Sphinx, making room for your WRITE's. For example, in vBulletin I push all heavy SELECT's with a dateline older than 10min to Sphinx and use MySQL for the SELECT's with a dateline set between TIMENOW and TIMENOW - 600. That process is enough to completely remove any table locks.

I plan to implement a similar process for Xen, once I have a little time to identify the heavy queries.

 

[Kier]

You can easily eliminate table locks by simply pushing all hungry SELECT's to Sphinx

Is it worth investing the effort in doing that? We've already eliminated virtually all table locks simply by using InnoDB tables.

 

[Floren]

Indeed is not. I was looking at the performance angle of WRITE's, on a MyISAM table... since you remove practically all SELECT's processed on that table, by passing them to Sphinx.

 

[Kevin]

Realized tonight that my XF tables were created all as MyISAM. Reason being was that my /etc/my.cnf had skip-innodb defined in it. Commented that out, restarted MySQL, and now InnoDB is a valid engine selection.

Is there an easier way of getting the XF tables that should be InnoDB switched to InnoDB from MyISAM as opposed to switching the tables manually one-by-one?

 

[ChemicalKicks]

What is the BLOB?

I hope you know why I've just spat my coffee out

 

[Paul B]

The only tables which are MyISAM are:

xf_import_log
xf_search_index
xf_session
xf_session_admin

The rest are InnoDB apart from 3 which are MEMORY:

xf_attachment_view
xf_session_activity
xf_thread_view

So you could just run alter table engine=innodb and then manually change those 7 tables back.

Edit: Apparently it isn't possible to do all tables, they need to be done one at a time.

 

[Kevin]

So you could just run alter table engine=innodb and then manually change those 7 tables back.

Ah, good point... easier to update the exceptions rather than the rule. Will do, thanks.

 

[Marc]

On reading through this and noting Kiers comments, the 'time saved vs is it worth it' is something that a lot of people get wrong no matter what database it is in. I work daily with sql server and see some of the other developers making the same mistakes. Not only do they spend time doing something that will save an unnoticable amount of time, they also neglect the areas where they should be spending the time such as writing efficient queries, correct use of indexing, and indeed the setup of the database engine itself. Things that should be at the forefront of any development. Although I do a fair amount of .NET development nowadays I started with database development and its amazing how many really efficient vb.net/C#/PHP/etc programmers cant get their programs to run well because of a badly thought of database.

 

[simbolo]

Realized tonight that my XF tables were created all as MyISAM. Reason being was that my /etc/my.cnf had skip-innodb defined in it. Commented that out, restarted MySQL, and now InnoDB is a valid engine selection.

Is there an easier way of getting the XF tables that should be InnoDB switched to InnoDB from MyISAM as opposed to switching the tables manually one-by-one?

You can check my post out in another thread.

 

[Adam Howard]

Development on MyISAM was discontinued as of MySQL 5.5.5

Current 5.x versions will support it, but no further development will be produced. The new universal standard is InnoDB.

MySQL 6.0 (Community Server) and MySQL 8.0 (Cluster Server) currently in closed pre-alpha (development builds only) no longer will have MyISAM

 

[ForestForTrees]

Okay, total noob question here... I haven't yet installed XF, so I just don't know. When one installs XenForo, do you have to choose the table types or does the installer handle it for you? If the former, then I'm going to have to start reading this thread very carefully, as we're hoping to start our install this weekend for whatever product we choose, and I tend to be a bit perfectionist.

 

[Ghan_04]

It seems that if MySQL supports InnoDB, that is the engine that the installer will use. Otherwise, MyISAM.