MariaDB with XtraDB

[sobrenome]

I would like to know if IPS 4 can be run with MariaDB XtraDB (InnoDB drop-in) and if it can result in signifcant better performance, considering that files can be stored in the DB.

I have found some old topics regarding InnoDB with IPS 3.4 and the search limitations. Sphinx was a must.

But I guess that in the current development stage, XtraDB (and InnoDB) offers search capability.

 

[Makoto]

Absolutely. MariaDB is a drop-in replacement for MySQL, and XtraDB is a drop-in replacement for InnoDB. That means virtually any MySQL application that uses the InnoDB table engine can run on a MariaDB / XtraDB configuration, including IP.Board.

I would not move away from Sphinx, Sphinx is still a substantially better option for searching than MySQL, even with full-text search.

You should still see a noticeable performance improvement by moving to an up-to-date MariaDB / XtraDB server configuration. I absolutely did when I migrated my forum installation over, so if you have the means to do so, it's absolutely worth it.

(I'm not 100% sure what you mean by "files can be stored in the DB", are you talking about actual IP.Download files? You should generally never store files in the database if you can avoid it, that's a last resort that's rarely necessary. I may be misunderstanding you though.)

[Skipy7]

@Kirito How did you change your tables to run on XtraDB when running the sql some tables give off an error

ALTER TABLE  `TABLE NAME` ENGINE = INNODB;

 Warning: #1071 Specified key was too long; max key length is 767 bytes

[Makoto]

@Kirito How did you change your tables to run on XtraDB when running the sql some tables give off an error

ALTER TABLE  `TABLE NAME` ENGINE = INNODB;

 Warning: #1071 Specified key was too long; max key length is 767 bytes

​Hmm, I've never actually run into this issue before. Referencing a quick Google, it could be a UTF-8 related issue;
http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

Which table are you getting this warning on? Is this IPB3 or IPB4?

UTF-8 was only recently pushed with IPB4, if there are any indexed varchar columns over 255 characters in length, this is probably the cause of this warning. I'll need to see which table is throwing the error so I can look at the indexes the table uses to confirm. It should be an easy fix either way.

[Skipy7]

This is on 4.0 beta8 On table cms_databases might do it on others but my big list of alter table sql stops at that one and throws that error then stops.

[Makoto]

On table cms_databases might do it on others but my big list of alter table sql stops at that one and throws that error then stops.

​Hmm, looking at this, the index in question is only a varchar(255) index.

MariaDB [ipboard]> SHOW COLUMNS FROM ccs_databases;
+------------------------------+--------------+------+-----+---------+----------------+
| Field                        | Type         | Null | Key | Default | Extra          |
+------------------------------+--------------+------+-----+---------+----------------+
| database_id                  | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| database_name                | varchar(255) | YES  |     | NULL    |                |
| database_key                 | varchar(255) | YES  | UNI | NULL    |                |
| database_database            | varchar(255) | YES  |     | NULL    |                |
| database_description         | text         | YES  |     | NULL    |                |
| database_field_count         | mediumint(9) | NO   |     | 0       |                |
| database_record_count        | mediumint(9) | NO   |     | 0       |                |
| database_template_listing    | mediumint(9) | NO   |     | 0       |                |
| database_template_display    | mediumint(9) | NO   |     | 0       |                |
| database_template_categories | mediumint(9) | NO   |     | 0       |                |
| database_all_editable        | tinyint(1)   | NO   |     | 0       |                |
| database_revisions           | tinyint(1)   | NO   |     | 0       |                |
| database_field_title         | varchar(255) | YES  |     | NULL    |                |
| database_field_sort          | varchar(255) | YES  |     | NULL    |                |
| database_field_direction     | varchar(4)   | YES  |     | NULL    |                |
| database_field_perpage       | smallint(6)  | NO   |     | 25      |                |
| database_comment_approve     | tinyint(1)   | NO   |     | 0       |                |
| database_record_approve      | tinyint(1)   | NO   |     | 0       |                |
| database_rss                 | int(11)      | NO   |     | 0       |                |
| database_rss_cache           | mediumtext   | YES  |     | NULL    |                |
| database_rss_cached          | int(11)      | NO   |     | 0       |                |
| database_field_content       | varchar(255) | YES  |     | NULL    |                |
| database_lang_sl             | varchar(255) | YES  |     | NULL    |                |
| database_lang_pl             | varchar(255) | YES  |     | NULL    |                |
| database_lang_su             | varchar(255) | YES  |     | NULL    |                |
| database_lang_pu             | varchar(255) | YES  |     | NULL    |                |
| database_comment_bump        | tinyint(1)   | NO   |     | 0       |                |
| database_featured_article    | int(11)      | NO   |     | 0       |                |
| database_is_articles         | tinyint(1)   | NO   | MUL | 0       |                |
| database_forum_record        | tinyint(1)   | NO   |     | 0       |                |
| database_forum_comments      | tinyint(1)   | NO   |     | 0       |                |
| database_forum_delete        | tinyint(1)   | NO   |     | 0       |                |
| database_forum_forum         | mediumint(9) | NO   |     | 0       |                |
| database_forum_prefix        | varchar(255) | YES  |     | NULL    |                |
| database_forum_suffix        | varchar(255) | YES  |     | NULL    |                |
| database_search              | tinyint(1)   | NO   |     | 0       |                |
| database_tags_enabled        | tinyint(4)   | NO   |     | 0       |                |
| database_tags_noprefixes     | tinyint(4)   | NO   |     | 0       |                |
| database_tags_predefined     | text         | YES  |     | NULL    |                |
+------------------------------+--------------+------+-----+---------+----------------+
39 rows in set (0.00 sec)

MariaDB [ipboard]> SHOW INDEX FROM ipboard.ccs_databases;
+---------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ccs_databases |          0 | PRIMARY              |            1 | database_id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| ccs_databases |          0 | database_key         |            1 | database_key         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| ccs_databases |          1 | database_is_articles |            1 | database_is_articles | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [ipboard]> 

I think I know what the problem is here though. When you converted your database to utf8, you actually used utf8mb4 didn't you? 4-byte UTF-8 would cause the warning you are seeing here, as with standard 3-byte utf8 on a 255-length varchar column you're staying just under the 767 byte limit, but utf8mb4 would send you over that limit.

[Skipy7]

This is a fresh install so it used what the defaults where for install, That seem to be utf8mb4_unicode_ci

[Makoto]

This is a fresh install so it used what the defaults where for install, That seem to be utf8mb4_unicode_ci

​Really? o.O

A fresh install of IPB3 or IPB4? I don't think IPB3 explicitly defines its collations, so if you're using utf8mb4 on a fresh IPB3 install it's possible that this is simply the default collation configured on your MySQL server, I'm not sure about with IPB4 though.

Well, anyways, you should be able to resolve the issue by running this before converting your tables to InnoDB,

SET @@global.innodb_large_prefix = 1;

Referenced from this answer on SO, it will increase the key limit to 3072 bytes.

[Skipy7]

This is a fresh install of IPS4 Beta 8  

[Makoto]

It's entirely possible IPB simply defaults to that then. I'll look into it to make sure. Definitely worth making sure it's not causing any glitches on fresh InnoDB installations anyways.

Anyhow, try the above and let me know if it works.

[Skipy7]

This is on shared hosting that has changed from MySQL to MariaDB I'll have to ask my hosts to change it.

But the default collation on the server by looking at the variables in PHPMA is latin1_swedish_ci

[Skipy7]

Just had a look at my conf_global.php and there is a setting I might have missed on the install maybe :/

'sql_utf8mb4' => true,

 

[Makoto]

That could be a temporary flag for beta testing, or maybe IPB is intended to keep utf8mb4 as the default. You probably don't need it though, so you should be able to safely set that to false and try the installation again.

[Skipy7]

Just done a fresh reinstall and yes that is an option thats set as default

 

[sobrenome]

 

(I'm not 100% sure what you mean by "files can be stored in the DB", are you talking about actual IP.Download files? You should generally never store files in the database if you can avoid it, that's a last resort that's rarely necessary. I may be misunderstanding you though.)

​I am talking about the new feature of IPS 4 in "data storage".

The data storage method is used by various systems to save arbitrary data. If caching is enabled, using the MySQL Database provides the best performance for most environments, otherwise using the File System is usually best.

 

[sobrenome]

Which IPS 4 tables should be altered to XtraDB/InnoDB for better performance?

[Makoto]

 

​I am talking about the new feature of IPS 4 in "data storage".

​Oh, arbitrary data, not files. I think that's for some form of caching, or something of that nature. I have no idea honestly, I'd have to look at it after I set up another test installation.

Which IPS 4 tables should be altered to XtraDB/InnoDB for better performance?

​I just convert all of my tables over to (Xtra/Inno)DB. Some people opt to only convert their write heavy tables (such as cache tables) over to InnoDB.

I would recommend just converting them all instead of trying to pick and choose individual ones.

[sobrenome]

​I just convert all of my tables over to (Xtra/Inno)DB. Some people opt to only convert their write heavy tables (such as cache tables) over to InnoDB.

I would recommend just converting them all instead of trying to pick and chose individual ones.

​And there is any setting on IPS software to change or it will run normally after the conversion? 

[Makoto]

If conf_global.php still has a mysql_tbl_type entry in it, just change that to InnoDB. Though in IPB3 I don't think that really did anything terribly important other than prevent Full-Text Search from being enabled.

Otherwise I don't believe there should be anything you need to change.

[Skipy7]

There is no way to change the SQL storage engine through the IPS Software that I have found, you need to manually change it for all tables through PHPMyAdmin or the command line.

If the table collations are utf8mb4_unicode_ci making the change to (Xtra/Inno)DB is a little more complicated from what I found couple hours ago.

[RevengeFNF]

​I am talking about the new feature of IPS 4 in "data storage".

The data storage method is used by various systems to save arbitrary data. If caching is enabled, using the MySQL Database provides the best performance for most environments, otherwise using the File System is usually best.

 

​They are talking about cache. Even on 3.4.7 it is saved in the Database.

In IPS4 you can save it on the file System, Database or using a solution like Memcache, xcache etc etc

[sudo]

What is innodb_file_format set too?

[sobrenome]

I have been reading lots of articles over the internet about MyISAM and InnoDB and they are not conclusive about which one is faster.

it depends on many factors. The main commom sense is that for reading data MyISAM is faster. For writing and updating InnoDB is faster.

Are you really sure that for the IPS 4 architeture InnoDB is the better performance engine?

[RevengeFNF]

I have been reading lots of articles over the internet about MyISAM and InnoDB and they are not conclusive about which one is faster.

it depends on many factors. The main commom sense is that for reading data MyISAM is faster. For writing and updating InnoDB is faster.

Are you really sure that for the IPS 4 architeture InnoDB is the better performance engine?

​I can tell for my experience because i was using Mysql 5.5 with Myisam and switched to Innodb.

My board got a lot faster with Innodb. I was having problems with the View New Content being slow, i got ride of it. One of the advantages of Innodb, it uses row locks instead of table locks. So if you have a busy board, you can i have a lot of table locks with myisam, that will make reads slower.

Another thing is that buffer pool of innodb caches data and indexes, while the key buffer of myisam only caches indexes.

Later i switched from Mysql 5.5 to MariaDB 10.0 with xtradb and got even more performance improvements

Now if you switch to innodb and do not tune your my.cnf, you will get poor performance. The same occurs with myisam.

[Makoto]

I have been reading lots of articles over the internet about MyISAM and InnoDB and they are not conclusive about which one is faster.

it depends on many factors. The main commom sense is that for reading data MyISAM is faster. For writing and updating InnoDB is faster.

Are you really sure that for the IPS 4 architeture InnoDB is the better performance engine?

​A lot of these articles are probably old. InnoDB has made leaps and bounds in regards to performance, even more so for XtraDB. Performance isn't the only thing that matters either. InnoDB is ACID compliant, MyISAM is not. MyISAM is generally fast at reading because of its simplicity, but the simplicity does come at a cost, you can read a bit about the differences here,
http://en.wikipedia.org/wiki/MyISAM#Features

As far as I know, MyISAM development stagnated a long time ago, and all future development efforts are being put towards InnoDB now. These are the reasons why I recommend using InnoDB as your default table engine, and not just for write heavy tables. Even if MyISAM did offer you slightly better read performance on some tables, it's probably negligible enough with the latest version of MariaDB/XtraDB that it's not worth all that you're losing by not using (Inno/Xtra)DB.

(Another common reason for people using MyISAM over InnoDB is full-text support, which you don't need with Sphinx and as you already know is now available in the latest versions of InnoDB anyways).