MySQL Read Speed and Partitioning on Separate Drives

myisamMySQLmysql-5.6partitioningperformance

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

Best Answer

What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.

What would be preferable is to reverse the order to the primary key columns

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 (`fieldC`,`id`)
 -- 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
);

This may help navigate faster through the partitions. The MySQL Query Optimizer may/may not be smart enough to need the reversal.

If you have other columns in the WHERE clauses such as

  • WHERE fieldC='...' AND fieldA='...'
  • WHERE fieldC='...' AND fieldB='...'

you will need matching indexes so quickly search within one partition. Otherwise, you may find yourself doing a full table scan with the partition.

With that in mind, please create the table 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 (`fieldC`,`id`),
 KEY C_A_Index (`fieldC`,`fieldA`),
 KEY C_B_Index (`fieldC`,`fieldB`)
 -- 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
);

This will then accommodate such WHERE clauses.

Give it a Try !!!

CAVEAT

I noticed your character fields are CHAR and not VARCHAR. That's great.

If any of the character fields are VARCHAR, please read on.

If there are any VARCHAR fields, you need not convert them manually. Simply change the row storage format as follows:

ALTER TABLE mytable ROW_FORMAT=Fixed;

This will make the table's read speed increase 20%-25%. I wrote about this before:

Related Question