OSS iPedia

Miracle Linux Corporation, 2006-03-31

1. Preface

1.1. Purpose

The dual-core Intel Xeon and AMD Opteron CPU architectures for MySQL servers had not been tested with the OSS-DB development board WG. Using a machine with these CPU architectures incorporated, we measured the performance of the MySQL database server to analyze and investigate the characteristics of each CPU architecture.

This document treats the measurement results for dual-core Intel Xeon.

1.2. Measurement Environment

1.2.1. System Configuration

To conduct measurement, we operated both the target MySQL server and measurement tool DBT-1 on the same machine.

1.2.2. Hardware

The machine used consisted of the following hardware:

• DELL PowerEdge 1850
  • CPU: Intel Xeon x 1 (2.8GHz, Dual core, HT, EM64T,L2 2MB/core, FSB 800MHz)
  • Memory: 4GB (DDR2/400, ECC)
  • Storage: 146GB (RAID0, 73GB x 2, FC, 10000rpm, 3.5inch)
  • Features:
    • Dual-core CPU
    • Supports EM64T (x86-64).

1.2.3. Software

The OS and MySQL were 64-bit (x86-64). DBT-1 was 32-bit (IA32) because MySQL Connector/ODBC was not 64-bit.

• OS
  • Name: MIRACLE LINUX V4.0 (Asianux 2.0) for x86-64
  • Kernel: Linux 2.6.9-11.25AXsmp
  • Architecture: x86-64 (64bit)
  • File system: ext3
  • Package used: The most recent package available as of February 1, 2006
• DB
  • DB server: MySQL 5.0.18 (64-bit binary)
  • DB driver: MySQL Connector/ODBC 3.51.12 (32-bit binary)
  • Measurement tool: OSDL DBT-1 v2.1 MySQL(ODBC) 1.0 (32-bit binary)

1.2.4. Basic Settings

We built an environment which complied with "OSS performance/Reliability Evaluation/Troubleshooting Tool Development" DB Layer – DBMS Evaluation Using OSDL DBT-1/3 for the First Half of the 2005 Fiscal Year." All the other environments were used as default.

We set the MySQL server (mysqld) parameters as shown below. The default for innodb_thread_concurrency changed from 8 to 20 for MySQL 5.0.8 and later versions released after the first half of 2005. Thus, we added parameters for matching the necessary conditions.

• max_connections=120
• innodb_thread_concurrency=8
• innodb_buffer_pool_size=1024M
• read_buffer_size=1M
• query_cache_size=12M

The following three CPU configurations were used for measurement:

• CPU core 1, HT off
  • Kernel parameter maxcpus = 1 was specified.
• CPU core 2, HT off
  • Kernel parameter maxcpus = 2 was specified.
• CPU core 2, HT on
  • Kernel parameter maxcpus = 4 was specified or unspecified.

2. Measurement Procedure

Refer to "OSS performance/Reliability Evaluation/Troubleshooting Tool Development" DB Layer – DBMS Evaluation Using OSDL DBT-1/3 for the First Half of the 2005 Fiscal Year."

3. Measurement with the Parameters Tuned after the First Half of 2005

3.1. Measurement Method

We set the parameters, turned after the fist half of 2005, in the MySQL server. Then, we measured the performance by changing EU.

3.2. Measurements

3.2.1. BT/s

All these measurement results were similar to those for the SMP machine with the single-core Intel Xeon incorporated.

The peak performance of the 1-core and 2-core CPUs increased about 150%. This increase is smaller than the 168% increase which was achieved by changing the single-core Xeon configuration from UP to SMP in the first half of 2005. But, this increase is similar to the value which was measured in the second half of 2005.

The performance was uninfluenced by whether Hyper-Threading (HT) was on or off. It is similar to the performance which was measured in the first half of 2005. The peak performance measured with HT off was 1% higher than that measured with HT on. However, as EU increased, the difference between the performance values became smaller. This was unexpected because we thought the MySQL server for running multiple threads would be suitable for HT.

3.2.2. CPU Utilization Rates

These measurement results are similar to those for single-core Xeon UP/SMP. As EU increased, the CPU utilization rate for the 1-core CPU moved toward 100%. On the other hand, the maximum CPU utilization rate for the 2-core CPU was about 95%. The maximum value measured with HT on was closer to 95% than that measured with HT off.

The CPU utilization rate measured with HT on was a little smaller than that measured with HT off as long as these rates were below their maximum values.

3.2.3. Average Response Times

When the performance (BT/s) began to decrease for certain EU, the response time measured with HT on was longer than that measured with HT off. The difference in response time became greater with an increase in EU.

3.3. Consideration

3.3.1. Performance Enhancement by Changing from CPU Core 1 to Core 2

There seemed to be a bottleneck specific to dual-core Xeon, not single-core Xeon SMP. Dual-core Xeon differs on whether the cores share the L2 cache or have independent L2 caches, whether cache coherency control is provided by the inside of the CPU or via external buses or chip sets and whether the CPU shares the interface with the front side bus (FSB) or has its own interface. The differences in the peripherals might cause a performance difference between the dual-core CPU and CPU arranged in SMP configuration even though they are identical in core performance. There was a high potential for the CPU configurations to affect the performance because there were not significant differences in CPU utilization rate and I/O load.

The following shows a block diagram of dual-core Xeon 2.8 GHz in DELL PowerEedge 1850 used for our measurement.

I/F: Interface, MCH: Memory Controller Hub, FSB: Front Side Bus

3.3.2. Response Time Delay with HT On for EU before Performance Degradation

The CPU utilization rate was low for the problem EU range. This means that the MySQL server did not leverage the HT characteristics.

3.4. Future Challenges

We could see various characteristics by this measurement. But, the causes of the characteristics are unknown. In the future, we will identify these causes. For further detailed analysis, we will select a dual-core Xeon CPU and Xeon SMP CPU. These CPUs, together with their peripherals, will be as similar to each other as possible. To compare these CPUs, we will use DBT-1 and a tool for further detailed analysis such as OProfile.

4. Influence of MySQL Tuning Parameter Changes on Performance

4.1. Measurement Method

We measured DBT-1 by adding parameters and changing the existing parameters. These parameters are those tuned after the first half of 2005, including the typical tuning parameters as well as parameters which were likely to produce different effects depending on the CPU architecture.

EU used was 190000 for which the BT/s value below the theoretical value was close to the peak performance.

Note that we did not conduct measurement with the 1-core CPU. We measured data when HT was on and off for the 2-core CPU.

4.2. Measurements

Only the parameters that affected the performance are shown above. The parameters which enhanced the performance are highlighted.

The meanings of the parameters are as follows (quotation from the English version of the MySQL 5.0 online manual).

• innodb_concurrency_tickets: The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is allowed to enter InnoDB, it is given a number of "free tickets" equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB.
• innodb_sync_spin_loops: The number of retries to be made to execute a spin lock while a thread is waiting for an InnoDB mutex to be freed before the thread is suspended.
• innodb_thread_concurrency: The maximum number of threads which can enter InnoDB concurrently. For MySQL 5.0.8 and later versions, the default value has changed from 8 to 20, and the minimum infinite value has changed from 500 to 20 (that is, the default is interpreted as infinite).
• innodb_thread_sleep_delay: How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.

If the maximum number of threads for InnoDB is set to infinite (innodb_thread_concurrency = 20), the thread count check and threads do not enter the InnoDB queue. Thus, the innodb_concurrency_tickets and innotdb_thread_sleep_delay parameters should be ineffective. But, their ineffectiveness has not been confirmed.

The parameters below did not influence the measurement results. They are not detailed here. If necessary, refer to the performance data on the individual parameters.

• innodb_additional_mem_pool_size
• innodb_buffer_pool_size
• innodb_checksums
• innodb_commit_concurrency
• innodb_doublewrite
• innodb_file_io_threads
• innodb_flush_method
• innodb_max_dirty_pages_pct
• innodb_support_xa
• join_buffer_size
• key_buffer_size
• query_cache_size
• read_buffer_size
• sort_buffer_size

4.3. Consideration

4.3.1. innodb_concurrency_tickets
innodb_sync_spin_loops
innodb_thread_sleep_delay

We could gain a 5% to 10% increase in performance by tuning the values. However, the CPU utilization rate decreased. Thus, the performance can be further enhanced by adjusting other parameters.

When HT was off, the absolute value and increase rate of performance tended to become higher. This means that the CPUs were not suitable for HT.

4.3.2. innodb_thread_concurrency

When HT was off, we could gain an approximately 10% increase in performance by setting the maximum number of threads to infinite.However, the CPU utilization rate decreased. Thus, the performance can be further enhanced by adjusting other parameters.

When HT was on, the performance significantly degraded. For information about the analysis and consideration when HT was on, refer to the results (FIXME: Link) of measurement and analysis with OProfile.

4.4. Future Challenges

This performance measurement focused on each single parameter. A combination of several parameters is expected to further enhance the performance. When parameters are combined, they might affect each other to increase or decrease the performance. Thus, we should understand the effect of each parameter and then combine the parameters for more sophisticated tuning.

HT was almost ineffective. It might be utterly useless for processing with some parameters. This problem is very serious. Version 8.1 of PostgreSQL is another excellent OSS DB. It offers significantly increased performance and makes HT more effective, thereby destroying the assumption that MySQL performs better than PostgreSQL. We will have to seriously address the problem above and solve it so MySQL and PostgreSQL can evolve while competing with each other.

Related Performance Data

Related Investigation Data