Innodb_file_per_table on raid 5 server

MySQL & MariaDB Other MySQL® Questions
1

innodb_file_per_table on raid 5 server

2

Hi newtomysql;

RAID5 is not ideal for write-heavy OLTP workloads. Most people tend to use RAID10 for OLTP database systems, which gives you redundancy as well as good write speed at the cost of storage space.

That aside, if your MySQL database is not that busy with writes, you may have no problems with RAID5. So if you have no choice, then all you can do is test it out and then tune what you can control going forward. If you have a choice, then I would avoid RAID5 if possible in exchange for RAID10.

-Scott

3

Hi Scott,
Thank you for your reply. When you say heavy OLTP means how many write in one minute ? The problem now the server comes with 3 hard disk. Is there any better option to do with it RAID 1 ? Will that be ok ? When you tune what you can control going forward what can be tune? Any idea ?

4

Hi Newtomysql;

There is no way to tell you what metrics would work for you for what config; you really just have to test it out yourself. If you only have 3 drives, then your options are limited. RAID1 would buy you nothing over RAID5 really. RAID0 would give you better performance, but if a drive fails then you are in trouble. So if this is a critical application you are setting up, I would seek professional help. If this is a hobby or non-critical application, then you might want to just stick with RAID5 until you know more about what is required and what your work load will look like.

There are tons of performance tuning articles out there, but below is a good place to start:
[url]https://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/[/url]

You could also use the Percona Configuration Wizard to get a base my.cnf to start you out. The tool walks you through some questions to gather information and then gives you a recommend configuration file to start with:
[url]MySQL Tools and Management Software to Perform System Tasks by Percona

-Scott

5

Hi Scott,
Thank you for your links and updates. If I go with RAID 1 with the other other disk mirror with that be good ? Cause I think I overcome the parity overhead in RAID 1. Why you say RAID 1 will do no good compare to RAID5 ?

6

HI newtomysql;

Neither RAID1 nor RAID5 will help you with writes (which is the main benefit to RAID10), so my point was you might as well stick with RAID5 at that point since it should provide you with better read performance than RAID1 at least. But again it goes back to your usage. Even RAID1 may be perfectly fine for your work load, it is just not the ideal setup, but there are always trade-offs of cost and performance.

-Scott