12.04MySQLperformanceserver
What is the recommended tool(s) for testing the performance of MySQL 5.5 (innodb) under Ubuntu 12.04?
We have purchased a new multi-cpu multi-core server to replace our current MySQL dual-core server. However, the MySQL performance is worse than our current server! We know we need to tweak the innodb parameters but it would be helpful if there is a tool out there that would test and make recommendations.
Oh.., after multiple tries I got it working myself and it has taken upto now. The links are very nice tutorials and I could fix the issues I had this way.
Also I am not understanding whether mysql-server or mysql-client need to be installed along with mysql-cluster else mysql-cluster alone is enough for running mysqld on SQL nodes.
A: I am not sure if mysql-cluster works along mysql-server and mysql-client which are already installed from apt but I am sure that those or any extra package are NOT required for mysql-cluster on Ubuntu as it itself has all the packages. So I can run mysqld from cluster extracted package and nothing required for this to install additionally for SQL nodes.
./bin/mysqld_safe --user=mysql --defaults-file=/etc/my.cnf &
A: No need to run mysqld manually and instead can use /etc/init.d/mysql.server start copying from /usr/local/mysql/support-files/mysql.serverto start mysqld/SQL node.
Starting MySQL * Couldn't find MySQL manager (/usr/bin/mysqlmanager) or server (/usr/bin/mysqld_safe)
A: The above was the error encountered while trying to start mysql.server and that was because I had mysql-server and mysql-client already installed. Those should be completely removed(I'm not sure if can run cluster successfully along with them) and I did. Those were not completely removed(probably I have to use apt --purge remove). Check with dpkg --get-selections | grep mysql, if you find deinstall then those are not completely uninstalled, which should be, else one will have to face above error. Executing below command completely removed them and I could start mysql.server with no errors.
aptitude purge $(dpkg --get-selections | grep deinstall | sed s/deinstall//) #To completely remove all deinstall package
pkill -9 mysqld
/etc/init.d/mysql.server
............ * Manager of pid-file quit without updating file.
A: I faced the above error many times in my testing while trying to start mysql.server. I could avoid this by simply granting permission on data to mysql user like chown -R mysql:mysql /usr/local/mysql/data, this mistake was because I was trying extracting mysql-cluster newly number of times and thus the error as the user mysql could not create pid file under data directory for no permission and error log under /var/lib/mysql-cluster on sql/data node helped me trace this.
Should be very specific in assigning to /usr/local/mysql/data, if you just do chown -R mysql:mysql /usr/local/mysql and keep quiet, it won't work and same error is thrown!
After granting permission , check mysqld is working and running by issuing /usr/local/mysql/bin/mysql -uroot -p. Even after entering password you cannot connect and throws error, then mysql root password is not set or you forgot. Use below command to reset mysql root password, and then try connecting again:
/usr/local/mysql/bin/mysqladmin -uroot password your-new-password-here
Neither I am satisfied with the answer nor I am willing to accept and award myself as I have many more to do in this clustering. I am willing to update my question with more queries and await to award points to other who is going to answer. UPDATED QUESTION!
Thank you!
We thought of using MySQL Cluster for production web servers where apache-tomcat serving some static and jsp pages but because of slow joins, no support for foreign keys and so on... issues which are crucial in our environment and works fine in Innodb engine than NDB, I am ending not using mysql cluster and looking back to replication again though it has sync issues. Thank you!
Well, since nobody wants to answer... :)
Searching Synaptic for "bench" finds a lot of benchmarking suites capable of testing different aspects of a machine. The only one I heard about previously is phoronix-test-suite, which I'm sure is very comprehensive although my short attention span didn't allow me to figure out how to use it.
Then I found UnixBench, which is described as
UnixBench is the original BYTE UNIX benchmark suite, updated and revised by many people over the years.
The purpose of UnixBench is to provide a basic indicator of the performance of a Unix-like system; ... These test results are then compared to the scores from a baseline system to produce an index value, which is generally easier to handle than the raw scores.
Multi-CPU systems are handled. ... The tests compare Unix systems by comparing their results to a set of scores set by running the code on a benchmark system, which is a SPARCstation 20-61 (rated at 10.0).
UnixBench is mentioned by Linode as a tool for VM performance testing in this blog post:
Using identical hardware, KVM Linodes are much faster compared to Xen. For example, in our UnixBench testing a KVM Linode scored 3x better than a Xen Linode.
The test suite is NOT in Ubuntu repositories, but it is trivial to download and compile it:
wget https://github.com/kdlucas/byte-unixbench/archive/master.zip
unzip ./master.zip
cd ./byte-unixbench-master/UnixBench
./Run
The tests take a while to finish. The output looks like
------------------------------------------------------------------------
Benchmark Run: Mon Oct 15 2012 23:55:22 - 00:23:16
4 CPUs in system; running 1 parallel copy of tests
Dhrystone 2 using register variables 12015218.4 lps (10.0 s, 7 samples)
Double-Precision Whetstone 2214.8 MWIPS (10.1 s, 7 samples)
Execl Throughput 896.9 lps (30.0 s, 2 samples)
File Copy 1024 bufsize 2000 maxblocks 58968.3 KBps (30.0 s, 2 samples)
File Copy 256 bufsize 500 maxblocks 14578.6 KBps (30.0 s, 2 samples)
File Copy 4096 bufsize 8000 maxblocks 422068.2 KBps (30.0 s, 2 samples)
Pipe Throughput 70993.3 lps (10.0 s, 7 samples)
Pipe-based Context Switching 16001.5 lps (10.0 s, 7 samples)
Process Creation 1861.8 lps (30.0 s, 2 samples)
Shell Scripts (1 concurrent) 2525.5 lpm (60.0 s, 2 samples)
Shell Scripts (8 concurrent) 737.8 lpm (60.1 s, 2 samples)
System Call Overhead 432496.2 lps (10.0 s, 7 samples)
System Benchmarks Index Values BASELINE RESULT INDEX
Dhrystone 2 using register variables 116700.0 12015218.4 1029.6
Double-Precision Whetstone 55.0 2214.8 402.7
Execl Throughput 43.0 896.9 208.6
File Copy 1024 bufsize 2000 maxblocks 3960.0 58968.3 148.9
File Copy 256 bufsize 500 maxblocks 1655.0 14578.6 88.1
File Copy 4096 bufsize 8000 maxblocks 5800.0 422068.2 727.7
Pipe Throughput 12440.0 70993.3 57.1
Pipe-based Context Switching 4000.0 16001.5 40.0
Process Creation 126.0 1861.8 147.8
Shell Scripts (1 concurrent) 42.4 2525.5 595.6
Shell Scripts (8 concurrent) 6.0 737.8 1229.7
System Call Overhead 15000.0 432496.2 288.3
========
System Benchmarks Index Score 249.7
------------------------------------------------------------------------
Benchmark Run: Tue Oct 16 2012 00:23:16 - 00:51:20
4 CPUs in system; running 4 parallel copies of tests
Dhrystone 2 using register variables 42619039.2 lps (10.0 s, 7 samples)
Double-Precision Whetstone 8274.0 MWIPS (10.4 s, 7 samples)
Execl Throughput 3398.5 lps (30.0 s, 2 samples)
File Copy 1024 bufsize 2000 maxblocks 68332.4 KBps (30.0 s, 2 samples)
File Copy 256 bufsize 500 maxblocks 21462.9 KBps (30.0 s, 2 samples)
File Copy 4096 bufsize 8000 maxblocks 718205.6 KBps (30.0 s, 2 samples)
Pipe Throughput 149713.5 lps (10.0 s, 7 samples)
Pipe-based Context Switching 61968.3 lps (10.0 s, 7 samples)
Process Creation 5321.7 lps (30.0 s, 2 samples)
Shell Scripts (1 concurrent) 5957.1 lpm (60.0 s, 2 samples)
Shell Scripts (8 concurrent) 812.6 lpm (60.1 s, 2 samples)
System Call Overhead 1557391.5 lps (10.0 s, 7 samples)
System Benchmarks Index Values BASELINE RESULT INDEX
Dhrystone 2 using register variables 116700.0 42619039.2 3652.0
Double-Precision Whetstone 55.0 8274.0 1504.4
Execl Throughput 43.0 3398.5 790.4
File Copy 1024 bufsize 2000 maxblocks 3960.0 68332.4 172.6
File Copy 256 bufsize 500 maxblocks 1655.0 21462.9 129.7
File Copy 4096 bufsize 8000 maxblocks 5800.0 718205.6 1238.3
Pipe Throughput 12440.0 149713.5 120.3
Pipe-based Context Switching 4000.0 61968.3 154.9
Process Creation 126.0 5321.7 422.4
Shell Scripts (1 concurrent) 42.4 5957.1 1405.0
Shell Scripts (8 concurrent) 6.0 812.6 1354.3
System Call Overhead 15000.0 1557391.5 1038.3
========
System Benchmarks Index Score 592.5
Which means that the VPS in question has a score of 249.7 for single task and 592.5 for parallel processing.
My desktop machine, while having similar or lower specs to the physical machine my VPS is running on, produced a score of 1409.7 for single task and 5156.3 for parallel processing. Exactly the kind of metric I was looking for.
Another important metric is network speed. I've found a script which downloads test files from different locations and measures download speed. The script can be run with
wget freevps.us/downloads/bench.sh -O - -o /dev/null|bash
(although it probably would be safer to download the script and inspect its contents before running)
To monitor disk I/O latency there is ioping utility which can be installed from Ubuntu repositories:
# ioping . -c 10
4096 bytes from . (ext4 /dev/disk/...): request=1 time=16.4 ms
4096 bytes from . (ext4 /dev/disk/...): request=2 time=16.1 ms
...
Best Answer
Take a look at mysqltuner.pl which can be found here