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
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.
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.)
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:
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:
replication
mysql server(M/S, M/M) configuration:
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
mysqlslap:
emulate client load for MySQL server. This tool is bundled with
MySQL 5.1 server binary on Solaris OS
database
schema: world schema(availabe at: http://dev.mysql.com/doc)
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
Posted on: Aug 12, 2008
Posted by: luojiac
Category: Personal
Permanent link to this entry
| Comments [0]
