Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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:

share|improve this answer
    
thank you for your response! Changing the order of the primary key appers a good solution. Yes, the table are fully ROW_FORMAT=Fixed. Wath do you think about the raid choice and about "spreading" data on differents drive ? –  Cristian Porta May 2 '13 at 4:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.