Today we're going to test the insertion performance with MySQL's own benchmark Suite (Benchmark Suite), resulting in an error:
Copy Code code as follows:
# Let's just tell the environment
[ROOT@BEANVM ~]# Cat/etc/redhat-release
Red Hat Enterprise Linux Server Release 6.2 (Santiago)
[ROOT@BEANVM ~]# Uname-a
Linux BEANVM 2.6.32-220.el6.i686 #1 SMP Wed Nov 9 08:02:18 EST i686 i686 i386 gnu/linux
# below for an error
[MYSQL@BEANVM ~]$ cd/usr/local/mysql/sql-bench/
[MYSQL@BEANVM sql-bench]$./test-insert--server=mysql--user=root--password=mysql--log
Can ' t locate dbi.pm in @INC (@INC contains:/usr/local/lib/perl5/usr/local/share/perl5/usr/lib/perl5/vendor_perl/usr/ Share/perl5/vendor_perl/usr/lib/perl5/usr/share/perl5.) At./test-insert Line 33.
BEGIN failed--compilation aborted at./test-insert Line 33.
Obviously there is a missing DBI component, which is an important interface for Perl to connect to the database, first to check if the package exists
Copy Code code as follows:
[MYSQL@BEANVM]$ Rpm-qa |grep perl-db
Can be installed directly with Yum, here I use the form of the installation package, fortunately, these packages in the RHEL6 installation media can be found, as long as the installation of the following two packages can be:
Copy Code code as follows:
[ROOT@BEANVM packages]# RPM-IVH perl-dbi-1.609-4.el6.i686.rpm
Warning:perl-dbi-1.609-4.el6.i686.rpm:header V3 rsa/sha256 Signature, key ID Fd431d51:nokey
Preparing ... ########################################### [100%]
1:PERL-DBI ########################################### [100%]
[ROOT@BEANVM packages]# RPM-IVH perl-dbd-mysql-4.013-3.el6.i686.rpm
Warning:perl-dbd-mysql-4.013-3.el6.i686.rpm:header V3 rsa/sha256 Signature, key ID Fd431d51:nokey
error:failed dependencies:
Libmysqlclient.so.16 is needed by perl-dbd-mysql-4.013-3.el6.i686
libmysqlclient.so.16 (LIBMYSQLCLIENT_16) is needed by perl-dbd-mysql-4.013-3.el6.i686
When the Perl-dbd-mysql package is installed, We found that the hint missing libmysqlclient.so.16 file, if it is often installed MySQL, you should know libmysqlclient.so is a MySQL database must be the necessary library files, will not be linked to it?
Copy Code code as follows:
[ROOT@BEANVM ~]# ls-l/usr/lib/libmysqlclient.so.16*
[ROOT@BEANVM ~]# ls-l/usr/local/mysql/lib/
Total 147260
-rw-r--r--. 1 mysql mysql 13322694 Feb 14:30 libmysqlclient.a
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient_r.a-> libmysqlclient.a
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient_r.so-> libmysqlclient.so
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient_r.so.18-> libmysqlclient.so.18
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient_r.so.18.1.0-> libmysqlclient.so.18.1.0
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient.so-> libmysqlclient.so.18
lrwxrwxrwx. 1 MySQL Feb 14:39 libmysqlclient.so.18-> libmysqlclient.so.18.1.0
-rwxr-xr-x. 1 mysql mysql 7654927 Feb 14:30 libmysqlclient.so.18.1.0
-rw-r--r--. 1 mysql mysql 129797464 Feb 14:38 libmysqld.a
-rw-r--r--. 1 mysql mysql 10166 Feb 14:29 libmysqlservices.a
Drwxr-xr-x. 3 MySQL mysql 4096 Feb 14:39 plugin
From the results above can be learned that there are already libmysqlclient.so.18 files, but the version does not match, so still need the libmysqlclient.so.16 file, then where can be found? The easiest way to do this is to install the Mysql-shared-compat package in the MySQL RPM Installation Kit: (my MySQL is compiled and installed, However, in the same version of the RPM Suite MYSQL-SHARED-COMPAT-5.6.16-1.EL6.I686.RPM package provides the necessary libmysqlclient.so.16 files, in this case, the compilation of deployment or no RPM installed to the overall
Copy Code code as follows:
# my version of MySQL: 5.6.16
[ROOT@BEANVM ~]# RPM-IVH mysql-shared-compat-5.6.16-1.el6.i686.rpm
Preparing ... ########################################### [100%]
1:mysql-shared-compat ########################################### [100%]
You can install perl-dbd-mysql-4.013-3.el6.i686.rpm again.
Copy Code code as follows:
[ROOT@BEANVM packages]# RPM-IVH perl-dbd-mysql-4.013-3.el6.i686.rpm
Warning:perl-dbd-mysql-4.013-3.el6.i686.rpm:header V3 rsa/sha256 Signature, key ID Fd431d51:nokey
Preparing ... ########################################### [100%]
1:perl-dbd-mysql ########################################### [100%]
To sum up, install DBI recommended sequential installation of three packages:
Copy Code code as follows:
# DBI Installation package:
perl-dbi-1.609-4.el6.i686.rpm
# Prerequisite Library Packages, note the version, where the main provision is libmysqlclient.so.16
mysql-shared-compat-5.6.16-1.el6.i686.rpm
# DBD Components
perl-dbd-mysql-4.013-3.el6.i686.rpm
Once successful, you can use benchmark for benchmarking, and here are some of the data for testing insert performance:
Copy Code code as follows:
[MYSQL@BEANVM sql-bench]$./test-insert--server=mysql--user=root--password=mysql--log
Testing server ' MySQL 5.6.16 ' at 2014-02-15 16:36:17
Testing the speed of inserting data into 1 table and does some selects on it.
The tests are do with a table that has 100000 rows.
Generating random keys
Creating tables
Inserting 100000 rows in order
Inserting 100000 rows in reverse order
Inserting 100000 rows in random order
Time for Insert (300000): 879 wallclock secs (3.51 usr 1.21 sys + 0.00 CUSR 0.00 Csys = 4.72 CPU)
Testing insert of Duplicates
Time for Insert_duplicates (100000): wallclock secs 3.81 usr 1.41 sys + 0.00 CUSR 0.00 Csys = 5.22 CPU)
Test of Prepared+execute/once prepared many execute selects
Time for Prepared_select (100000): To wallclock secs (10.87 usr 1.81 sys + 0.00 CUSR 0.00 Csys = 12.68 CPU)
......
The machine is too bad, please disregard the data.
