Performance over Leased Line 10 Mbit
Hi!
a question about running MySQL over a leased line.
Being an admin, I have got into arguing with the SQL-Coders about pros and cons of moving the MySQL server to a hosting center connected by a leased line.
Important to mention I am pro the relocation to hosting center while they are against.
Let me describe the situation:
Headquarters NOW:
- 1 fat MySQL server (16 cores, 24 GB RAM)
- ca. 30-50 MySQL clients connecting over LAN to the MySQL server
Proposed change:
- move the MySQL server to the hosting center
- connected by a leased (dedicated) line: 10 Mbit, measured about 6-9 Mbit, Ping: ca. 15 ms
- MySQL clients stay at headquarters, will connect by the leased line to the MySQL server
The arguments brought against the change are:
- sysbench delivers inside the LAN:
- 1900 transactions/s
- Ping: 1ms
- TX/RX: 100 Mbyte/s (= Gigabit LAN).
On the other hand by using the leased line sysbench delivers:
- 75 transactions/s
- Ping: 15 ms
- TX/RX: 0,8 Mbyte /s (= 10 Mbit leased line)
For exact results see below.
While not being a DB expert I had a few looks at the MySQL server:
- measuring NIC interface throughput in work hours: ca. 5 Gbyte / day
- checking "mysqladmin status":
Uptime: 15736431 Threads: 62 Questions: 287203686 Slow queries: 11517 Opens: 594044 Flush tables: 1 Open tables: 64 Queries per second avg: 18.251
- measuring the CPU Load (Linux) per day, we use Nagios:
- for ca. 8 hours: 1-1.5,
- for ca. 16 hours: mostly idling with a few peaks not reaching 0.25
It seems for me as the MySQL coders ( they have coded the DB applications ) are comparing apples and oranges by bringing the argument of sysbench. My impresion is that the server is an idler with little network traffic.
What do you think about the setup and are the sysbench comparisons meaningful? could it be that sysbench as it was used performs weak over the leased line, but their DB apps would perform with sufficient performance? I think about parallelization, asychronicity etc., sorry I am not a MySQL guru.
Which other paramters I should measure on the existing server to make meaningful statements about whether the leased line of 10 Mbit is a viable solution?
thanks!
Peter
PS:
sysbench commands, that were used:
--
# prepare
sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=[host] --mysql-db=test --mysql-user=[user] --mysql-password=[password] prepare
# run
sysbench --test=oltp --oltp-table-size=1000000 --mysql-host=[host] --mysql-db=test --mysql-user=[user] --mysql-password=[password] --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
# cleanup
sysbench --test=oltp --mysql-host=[host] --mysql-db=test --mysql-user=[user] --mysql-password=[password] cleanup
--
Results of SYSBENCH over 10 Mbit Leased Line:
--
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "LOCK TABLES READ" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.
OLTP test statistics:
queries performed:
read: 63098
write: 0
other: 9014
total: 72112
transactions: 4507 (74.90 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 63098 (1048.64 per sec.)
other operations: 9014 (149.81 per sec.)
Test execution summary:
total time: 60.1711s
total number of events: 4507
total time taken by event execution: 480.4224
per-request statistics:
min: 83.83ms
avg: 106.59ms
max: 747.62ms
approx. 95 percentile: 291.07ms
Threads fairness:
events (avg/stddev): 563.3750/9.14
execution time (avg/stddev): 60.0528/0.05
--
Results of SYSBENCH over Gigabit-LAN Line:
--
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 8
Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.
OLTP test statistics:
queries performed:
read: 1608922
write: 0
other: 229846
total: 1838768
transactions: 114923 (1915.29 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1608922 (26814.08 per sec.)
other operations: 229846 (3830.58 per sec.)
Test execution summary:
total time: 60.0029s
total number of events: 114923
total time taken by event execution: 479.2058
per-request statistics:
min: 2.79ms
avg: 4.17ms
max: 420.09ms
approx. 95 percentile: 5.17ms
Threads fairness:
events (avg/stddev): 14365.3750/42.18
execution time (avg/stddev): 59.9007/0.00
--
Edited 3 time(s). Last edit at 03/27/2013 08:12AM by Peter Konrady.