(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