(mysql.info.gz) Performance figures
Info Catalog
(mysql.info.gz) SCI Sockets
(mysql.info.gz) MySQL Cluster Interconnects
16.6.2 Low-level benchmarks to understand impact of cluster interconnects
-------------------------------------------------------------------------
The ndbd process has a number of simple constructs which are used to
access the data in MySQL Cluster. We made a very simple benchmark to
check the performance of each such statement and the effect various
interconnects have on their performance.
There are four access methods:
`Primary key access'
This is a simple access of one record through its primary key. In
the simplest case only one record is accessed at a time. This
means that the full cost of setting up a number of TCP/IP message
and a number of costs for context switching is taken by this
single request. In a batched case where e.g. 32 primary key
accesses are sent in one batch then those 32 accesses will share
the set-up cost of TCP/IP messages and context switches (if the
TCP/IP are for different destinations then naturally a number of
TCP/IP messages needs to be set up.
`Unique key access'
Unique key accesses are very similar to primary key accesses
except that they are executed as a read of an index table followed
by a primary key access on the table. However only one request is
sent from the MySQL Server, the read of the index table is handled
by the ndbd process. Thus again these requests benefit from being
accessed in batches.
`Full table scan'
When no indexes exist for the lookup on a table, then a full scan
of a table is performed. This is one request to the ndbd process
which divides the table scan into a set of parallel scans on all
ndbd processes in the cluster. In future versions the MySQL
server will be able to push down some filtering in those scans.
When no indexes exist for the lookup on a table, then a full scan
of a table is performed. This is one request to the ndbd process
which divides the table scan into a set of parallel scans on all
ndbd processes in the cluster. In future versions the MySQL
server will be able to push down some filtering in those scans.
`Range scan using ordered index'
When an ordered index is used it will perform a scan in the same
manner as the full table scan except that it will only scan those
records which are in the range used by the query set-up by the
MySQL server. In future versions a special optimization will
ensure that when all index attributes that are bound includes all
attributes in the partitioning key then only one partition will be
scanned instead of all in parallel.
To check the base performance of these access methods we developed a
set of benchmarks. One such benchmark, testReadPerf issues, simple
primary and unique key access, batched primary and unique key accesses.
The benchmark also measures the set-up cost of range scans by issuing
scans returning a single record and finally there is a variant which
uses a range scan to fetch a batch of records.
In this manner we can test the cost of issuing single key access and
single record scan accesses and measure the impact of the communication
media implementation of these base access methods.
We executed those base benchmark both using a normal transporter using
TCP/IP sockets and a similar set-up using SCI sockets. The figures
reported below is for small accesses of 20 records per of data per
access. The difference between serial and batched goes down by a factor
of 3-4 when using 2 kB records instead. SCI Sockets were not tested
with 2 kB record2 kB records. Tests were performed on a 2-node cluster
with 2 dual CPU machines equipped with AMD MP1900+ processors.
Access type: TCP/IP sockets SCI Socket
Serial pk access: 400 microseconds 160 microseconds
Batched pk access: 28 microseconds 22 microseconds
Serial uk access: 500 microseconds 250 microseconds
Batched uk access: 70 microseconds 36 microseconds
Indexed eq-bound: 1250 microseconds 750 microseconds
Index range: 24 microseconds 12 microseconds
We did also another set of tests to check the performance of SCI Sockets
compared to using the SCI transporter and all compared to the TCP/IP
transporter. All these tests used primary key accesses either serially,
multi-threaded and multi-threaded and batched simultaneously.
More or less all of these tests showed that SCI sockets were about 100%
faster compared to TCP/IP. The SCI transporter was faster in most cases
compared to SCI sockets. One notable case however with many threads in
the test program showed that the SCI transporter behaved really bad if
used in the mysqld process.
Thus our conclusion overall is that for most benchmarks SCI sockets
improves performance with around 100% compared to TCP/IP except in rare
cases when communication performance is not an issue such as when scan
filters make up most of processing time or when very large batches of
primary key accesses are achieved. In that case the CPU processing in
the ndbd processes becomes a fairly large part of the cost.
Using the SCI transporter instead of SCI Sockets is only of interest in
communicating between ndbd processes. Using the SCI transporter is also
only of interest if a CPU can be dedicated for the ndbd process since
the SCI transporter ensures that the ndbd will never go to sleep. It is
also important to ensure that the ndbd process priority is set in such
a way that the process doesn't lose in priority due to running for a
long time (as can be done by locking processes to CPU's in Linux 2.6).
If this is a possible configuration then ndbd process will benefit by
10-70% compared to using SCI sockets (the larger figures when
performing updates and probably also on parallel scan activities).
There are several other implementations of optimized socket variants for
clusters reported in various papers. These include optimized socket
variants for Myrinet, Gigabit Ethernet, Infiniband and the VIA
interface. We have only tested MySQL Cluster so far with SCI sockets
and we also include documentation above on how to set-up SCI sockets
using ordinary TCP/IP configuration for MySQL Cluster.
Info Catalog
(mysql.info.gz) SCI Sockets
(mysql.info.gz) MySQL Cluster Interconnects
automatically generated byinfo2html