Jump to content

MariaDB with XtraDB


sobrenome

Recommended Posts

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.

 

Link to comment
Share on other sites

  • Replies 132
  • Created
  • Last Reply

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.)

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

(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.

 

Link to comment
Share on other sites

 

​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.

Link to comment
Share on other sites

​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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

​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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.

×
×
  • Create New...