MySQL Community – what do you want in a load testing framework?

2011/05/09 by admin in MySQL Server, open source, Programming, Python | 2 Comments

So I’ve been doing a fair number of automated load tests these past six months. Primarily with Sysbench, which is a fine, fine tool. First I started using some simple bash based loop controls to automate my overnight testing, but as usually happens with shell scripts they grew unwieldy and I rewrote them in python. Now I have some flexible and easily configurable code for sysbench based MySQL benchmarking to offer the community. I’ve always been a fan of giving back to such a helpful group of people – you’ll never hear me complain about “my time isn’t free”. So, let me know what you want in an ideal testing environment (from a load testing framework automation standpoint) and I’ll integrate it into my existing framework and then release it via the BSD license. The main goal here is to have a standardized modular framework, based on sysbench, that allows anyone to compare their server performance via repeatable tests. It’s fun to see other people’s benchmarks but it’s often difficult to repeat and compare since most tests aren’t fully documented in their blog posts – this could be a solution to that.

Currently I have the harness doing iterations based on:

  • incrementing (choose a global dynamic variable, ie: sync_binlog=0-1000) system values
  • storage engine vs storage engine for the same workload
  • thread quantity increments for read-only or read+write
  • N-nodes in a cluster workloads with WRR traffic distribution (need to code WLC and others)
  • QPS testing for connection pool vs open/close connection
  • multi-table vs single-table workloads

Outputs available: CSV, XML, JSON for easy integration into any number of the various graphing frameworks available. I’ll probably code up a light weight python http server preloaded with Highcharts and Sparklines so you can see your benchmarks easily without having to roll your own graphs.

Quick now, tell me what you’d like me to code for you!

Read More

MySQL Analytics: updated query for table engine data statistics

2011/03/23 by admin in MySQL Server | Leave a comment

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
innodb_perc_index: 22.1226
innodb_perc_data: 77.8774
myisam_perc_index: 7.6898
myisam_perc_data: 92.3102
innodb_perc_total_index: 49.8698
innodb_perc_total_data: 22.5844
myisam_perc_total_index: 50.1302
myisam_perc_total_data: 77.4156

select round(sum(innodb_data_size + innodb_index_size) / (innodb_data_free + sum(innodb_data_size + innodb_index_size))) * 100  as 'innodb_tablespace_utilization_perc'
, (data_size + index_size) / gb as total_size_gb
, index_size / gb as index_size_gb
, data_size / gb as data_size_gb
, sum(innodb_index_size + innodb_data_size) / pow(1024,3) as innodb_total_size_gb
, innodb_data_size / pow(1024,3) as innodb_data_size_gb
, innodb_index_size / pow(1024,3) as innodb_index_size_gb
, sum(myisam_index_size + myisam_data_size) / pow(1024,3) as myisam_total_size_gb
, myisam_data_size / pow(1024,3) as myisam_data_size_gb
, myisam_index_size / pow(1024,3) as myisam_index_size_gb
, index_size / (data_size + index_size) * 100 as perc_index
, data_size / (data_size + index_size) * 100 as perc_data
, innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index
, innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data
, myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index
, myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data
, innodb_index_size / index_size * 100 as innodb_perc_total_index
, innodb_data_size / data_size * 100 as innodb_perc_total_data
, myisam_index_size / index_size * 100 as myisam_perc_total_index
, myisam_data_size / data_size * 100 as myisam_perc_total_data
from ( select sum(data_length) data_size,
	sum(index_length) index_size,
	sum(if(engine = 'innodb', data_length, 0)) as innodb_data_size,
	sum(if(engine = 'innodb', index_length, 0)) as innodb_index_size,
	sum(if(engine = 'myisam', data_length, 0)) as myisam_data_size,
	sum(if(engine = 'myisam', index_length, 0)) as myisam_index_size,
	sum(if(engine = 'innodb', data_free, 0)) as innodb_data_free,
	pow(1024, 3) gb from information_schema.tables )
a\G
Read More

MySQL analytics: information_schema polling for table engine percentages

2010/09/23 by admin in MySQL Server | 4 Comments

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select
(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb,
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb,
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as innodb_perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='myisam') as myisam_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='myisam') as myisam_perc_data,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='innodb') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_innodb,
(select (select sum(DATA_LENGTH) from tables where ENGINE='innodb') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_innodb,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='myisam') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_myisam,
(select (select sum(DATA_LENGTH) from tables where ENGINE='myisam') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_myisam

from tables limit 1\G

This will output something along the lines of: 

          total_size_gb: 2.7505537783727
         total_index_gb: 0.018660545349121
          total_data_gb: 2.7318932330236
             perc_index: 0.6784
              perc_data: 99.3216
      innodb_perc_index: 0.6502
       innodb_perc_data: 99.3498
      myisam_perc_index: 17.5120
       myisam_perc_data: 82.4880
perc_total_index_innodb: 95.6713
 perc_total_data_innodb: 99.8607
perc_total_index_myisam: 4.3287
 perc_total_data_myisam: 0.1393
Read More

Update: OSS MySQL Monitoring Solution

2008/11/21 by admin in Monolith, MySQL Server, Uncategorized | Leave a comment

Progress on the Open Source enterprise grade MySQL monitoring system;  the schema for Monolith version 2 has been designed. Due to the many suggestions for features and the interest it has received I’ve put this on the front burner. That said, here is some more info on the next steps I’ll be taking.

  • Monitored servers will use a command line agent (called remotely) to pull information from both MySQL and the OS.
  • Historical information will include all values from global status and global variables, as well as CPU/Memory/IO/Disk usage.
  • Standard graphing functions – the ones in the list from the previous post – will gather information from various view tables that contain historical data that is collected from the agent script. User defined graphing will allow you to look at historical values over time for any of the various global status or global variable settings (integer based ones anyway).
  • The cnf file for each server will be stored in the monitoring database and available for viewing for historical purposes. 
Read More