Thursday Oct 30, 2008

Scale-out MySQL Study on Solaris Nevada

HW configuration:

Server A: v20z Server(CPU: 2x2193 Mhz, Memory: 4032 MB)

Server B: x4150 Server(CPU: 8x2826 Mhz, Memory: 16384 MB)

SW configuration:

MySQL5.1.24-rc, MySQL proxy 0.61, Amoeba for MySQL 0.31, DBT2 for MySQL

OS versions: Solaris Nevada snv_91

Test: MySQL DBT2 Test using Amoeba For MySQL

amoeba configuration and query routing rule:

#cat amoeba.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">

<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<server>

<property name="port">2066</property>

<property name="ipAddress">HostA</property>

<property name="readThreadPoolSize">600</property>

<property name="clientSideThreadPoolSize">600</property>

<property name="serverSideThreadPoolSize">600</property>

<property name="netBufferSize">300</property>

<property name="tcpNoDelay">no</property>

<property name="user">root</property>

</server>

<connectionManagerList>

<connectionManager name="defaultManager">

<className>com.meidusa.amoeba.net.AuthingableConnectionManager</className>

</connectionManager>

</connectionManagerList>

<dbServerList>

<dbServer name="HostB">

<factoryConfig>

<className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className>

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">HostB</property>

<property name="schema">dbt2</property>

<property name="user">root</property>

</factoryConfig>

<poolConfig>

<className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>

<dbServer name="HostA">

<factoryConfig>

<className>com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory</className>

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">HostA</property>

<property name="schema">dbt2</property>

<property name="user">root</property>

</factoryConfig>

<poolConfig>

<className>com.meidusa.amoeba.net.poolable.PoolableObjectPool</className>

<property name="maxActive">200</property>

<property name="maxIdle">200</property>

<property name="minIdle">10</property>

<property name="minEvictableIdleTimeMillis">600000</property>

<property name="timeBetweenEvictionRunsMillis">600000</property>

<property name="testOnBorrow">true</property>

<property name="testWhileIdle">true</property>

</poolConfig>

</dbServer>

<dbServer name="multiPool" virtual="true">

<poolConfig>

<className>com.meidusa.amoeba.server.MultipleServerPool</className>

<property name="loadbalance">1</property>

<property name="poolNames">HostB,HostA</property>

</poolConfig>

</dbServer>

</dbServerList>

<queryRouter>

<className>com.meidusa.amoeba.mysql.parser.MysqlQueryRouter</cla

<property name="needParse">true</property>

</queryRouter>

</amoeba:configuration>


#cat rule.xml

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">

<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">

<tableRule name="warehouse" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="item" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="customer" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="district" schema="dbt2" defaultPools="HostB">

</tableRule>

<tableRule name="history" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="new_order" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="orders" schema="dbt2" defaultPools="HostA">

</tableRule>

<tableRule name="order_line" schema="dbt2" defaultPools="HostB">

</tableRule>

<tableRule name="stock" schema="dbt2" defaultPools="HostB">

</tableRule>

</amoeba:rule>


Test 1. number of warehouse: 3

DBT2 Test results using single MySQL vs. two MySQLs using Amoeba:

Threads#

1 mysqld(Server A)

CPU(usr/sys/idle)

Threads#

2 mysqld(Server A,B)

16

9576 TPM

80/19/1

16

6928TPM

32

9628 TPM

79/21/0

32

9310TPM

64

9574 TPM

78/20/2

64

9357TPM

128

8918 TPM

80/19/1

128

9629 TPM

256

8425 TPM

81/19/0

256

9792 TPM

512

8012 TPM

82/18/0

512

9564 TPM







CPU statistics on Server A(DBT2 app, MySQL server)

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl

0 64 0 5 2392 2099 9095 149 1124 130 0 42926 45 30 0 25

1 60 0 1 1059 1 10658 329 1125 124 0 48039 50 23 0 26

CPU statistics on Server B(Amoeba for MySQL: bind with CPU 0-5, MySQL Server:Use CPU6,7)

CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl

0 3 0 1 10916 10413 13315 375 4122 1368 0 30541 35 38 0 28

1 4 0 3 501 0 18041 588 4673 891 0 42709 41 28 0 31

2 2 0 0 435 46 14415 380 3665 1022 0 26819 36 17 0 46

3 3 0 0 365 0 14087 362 3689 739 0 29129 39 20 0 41

4 2 0 1 319 2 11197 299 2582 793 0 19806 34 14 0 52

5 4 0 1 434 3 15149 457 3935 750 0 33424 39 22 0 39

6 3 0 0 33 0 4206 32 446 134 0 20105 22 7 0 71

7 1 0 0 37 0 4202 34 448 143 0 20119 21 7 0 71


Test 2. number of warehouse: 100

DBT2 Test results using single MySQL vs. two MySQLs using Amoeba:

 

 

 

 

 

 

 

 

 

 

 

 

Disk statistics on Server A(Single MySQL server Test)

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

95.8 3.8 28064.7 22.1 0.0 28.0 0.0 281.6 0 100 c1t1d0

extended device statistics

Disk statistics on Server A(Two MySQL servers Test)

extended device statistics

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

62.6 3.2 32931.2 227.2 0.0 7.6 0.0 115.5 0 100 c1t1d0

Conclusion:

  1. Use two mysql servers with amoeba proxy to divide duties can significantly improve MySQL performance when the single MySQL server encountered disk I/O bottleneck

  2. The limitation of Amoeba proxy for MySQL is that it consumed many CPU resources and had overhead on extra network I/Os. In DBT2 benchmark test, using two MySQL servers can't achieve more performance gain when the single MySQL server encountered CPU bottleneck, however, it scale better with number of concurrent users than the single MySQL server.

Tuesday Aug 12, 2008

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 [0]

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