InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.
You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).
CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Give it a Try !!!
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:
Best Answer
The simple answer is No.
In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.
There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.
Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.