Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was partitioned for a fixed field -21 partitions, avg 1Mln Row/Partition- like this:
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`id`,`fieldC`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,''),
partition pValueA VALUES IN('valueA'),
partition pValueB VALUES IN('valueB'),
partition pValueC VALUES IN('valueC')
-- other partitions
);
The root of the queries are always "WHERE fieldC = ? ... AND ...
" so I always guaranteed access to a partition.
This table can be only MyISAM so I have installed the MySQL server 5.6.11
Prior to MySQL 5.6.6, a SELECT from a partitioned MyISAM table caused a lock on the entire table; in MySQL 5.6.6 and later, only those partitions actually containing rows that satisfy the SELECT statement's WHERE condition are locked. This has the effect of increasing the speed and efficiency of concurrent operations on partitioned MyISAM tables.
The setup consist on a Master/Slave topology with same HW (I can distribuite reads across Master and Slave)
Possible solution
I have tree choice for the hardware architecture(6 slot disk 3.5''):
Soloution #1
1xraid1 for OS, /tmp and binlog
1xraid1 partition for mysql data (ex: /diskA)
1xraid1 additional partition for mysql data (ex: /diskB)
Soloution #2
1xraid1 OS, /tmp and binlog
1xraid10 mysql data
Soloution #3 (2 disk waste for tmp!)
1xraid1 OS and binlog
1xraid1 partition for mysql data
1xraid1 /tmp
The first choice can help me to spread the I/O over two disk using the:
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`id`,`fieldC`)
-- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
partition pNULLorEMPTY VALUES IN(NULL,'')
DATA DIRECTORY = '/diskA/customer'
INDEX DIRECTORY = '/diskA/customer',
partition pValueA VALUES IN('valueA')
DATA DIRECTORY = '/diskB/customer'
INDEX DIRECTORY = '/diskB/customer',
partition pValueB VALUES IN('valueB')
DATA DIRECTORY = '/diskA/customer'
INDEX DIRECTORY = '/diskA/customer',
partition pValueC VALUES IN('valueC')
DATA DIRECTORY = '/diskB/customer'
INDEX DIRECTORY = '/diskB/customer'
-- other partitions
);
The second choice improve the fault tolerance level, but consider that I have a Master/Slave configuration so, in every moment, I can promote the Slave as a Master.
What kind of architecture you advice?
There are problems that at the moment I do not see?
Any other kind of suggestions would be greatly appreciated. :)
Thanks in advance
crx