Power-Efficiency Study For MySQL Database Server

Introduction

The purpose of this document is to explain the power-efficeincy analysis proess, power-efficiency issues and solutions in MySQL database server as an example to help identify the right tools and procedures that support the development of energy-efficient application.


Analysis Process

  1. Install and start test application(MySQL) on idle system, create workload to simulate the scenario that multiple concurrent clients connecting to the MySQL server without executing any queries.

  2. Run PowerTop on Solaris to see how much time the CPUs are spending running in lower power states, and how much time the CPU are spending running in C0 state(meaning CPU is actually executing instructions.)

  3. Use DTrace to profile application understand the source of the wakeups causing power-consuming activity.

The remainder of the document covers the detail information on each of the above procedures


Test Setup And MySQL Configurations

HW Installation:

Host Server Type

SunFire x4150Server

CPU: 8x2826MHz Intel-Xeon

Memory: 16GB

Client Server Type

SunFire v20z server

CPU: 2x1793MHz AMD

Memory:2G


SW Installation:

Host OS

Solaris snv_96 x86

MySQL Database Server

5.0.51

Test App

Sysbench

commenting query execution:

//rc = mysql_stmt_execute(stmt->ptr);

Sysbench test Installation Steps:

  • shell> cd /sysbench-version

  • shell> ./configure

  • shell> make

  • shell> make install

MySQL Configurations:

a. Standalone MySQL server

default configuration

b. Master-slave MySQL Server

master db server: /etc/my.cnf

[mysqld]

server-id=1

log-bin= /usr/local/mysql/data/mysql-bin.log


expire_logs_days = 10

max_binlog_size = 500M

slave db server: /etc/my.cnf

[mysqld]

server-id=2

master-host = v20z-241-30

master-user = slave_v20z

master-password = passw0rd

master-connect-retry = 60


C. Master-master MySQL Server

master db server: /etc/my.cnf

[mysqld]

replicate-same-server-id = 0

server-id=1

auto-increment-increment = 2

auto-increment-offset = 1


master-host = 10.6.241.106

master-user = slave_x4150

master-password = passw0rd

master-connect-retry = 60


log-bin= /usr/local/mysql/data/mysql-bin.log


expire_logs_days = 10

max_binlog_size = 500M


Command To run 16 concurrent user sysbench oltp test(no queries):

standalone mysql server configuration:

  • shell> sysbench –test=oltp –oltp-table-size=100000 –mysql-db=sbtest –max-requests=0 --oltp-dist-type=special —num-threads=16 run

replication mysql server(M/S, M/M) configuration:

  • shell> sysbench –test=oltp –oltp-table-size=100000 –mysql-db=sbtest –max-requests=0 --oltp-dist-type=special –mysql-host=x4150-241-06 —num-threads=16 run


Test Observations and Results

Before starting MySQL server, the system is idle.

shell>powertop

Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation              

Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (3.7%)          2826 Mhz        100.0%          
C1                      2.5ms   (96.3%)                                                                                                                     Wakeups-from-idle per second: 384.9     interval: 8.0s                          
no ACPI power usage estimate available

Top causes for wakeups:                                                         
26.0% (100.0)               <kernel> :  genunix`clock                           
25.9% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
19.1% ( 73.4)                  sched :  <scheduled timeout expiration>          
 8.7% ( 33.4)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 4.4% ( 16.8)                   java :  <scheduled timeout expiration>          
 1.0% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr              
 1.0% (  4.0)               <kernel> :  genunix`schedpaging

The powertop tool for Solaris x86 reports how much time CPUs spending in lower power states(C1), and how much time the CPUs are runing on C0 state(executing instructions). Even though the system show idle  from processor monitor tools(vmstat, mpstat), the powertop can show that not 100% of its time running at the C1 state, but arround 3.7% time running on C0 state with wakeups  mainly from some kernel activities.

Test 1: Standalone MySQL server Configuration

When running 16-concurrent users  sysbench test, the powertop report the similar data as expected since there is no queries executed and the MySQL server didn't do any work:

Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation

Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (3.9%)          2826 Mhz        100.0%          
C1                      2.5ms   (96.1%)                                         
                                                                             
                                                                                
Wakeups-from-idle per second: 389.3     interval: 8.0s                          
no ACPI power usage estimate available

Top causes for wakeups:                                                         
25.8% (100.2)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
25.7% (100.0)               <kernel> :  genunix`clock                           
18.8% ( 73.1)                  sched :  <scheduled timeout expiration>          
 8.6% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 4.3% ( 16.6)                   java :  <scheduled timeout expiration>          
 1.0% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr              
 1.0% (  4.0)               <kernel> :  genunix`schedpaging

Test 2: Master-slave MySQL server configuration

On slave server, the powertop report :

 Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation              

Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (27.3%)         2826 Mhz        100.0%          
C1                      0.1ms   (72.7%)                                         
                                                                          
Wakeups-from-idle per second: 9846.8    interval: 8.0s                          
no ACPI power usage estimate available

Top causes for wakeups:                                                         
33.5% (3301.1)           <interrupt> :  e1000g#0                                
15.6% (1532.4)           <interrupt> :  aac#0                                   
 1.0% (100.2)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
 1.0% (100.0)               <kernel> :  genunix`clock                           
 0.7% ( 73.2)                  sched :  <scheduled timeout expiration>          
 0.3% ( 33.3)               <kernel> : ehci`ehci_handle_root_hub_status_change 
 0.2% ( 16.7)                   java :  <scheduled timeout expiration>


Use DTrace tracing MySQL's hot function calls to help understand why MySQL slave server is causing CPU wake up from idle state to waste power:



#!/usr/sbin/dtrace -qs


pid$1:::entry

{

self->ts = vtimestamp;

}


pid$1:::return

/self->ts/

{

@a["Count",probefunc] = count();

@b["Time",probefunc] = sum(vtimestamp - self->ts);

self->ts = 0;

}


tick-10sec

{

exit(0);

}


Identified that the cuase of the wakeups was the known MySQL's bug: http://bugs.mysql.com/bug.php?id=33815

Re-configure MySQL's slave server's “server-id = 3” in /etc/my.cnf, start 16 con-current users sysbench test, the powertop report close to 96% low power state(C1) and there is no wakeups from MySQL.


Test 3: Master-master MySQL server configuration

When running 16-concurrent users  sysbench test connecting to the MySQL master server, the powertop report no wakeups from MySQL as expected since there is no queries executed by the MySQL server:

Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation              

Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (4.7%)          2826 Mhz        100.0%          
C1                      1.9ms   (95.3%)                                         
                                                                                
                                                                                
                                                                                
                                                                                
Wakeups-from-idle per second: 496.1     interval: 8.0s                          
no ACPI power usage estimate available

Top causes for wakeups:                                                         
20.2% (100.2)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
20.2% (100.0)               <kernel> :  genunix`clock                           
14.8% ( 73.4)                  sched :  <scheduled timeout expiration>          
 6.7% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 3.4% ( 16.7)                   java :  <scheduled timeout expiration>          
 0.8% (  4.1)            <interrupt> :  e1000g#0                                
 0.8% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr              
 0.8% (  4.0)               <kernel> :  genunix`schedpaging                     
 0.4% (  2.0)               <kernel> :  fcip`fcip_timeout

Power and Performance Measurement

With the different MySQL configuration(single server, replication server) in the tests above, we can see when MySQL's running on idle system, CPU can spend the lowest power states. In order to reduce the energy costs, we can work on improving MySQL applicaiton's performance(response time) to keep system in idle state longer saving the CPU resource.

There are different places that we can make changes to improve performance of a MySQL application: schema optimization and indexing, query performance optimization, tuning database server settings. The more detail and deep information on MySQL performance was covered in the guide book of High Perormance MySQL,2nd edition written by the MySQL performance experts. In this document, I have research test results bellow showing that increasing MySQL performance with these methods can actually reduce CPU utilization to save energy.


Performance Test Tool

  1. mysqlslap: emulate client load for MySQL server. This tool is bundled with MySQL 5.1 server binary on Solaris OS

  2. database schema: world schema(availabe at: http://dev.mysql.com/doc)

  3. Execute custom query file with mysqlslap:

    shell>mysqlslap -uroot -p -q ./myqueries.sql --create-schema=world -i 8

Test 1: Indexing

Create a new table city_huge based on City table in world database

mysql>create table city_huge select \* from City;

mysql>insert into city_huge select \* from City; (4 times)

mysql>alter table city_huge add index name_btree(Name); (create index on Name column)


Execute the query file using the index:

shell>cat scan-index.sql

...

SELECT count(\*) FROM city_huge force index (name_btree) where name = 'Amsterdam'

...


shell>mysqlslap -uroot -p -q ./scan-index.sql --create-schema=world -i 8

Benchmark
        Average number of seconds to run all queries: 0.110 seconds
        Minimum number of seconds to run all queries: 0.109 seconds
        Maximum number of seconds to run all queries: 0.111 seconds
        Number of clients running queries: 1

Average number of queries per client: 1000

shell>powertop
Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation              
Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (8.6%)          2826 Mhz        100.0%          
C1                      0.3ms   (91.4%)                                         
                                                                               
Wakeups-from-idle per second: 2633.3    interval: 8.0s                          
no ACPI power usage estimate available
Top causes for wakeups:                                                         
 3.8% (100.0)               <kernel> :  genunix`clock                           
3.8% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
 2.8% ( 73.3)                  sched :  <scheduled timeout expiration>          
 1.3% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 0.6% ( 16.8)                   java :  <scheduled timeout expiration>          
 0.2% (  6.4)            <interrupt> :  aac#0                                   
 0.2% (  6.4)            <interrupt> :  e1000g#

Execute the query file without using the index:

shell>cat scan-noindex.sql

...

SELECT count(\*) FROM city_huge ignore index (name_btree) where name = 'Amsterdam'

..

# mysqlslap -uroot -p -q ./scan-noindex.sql --create-schema=world -i 8
Benchmark                                                                                            
        Average number of seconds to run all queries: 2.380 seconds     
        Minimum number of seconds to run all queries: 2.374 seconds    
        Maximum number of seconds to run all queries: 2.391 seconds   
        Number of clients running queries: 1                                             
        Average number of queries per client: 1000                                                                         
                                                                                                                                                                                                                                            
shell>powertop                                                                               
 Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation                                    
                                                                                                                        
Cn                      Avg     residency       P-states (frequencies)                             
C0 (cpu running)                (22.5%)         2826 Mhz        100.0%                          
C1                      0.4ms   (77.5%)                                                                           
Wakeups-from-idle per second: 1748.5    interval: 8.0s                                                             
no ACPI power usage estimate available                                                         
                                                                                                                         
Top causes for wakeups:                                                                                  
 5.7% (100.0)               <kernel> :  genunix`clock                                              
 5.7% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
 4.2% ( 73.4)                  sched :  <scheduled timeout expiration>                     
 1.9% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 1.0% ( 16.8)                   java :  <scheduled timeout expiration>                       
 0.2% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr                         
 0.2% (  4.0)               <kernel> :  genunix`schedpaging                                   

For all the database application, adding index is a great method to boost read-access performance. It allows MySQL spend less time find and retrieve the records from indexing instead of scanning the whole table. The time saving of table scan can boost the power usage of the database system. In the above test, we can see that using index can significantly improve the response time of queries and save power by increasing CPUs' spending in lower power states(C1) for arround 12%.

Test 2: Query Performance

Execute query file running join with two tables

#cat join.sql

select Country.Name from Country join city_huge on Country.Code=city_huge.CountryCode where

city_huge.Population> 8000000;

select Country.Name from Country join city_huge on Country.Code=city_huge.CountryCode where

city_huge.Population> 9000000;


#mysqlslap -uroot -p -q ./join.sql --create-schema=world -i 4

Benchmark
        Average number of seconds to run all queries: 0.003 seconds
        Minimum number of seconds to run all queries: 0.003 seconds
        Maximum number of seconds to run all queries: 0.005 seconds
        Number of clients running queries: 1
        Average number of queries per client: 2

shell>powertop
Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (6.3%)          2826 Mhz        100.0%          
C1                      1.3ms   (93.7%)                                         
                                                                                                              
Wakeups-from-idle per second: 726.3     interval: 8.0s                          
no ACPI power usage estimate available
Top causes for wakeups:                                                         
13.8% (100.0)               <kernel> :  genunix`clock                           
13.7% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
10.1% ( 73.4)                  sched :  <scheduled timeout expiration>          
 4.6% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 2.3% ( 16.8)                   java :  <scheduled timeout expiration>          
 0.9% (  6.9)            <interrupt> :  e1000g#0                                
 0.5% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr

Execute query file replacing the join quereis with subqueries

#cat subquery.sql

SELECT distinct Country.name FROM Country WHERE code IN (SELECT CountryCode FROM city_huge WHERE population > 8000000);

SELECT distinct Country.name FROM Country WHERE code IN (SELECT CountryCode FROM city_huge WHERE population > 9000000);


#mysqlslap -uroot -p -q ./subquery.sql --create-schema=world -i 4 
Benchmark
        Average number of seconds to run all queries: 10.568 seconds
        Minimum number of seconds to run all queries: 10.523 seconds
        Maximum number of seconds to run all queries: 10.600 seconds
        Number of clients running queries: 1
        Average number of queries per client: 2

#powertop
Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation        
                                                                                                        
Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (19.0%)         2826 Mhz        100.0%     
C1                      1.1ms   (81.0%)                                         
                                                                                            
Wakeups-from-idle per second: 731.0     interval: 8.0s       
no ACPI power usage estimate available                            
                                                                                                       
Top causes for wakeups:                                                                
13.7% (100.0)               <kernel> :  genunix`clock                           
13.6% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
10.0% ( 73.4)                  sched :  <scheduled timeout expiration>          
 4.6% ( 33.3)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 2.3% ( 16.7)                   java :  <scheduled timeout expiration>          
 0.5% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdlr              
 0.5% (  4.0)               <kernel> :  genunix`schedpaging
The above test shows the MySQL optimizer's specific problem on converting IN subquery from non-correlated subquery to correlated subquery, which searches for totally N \* M rows of the inner and outer queries. By translating the subquery to the Join query, it saves time by retrieving only N+M rows of data in the inner and outer queries from the database. The improved response time of the Join queries save the power of the database system by incresing CPUs' spending in lower power states(C1) for arround 16%.

Test 3: MySQL Server configurations

MySQL's query cache stores the identical SELECT queries issued by client to the database server. By default, MySQL set the query_cache_size as 0.  In this test , configure MySQL server to set query_cache_size as 1M
mysql>set global  query_cache_size=1m

# mysqlslap -uroot -p -q ./subquery.sql --create-schema=world -i 4
Enter password: 
Benchmark
        Average number of seconds to run all queries: 2.630 seconds
        Minimum number of seconds to run all queries: 0.000 seconds
        Maximum number of seconds to run all queries: 10.522 seconds
        Number of clients running queries: 1
        Average number of queries per client: 2

#powertop
Solaris PowerTOP version 1.0       (C) 2007 Intel Corporation              
Cn                      Avg     residency       P-states (frequencies)          
C0 (cpu running)                (10.4%)         2826 Mhz        100.0%          
C1                      1.5ms   (89.6%)                                         
Wakeups-from-idle per second: 585.4     interval: 8.0s                          
no ACPI power usage estimate available
                                                                
Top causes for wakeups:                                                         
17.1% (100.0)               <kernel> :  genunix`clock                           
17.0% ( 99.8)               <kernel> :  uhci`uhci_handle_root_hub_status_change 
12.5% ( 73.3)                  sched :  <scheduled timeout expiration>          
 5.7% ( 33.4)               <kernel> :  ehci`ehci_handle_root_hub_status_change 
 2.9% ( 16.8)                   java :  <scheduled timeout expiration>          
 1.1% (  6.4)            <interrupt> :  e1000g#0                                
 0.7% (  4.0)               <kernel> :  uhci`uhci_cmd_timeout_hdl


After configure MySQL server to enable query cache, MySQL can save time and CPU resource on repetitivly parsing, optimization and execution of the same queries. The test above showed that the queries' response time can be redcued by nearly 4 times by retrieving query result set from the query cache directly and the power usage can be saved by incresing CPUs' spending in lower power states(C1) for arround 11%.


Conclusion

In this document, I use MySQL as an example to show the steps to measure the energy efficiency of the application software. By using the powertop tool on Solaris, we can see whether the system can take advantage of power management features when it is idle; or we can use dtrace tool on Solaris to analyze the source of the power management issues report in the powertop tool, and further improve power efficiency by developing and tuning high performance application.


References

Permanent link to this entry

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

luojiac

Search

Top Tags
Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today