Hi,
To achieve speedup add
1) many threads/connections to each MySQL Server from each client
2) MySQL Servers (to get full use of the data nodes you need quite a few, around 10)
Also don't forget to ensure that the clients doesn't become the bottleneck which is easy
with a powerful cluster. MySQL Servers consume a major portion of processing power
in query execution so don't forget to allocate sufficient resources to those as well.
This should increase your throughput substantially.
It should be possible to get you upto at least tens of
thousand queries like this per second with 8 data nodes.
You don't mention anything about multiple connections or how
many MySQL Servers are involved in the test. If it is only one
client and one connection then you seem to get a roundtrip of
4 milliseconds per query which sounds reasonable.
I've seen some figures from a partner where they got it up to
at least many thousands of queries even with only 2 data nodes.
There is one option parameter that will benefit you greatly for this workload
(it will hurt SELECT COUNT(*) performance however)
is to set
--ndb-use-exact-count to 0
This can improve your performance by as much as 100%.
Rgrds Mikael
Anton Mironenko wrote:
> Hi,
> at the moment we are thinking about buying MySQL
> cluster, so it can be a part of our telekom
> solution.
>
> So we are doing tests.
>
> Right now we have a poor read throughput - 240
> read operations per second.
> Here is a configuration:
> Number of hosts - 4
> hosts' hardware configuration:
> 1 - SunFire Blade B200x, 2 x Intel(R) Xeon(TM) LV
> CPU 2.0GHz HyperThreading, RAM - 4GB, OS - Solaris
> x86 9
> 2 - SunFire Blade B200x, 2 x Intel(R) Xeon(TM) LV
> CPU 2.0GHz HyperThreading, RAM - 4GB, OS - Solaris
> x86 9
> 3 - 2 x Intel(R) Xeon(TM) CPU 2.40GHz
> HyperThreading, RAM - 4GB, OS - Linux RHEL 3
> 4 - 2 x Intel(R) Xeon(TM) CPU 2.40GHz
> HyperThreading, RAM - 4GB, OS - Linux RHEL 3
>
> 2 NDB nodes are installed on each host, so there
> are 8 NDB nodes in the cluster.
>
> Number of replicas = 2.
>
> Hosts are connected to each other with Gigabit
> network.
>
> Read operation query is performed from JDBC client
> and looks like:
>
> SELECT * FROM ACC_USER WHERE ACC_USER_ID = ;
>
> Table structure is the following:
>
> CREATE TABLE `ACC_USER` (
> `ACC_USER_ID` decimal(18,0) NOT NULL default
> '0',
> `PERSON_ID` decimal(18,0) default NULL,
> `ACCOUNT_ID` decimal(18,0) default NULL,
> `CURRENCY_ID` decimal(18,0) default NULL,
> `LANGUAGE_ID` decimal(18,0) default NULL,
> `IS_LOCKED` decimal(1,0) NOT NULL default '0',
> `M_TYPE` decimal(3,0) NOT NULL default '1',
> `ACCOUNT_LEVEL_ID` decimal(18,0) default NULL,
> `COMPANY_ID` decimal(18,0) default NULL,
> `OWNER_ACC_USER_ID` decimal(18,0) default NULL,
> `T_STATE_ID` decimal(18,0) default NULL,
> `T_COS_ID` decimal(18,0) default NULL,
> `T_CHRG_PROFILE_ID` decimal(18,0) default NULL,
> `BEGIN_DATE` timestamp NOT NULL default
> '0000-00-00 00:00:00',
> `END_DATE` timestamp NOT NULL default
> '0000-00-00 00:00:00',
> `M_VPN_EXT` decimal(32,0) default NULL,
> `IS_RING_TIME_INCLUDED` decimal(1,0) default
> NULL,
> `T_LAST_TRANSITION_ID` decimal(18,0) default
> NULL,
> `T_ASP_ZONE_CHP_ID` decimal(18,0) default NULL,
> PRIMARY KEY (`ACC_USER_ID`),
> KEY `ACCOUNT_ID` (`ACCOUNT_ID`),
> KEY `OWNER_ACC_USER_ID` (`OWNER_ACC_USER_ID`)
> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
>
> The number of objects in this table is about
> 500K.
>
>
> The question is: why do we have such a small
> throughput? is it a limit for MySQL cluster with
> the configuration above,
> or we need to dig into our client - it might be
> not optimized?
>
> If 240 read operations per second is not a limit
> for MySQL cluster, what is its limit for the
> configuration above?
>
> Thanks in advance.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com