Looking at running queries SHOW FULL PROCESSLIST

One of the most common commands that a MySQL DBA performs is the SHOW FULL PROCESSLIST command. I can't tell you how many thousands of times I've run this. The problem is that there is too much information that I need to filter out before I can focus on the queries that matter. Fore xample, I don't want to see any idle connections nor do I want to see my own connection. The other problem is that if most queries run in under a second, I miss a lot of queries.

Problem solved!

I have posted a very handy script named proclist.pl to MySQL Forge.This script can be used to periodically poll the server for queries, with millisecond precision. Moreover, the output can be imported into a MySQL database for further analysis.

More info on iSCSI benchmark

Looks like a lot of interest was generated from the results of RightMedia's iSCSI benchmarking. Peter Zaitsev is curious about the environment of the benchmark. So here's all the gory details
MySQL version : 5.0.21
System : GenToo Linux, kernel version 2.6.16-gentoo-r2-1
Hardware : 4GB memory, 2 Dual Core AMD Opteron 275 chips
MySQL configuration file (abridged version):

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
basedir = /usr
datadir = /var/lib/mysql/data
port = 3306
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
socket = /var/run/mysqld/mysqld.sock
user = mysql
max_connections = 1024
table_cache = 128
thread_case_size = 32
sort_buffer_size = 64M
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 3000M
innodb_data_home_dir = /var/lib/mysql/inno
innodb_log_group_home_dir = /var/lib/mysql/log/inno
innodb_log_file_size = 2000M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_flush_trx_at_commit = 2
innodb_autoincrement_size = 32M
innodb_status_file
innodb_thread_concurrency = 4
innodb_flush_method = O_DIRECT
innodb_support_xa = 0

sysbench v0.4.3
Command used to prepare 100,000,000 record sbtest database:
sysbench --num-threads=1 --test=oltp --mysql-user=root --oltp-table-size=100000000 --mysql-table-type=innodb prepare

For each SysBench run:
sysbench --num-threads=# --test=oltp --mysql-user=root --oltp-table-size=100000000 --mysql-table-type=innodb --max-requests=10000 --oltp-test-mode=complex run

Before each SysBench run, the kernel pagecache was also cleaned out:
sync ; echo 1 > /proc/sys/vm/drop_caches

This was done to provide a fair & clean test. For each batch of runs by thread, 4 runs were made,with the best TPS used as the final number for the batch.

I am pretty happy with the results. I plan on putting for iSCSI benchmark results soon, as I think a lot of folks would be interested in the results, especially if the iSCSI device scales well.

MySQL sysbench benchmarking - iSCSI

Our company, RightMedia is in the process of evaluating a storage subsystem for our ever growing MySQL databases. It seems that our data is growing at an alarming rate (this is a good thing) and our internal SCSI disks aren't handling the load of massive inserts, plus large queries. So the time is now to evaluate a storage subsystem that will grow with us and scale for our performance needs.

I was really reluctant when I first heard iSCSI proposed as a potential solution. I am familiar with large storage subsystems all hooked in through fibre channel. It's an expensive solution, but performance is great. There are a few iSCSI vendors, and we had lots of problems with kernel version & driver version. But, we perservered, and have systems where we can benchmark the performance of iSCSI.

Now before I get to the actual numbers, let me give you the environment for the testing. I have tried to use as much identical hardware as possible for testing. All of the tests were done with Gentoo Linux, kernel version 2.6.16. The machines have at least 8GB memory (one has 16GB of memory), and all have 2 dual core Opteron CPUS. MySQL version 5.0.21 was used for testing, and InnoDB used as the storage engine. The following types of drives were used for benchmarking: 1) Single 7200 RPM 200GB SATA drive 2) 3 15,000 RPM 73GB SCSI drives in a RAID-0 configuration (128k stripe size) 3) 4 disk RAID-10 configuration on an iSCSI volume (actually using 8 disks) 4) 8 disk RAID-10 configuration on an iSCSI volume (16 disks,, 1M stripe size)

Without further ado, here is a chart of the results:



Shocking isn't it? The iSCSI disk outperforms even a local SCSI array? I found this hard to believe myself, but it gives me more hope that this might be a viable solution. I am still conducting lots of tests on teh device to make sure that the machine scales, and I'll be posting results soon.