MySQL5.1MyISAM and InnoDB Engine read/write Performance Comparison

I. Preface 2. Summary 3. 1 million data Performance Test 4. 2 million data Performance Test 5. 5 million data Performance Test 6. 10 million data Performance Test 7. Summary Note, test environment: CentOS6.4x86 _ 64, software version: MySQ

I. Preface 2. Summary 3. 1 million data Performance Test 4. 2 million data Performance Test 5. 5 million data Performance Test 6. 10 million data Performance Test 7. Summary Note, test environment: CentOS6.4x86 _ 64, software version: MySQ

Ii. Overview

1. Prepare the environment

(1) install the yum Source

[Root @ node6 src] # wget [root @ node6 src] # rpm-ivh epel-release-6-8.noarch.rpm

(2). Synchronization time (system and hardware)

[Root @ node6 src] # yum install-y ntp [root @ node6 src] # ntpdate 202.120.2.101 [root @ node6 src] # hwclock-w

2. Install mysql 5.1

[Root @ node6 mysql-5.1.73] # tar xf mysql-5.1.73.tar.gz [root @ node6 mysql-5.1.73] # cd mysql-5.1.73 [root @ node6 mysql-5.1.73] #. /configure -- prefix =/usr/local/mysql -- localstatedir =/data/mysql -- enable-consumer er -- with-client-ldflags =-all-static -- with-mysqld-ldflags = -all-static -- with-pthread -- enable-static -- with-big-tables -- without-ndb-debug -- with-charset = utf8 -- with-extra-charsets = all -- -debug -- enable-thread-safe-client -- enable-local-infile -- with-plugins = max

Error 1: checking for termcap functions library... configure: error: No curses/termcap library found.

Cause: the ncurses installation package is missing.

Solution,

[Root @ node6 mysql-5.1.73] # yum-y install ncurses-devel

Continue,

[Root @ node6 mysql-5.1.73] #. /configure -- prefix =/usr/local/mysql -- localstatedir =/data/mysql -- enable-consumer er -- with-client-ldflags =-all-static -- with-mysqld-ldflags = -all-static -- with-pthread -- enable-static -- with-big-tables -- without-ndb-debug -- with-charset = utf8 -- with-extra-charsets = all -- -debug -- enable-thread-safe-client -- enable-local-infile -- with-plugins = max

The above configuration content is omitted ......

This version of MySQL Cluster is no longer maintained. Please use the separate sources provided for MySQL Cluster instead. See for more details. Thank you for choosing MySQL! Remember to check the platform specific part of the reference manual for hints about installing MySQL on your platform. Also have a look at the files in the Docs directory.

At this point, the compilation configuration is complete. Next we compile and install.

[Root @ node6 mysql-5.1.73] # make & make install

Note: Compilation and installation take a long time. Please wait patiently. Of course, you will see the configurations of your machine. The better the configuration, the less time it takes to compile and install.

3. Create a data directory and authorize

[Root @ node6 mysql-5.1.73] # mkdir-pv/data/mysql mkdir: the directory "/data/mysql" [root @ node6 mysql-5.1.73] # useradd mysql [root @ node6 mysql-5.1.73] # chown mysql. mysql/data/mysql/[root @ node6 mysql-5.1.73] # ll/data/total usage 20 drwx ------. 2 root 16384 18:42 lost + found drwxr-xr-x. 2 mysql 4096 January 4 16:10 mysql

4. Provide a configuration file for mysql

[Root @ node6 mysql-5.1.73] # cp support-files/my-huge.cnf/etc/my. cnfcp: overwrite "/etc/my. cnf "? Y

5. modify the configuration file.

[Root @ node6 mysql-5.1.73] # vim/etc/my. cnf [client] default-character-set = utf8 [mysqld] default-character-set = utf8 datadir =/data/mysql

6. Provide the Startup Script

[Root @ node6 mysql-5.1.73] # cp support-files/mysql. server/etc/init. d/mysqld [root @ node6 mysql-5.1.73] # chmod + x/etc/init. d/mysqld [root @ node6 ~] # Chkconfig mysqld -- add [root @ node6 ~] # Chkconfig mysqld on

7. initialize mysql

[Root @ node6 mysql-5.1.73] #/usr/local/mysql/bin/mysql_install_db -- basedir =/usr/local/mysql/-- datadir =/data/mysql/-- user = mysql Installing mySQL system tables... 140104 16:18:43 [Warning] '-- default-character-set' is deprecated and will be removed in a future release. please use '-- character-set-server' instead. 140104 16:18:43 [Warning] '-- skip-locking' is deprecated and will be removed in a futu Re release. please use '-- skip-external-locking' instead. OK Filling help tables... 140104 16:18:43 [Warning] '-- default-character-set' is deprecated and will be removed in a future release. please use '-- character-set-server' instead. 140104 16:18:43 [Warning] '-- skip-locking' is deprecated and will be removed in a future release. please use '-- skip-external-locking' instead. OKTo start mysqld Boot time you have to copy support-files/mysql. server to the right place for your systemPLEASE remember to set a password for the MySQL root USER! To do so, start the server, then issue the following commands: /usr/local/mysql // bin/mysqladmin-u root password 'new-password'/usr/local/mysql // bin/mysqladmin-u root-h node6.test.com password' new -password 'Alternatively you can run: /usr/local/mysql // bin/mysql_secure_installationwhich will also give you the option of removing the test databases and anonymous user created by default. this is str Onugly recommended for production servers. see the manual for more instructions. you can start the MySQL daemon with: cd/usr/local/mysql /; /usr/local/mysql // bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd/usr/local/mysql // mysql-test; perl mysql-test-run.plPlease report any problems with the/usr/local/mysql // scripts/mysqlbug script!

Note: we have seen several warnings from the above content. Let's take a look.

140104 16:18:43 [Warning] '-- default-character-set' is deprecated and will be removed in a future release. please use '-- character-set-server' instead. 140104 16:18:43 [Warning] '-- skip-locking' is deprecated and will be removed in a future release. please use '-- skip-external-locking' instead. OK Filling help tables... 140104 16:18:43 [Warning] '-- default-character-set' is deprecated and will be removed in a future release. please use '-- character-set-server' instead. 140104 16:18:43 [Warning] '-- skip-locking' is deprecated and will be removed in a future release. please use '-- skip-external-locking' instead.

From the preceding warning, we can see that the -- default-character-set and -- skip-locking options are outdated. We recommend that you use -- character-set-server and -- skip-external-locking.

8. Check the initialization directory.

[Root @ node6 data] # ls/data/mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index test

9. Start mysql.

[Root @ node6 ~] # Service mysqld start Starting MySQL... SUCCESS!