MySQL - Githubissues

iofdata / DM

Data Mining Books and Tools.

0 stars 1 forks source link

MySQL #5

Open Buttonwood opened 10 years ago

Buttonwood commented 10 years ago

安装MySQL

wget http://192.168.2.2:8080/download/MySQL-client-5.6.19-1.el6.x86_64.rpm
wget http://192.168.2.2:8080/download/MySQL-server-5.6.19-1.el6.x86_64.rpm
#wget -c http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-devel-5.6.19-1.el6.x86_64.rpm/from/http://mysql.spd.co.il/

rpm -ivg MySQL-server-5.6.19-1.el6.x86_64.rpm
# 报错 
#file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.19-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64

yum remove mysql-libs-5.1.*
rpm -ivh MySQL-server-5.6.19-1.el6.x86_64.rpm

rpm -ivg MySQL-client-5.6.19-1.el6.x86_64.rpm

chkconfig
service mysql start

# 数据目录
lsof|grep mysql
ll -rt /var/lib/mysql/   

# mysql #配置文件
ll -rt /usr/share/mysql/

#相关程序命令
which mysql 
/usr/bin/mysql

#启动脚本
ll -rt /etc/rc.d/init.d/

Buttonwood commented 10 years ago

mysql 报错 root@localhost 不能启动的问题

mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

service mysql stop
chkconfig
mysqld_safe --skip-grant-table

# http://wenku.baidu.com/link?url=ARPOWYE5qWssO6toplKyOFWoaVP57UeA3Lj3IUU00wk3tcP3EGpfEp52uJE8M_Ckio7sieL-8x0gOQ4Epwn8d4eUw_xpMNlGZZeswiPsg93
/etc/init.d/mysql stop 
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
mysql -u root mysql 
#mysql> UPDATE user SET Password='tanhao'  where USER='root';
mysql>   update mysql.user set password=password('tanhao') where user='root';
#mysql> select * FROM user WHERE USER='root'\G
mysql>SELECT host,user,password FROM user;
mysql> FLUSH PRIVILEGES;
mysql> quit
/etc/init.d/mysql restart 
mysql -uroot -p 
# You must SET PASSWORD before executing this statement解决
mysql> SET PASSWORD = PASSWORD('123456'); # new root key will be 123456

Buttonwood commented 10 years ago

切换mysql目录到 /home/data

#### 参见 http://database.51cto.com/art/200709/56715.htm 
mkdir /home/data
mysqladmin -u root -p shutdown # 关掉mysql服务
cd /home/data

lsof|grep mysql
ll -rt /var/lib/mysql # mysql 数据目录
mv /var/lib/mysql ./

##### 编辑MySQL的配置文件/etc/my.cnf
find / -name *.cnf -print 
    /home/data/mysql/auto.cnf
    /usr/share/doc/MySQL-server-5.6.19/my-default.cnf
    /usr/share/mysql/my-default.cnf
    /usr/my.cnf
    /etc/pki/tls/openssl.cnf
cp  /usr/my.cnf /etc/my.cnf
vi   /etc/my.cnf
#port    =   3306
socket  =   /home/data/mysql/mysql.sock
# 注释掉最后一行
##### 修改MySQL启动脚本/etc/init.d/mysql
vi　/etc/init.d/mysql
#datadir=/var/lib/mysql(注释此行)
datadir=/home/data/mysql (加上此行)
##### reboot
/etc/init.d/mysql restart 

##### error
ln -s  /home/data/mysql/mysql.sock /var/lib/mysql/

Buttonwood commented 10 years ago

sysbench 安装

http://blog.163.com/digoal@126/blog/static/16387704020134142151769/

# 找不到mysql的include 和lib文件目录
wget -c http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-devel-5.6.19-1.el6.x86_64.rpm/from/http://mysql.spd.co.il/
rpm -ivh MySQL-devel-5.6.19-1.el6.x86_64.rpm

wget -c http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-5.6.19-1.el6.x86_64.rpm/from/http://mysql.spd.co.il/
rpm -ivh MySQL-shared-5.6.19-1.el6.x86_64.rpm

rpm -qa|grep MySQL
rpm -ql MySQL-server-5.6.19-1.el6.x86_64

chkconfig --list|grep -i mysql

./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql/
make
make check
make install

最终解决方案

## 5。0 版本但是不能测试 oltp
http://www.lefred.be/node/154
rpm -ivh sysbench-0.5-2.el6_.x86_64.rpm
rpm -ql sysbench

#git clone https://github.com/nettedfish/sysbench_from_percona.git

## 转回 4.10 版本 解决方案
yum -y install libtool
yum -y install automake*
rm -rf libtool
ln -s /usr/bin/libtool ./
./autogen.sh
./configure 
make & make install
ln -s  /usr/local/bin/sysbench /usr/bin/ 
sysbench --test=oltp help

sysbench 0.4.10:  multi-threaded system evaluation benchmark

oltp options:
  --oltp-test-mode=STRING         test type to use {simple,complex,nontrx,sp} [complex]
  --oltp-sp-name=STRING           name of store procedure to call in SP test mode []
  --oltp-read-only=[on|off]       generate only 'read' queries (do not modify database) [off]
  --oltp-skip-trx=[on|off]        skip BEGIN/COMMIT statements [off]
  --oltp-range-size=N             range size for range queries [100]
  --oltp-point-selects=N          number of point selects [10]
  --oltp-simple-ranges=N          number of simple ranges [1]
  --oltp-sum-ranges=N             number of sum ranges [1]
  --oltp-order-ranges=N           number of ordered ranges [1]
  --oltp-distinct-ranges=N        number of distinct ranges [1]
  --oltp-index-updates=N          number of index update [1]
  --oltp-non-index-updates=N      number of non-index updates [1]
  --oltp-nontrx-mode=STRING       mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  --oltp-auto-inc=[on|off]        whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay=N          time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min=N         minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max=N         maximum time in microseconds to sleep after each request [0]
  --oltp-table-name=STRING        name of test table [sbtest]
  --oltp-table-size=N             number of records in test table [10000]
  --oltp-dist-type=STRING         random numbers distribution {uniform,gaussian,special} [special]
  --oltp-dist-iter=N              number of iterations used for numbers generation [12]
  --oltp-dist-pct=N               percentage of values to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res=N               percentage of 'special' values to use (for special distribution) [75]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]

Compiled-in database drivers:
  mysql - MySQL driver

mysql options:
  --mysql-host=[LIST,...]       MySQL server host [localhost]
  --mysql-port=N                MySQL server port [3306]
  --mysql-socket=STRING         MySQL socket
  --mysql-user=STRING           MySQL user [sbtest]
  --mysql-password=STRING       MySQL password []
  --mysql-db=STRING             MySQL database name [sbtest]
  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]
  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]
  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options=STRING additional options passed to CREATE TABLE []

###  关于libtool 错误的其它参考
http://wenku.baidu.com/link?url=DGlHEoWxiLWq4829Qp_7ngYB9r3glfkk_JOHEVNDedUWDSWJxYRPiv55KWkhSPnkeKPXjn2O2N6n-Xgqi_j4c2j2GIRfqU66l2NlWZ0mHSK

Buttonwood commented 10 years ago

CentOS mysql test

CREATE DATABASE students;
USE students;

CREATE TABLE transcripts(
stu_id int NOT NULL AUTO_INCREMENT,
stu_name    char(50)    NOT NULL,        
stu_class     char(50)    NULL,
stu_grade    int    NULL,
PRIMARY KEY(stu_id)      
) ENGIN=InnoDB;

SHOW TABLE STATUS LIKE 'transcripts';
SHOW CREATE TABLE transcripts;
ALTER TABLE transcripts ADD stu_email CHAR(50);

INSERT INTO transcripts(stu_id,stu_name,stu_class,stu_grade,stu_email)
VALUES 
(1, 'Jim','A',90,NULL),
(2, 'Tom','B',95,NULL),
(3, 'Jhon','A',70,NULL),
(4, 'Adam','B',65,NULL),
(5, 'Grace','A',80,NULL),
(6, 'Ana','B',95,NULL),
(7, 'Babe','A',80,NULL),
(8, 'KK','B',85,NULL),
(9, 'Han','A',70,NULL),
(10, 'Sun','B',75,NULL),
(11, 'GG','A',80,NULL),
(12, 'Dog','B',95,NULL),
(13, 'Cao','A',60,NULL),
(14, 'Sky','B',85,NULL),
(15, 'FF','A',90,NULL),
(16, 'Dom','B',85,NULL);

SELECT * FROM transcripts;

windows mysql test

cmd -> MySQL cmdline

USE mysql;
SELECT host,user,password FROM user;
SHOW variables like 'datadir';

SHOW variables like 'socket'\G; # socket 套接字

Buttonwood commented 10 years ago

1 登录及管理

mysql -h localhost -p -u root
mysql> GRANT ALL ON mysqlcookbook TO 'tom'@'localhost' IDENTIFIED   BY 'cbpass'
mysql> GRANT ALL ON pythoncookbook TO 'ana'@'xyz.com' IDENTIFIED   BY 'cbpass'
mysql -h ocalhost -p -u tom/ana
Enter password: cbpass

### 包含cookbook数据库的表备份
mysqldump -h localhost -p -u tom/ana mysqlcookbook >mysqlcookbook.sql
mysqldump -h localhost -p -u root students > students.sql

mysql -p -u root  test < students.sql
#### or
mysql> source  students.sql;
#### 管道
mysqldump -h localhost -p -u tom/ana mysqlcookbook | mysql  -h xyz.com -p -u root  test

mysqladmin -p -u root shutdown

Buttonwood commented 10 years ago

2 查询结构生成xml或者html文档

mysql  -h localhost -p -u tom/ana -e "SELECT * FROM limits WHERE legs=0 " mysqlcookbook
mysql  -h localhost -p -u root  -e "SELECT * FROM transcripts " test

mysql  -h localhost -p -u tom/ana -H -e "SELECT * FROM limits WHERE legs=0 " mysqlcookbook
mysql  -h localhost -p -u root -X -e "SELECT * FROM transcripts " test  # -H --> --html

mysql  -h localhost -p -u tom/ana -X -e "SELECT * FROM limits WHERE legs=0 " mysqlcookbook
mysql  -h localhost -p -u root -X -e "SELECT * FROM transcripts " test # -X --> --xml

Buttonwood commented 10 years ago

3 perl DBI

yum install perl-CPAN
perl -MCPAN -eshell
install DBI
# or
yum install perl-DBD-MySQL
# or
perl -MCPAN -e "install DBD::mysql"
# or
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.027.tar.gz
tar -xvzf /DBD-mysql-4.027.tar.gz
cd /DBD-mysql-4.027 && perl  Makefile.PL && make && make install

connect.pl

use strict;
use warning;
use DBI;

my $user = "root";
my $passwd = "123456";
my $host = "localhost";
my $database = "test";

my $dsn = "DBI:mysql:host=$host;database=$database ;";

my $dbh = DBI->connect($dsn,$user,$passwd) or die "Cannot connect to serve $host\n";
print "Connected\n";

$dbh->disconnect();
print "Disconnected\n";

Buttonwood commented 10 years ago

4 python DB

sudo yum search mysqldb
#sudo yum install MySQL-python.x86_64
wget https://pypi.python.org/packages/2.6/s/setuptools/setuptools-0.6c11-py2.6.egg  --no-check-certificate
wget https://pypi.python.org/packages/source/p/pip/pip-1.3.1.tar.gz --no-check-certificate
pip install MySQLdb
### TODO 20140723

connet.py

# -*- coding: UTF-8 -*-
import sys
import MySQLdb

def main():
    try:
        connect = MySQLdb.connect(db = "test",
                        host = "localhost",
                        user = "root",
                        passwd = "123456")
        # Non-local TCP/IP 
        '''
        connect = MySQLdb.connect(db = "test",
                        host = "mysql.example.com",
                        port = 3307,
                        user = "root",
                        passwd = "123456")
        '''
        print "Connected"
    except :
        print "Can not connect to server"

if __name__ == '__main__':
    main()

Buttonwood commented 10 years ago

sysbench 测试

1 cpu 性能测试

sysbench --test=cpu --cpu-max-prime=20000 run

cpu测试主要是进行素数的加法运算，在上面的例子中，指定了最大的素数为 20000，自己可以根据机器cpu的性能来适当调整数值。

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing CPU performance benchmark

Threads started!

Done.

Maximum prime number checked in CPU test: 20000

Test execution summary:
    total time:                          30.5083s
    total number of events:              10000
    total time taken by event execution: 30.5043
    per-request statistics:
         min:                                  2.83ms
         avg:                                  3.05ms
         max:                                  6.46ms
         approx.  95 percentile:               3.48ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   30.5043/0.00

2 线程测试

sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
Done.

Test execution summary:
    total time:                          0.6215s
    total number of events:              10000
    total time taken by event execution: 39.3849
    per-request statistics:
         min:                                  0.04ms
         avg:                                  3.94ms
         max:                                 85.26ms
         approx.  95 percentile:              30.41ms

Threads fairness:
    events (avg/stddev):           156.2500/10.52
    execution time (avg/stddev):   0.6154/0.00

3 磁盘IO性能测试，随机读写和删除

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

上述参数指定了最大创建16个线程，创建的文件总大小为3G，文件读写模式为随机读。

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
Done.

Test execution summary:
    total time:                          0.6215s
    total number of events:              10000
    total time taken by event execution: 39.3849
    per-request statistics:
         min:                                  0.04ms
         avg:                                  3.94ms
         max:                                 85.26ms
         approx.  95 percentile:              30.41ms

Threads fairness:
    events (avg/stddev):           156.2500/10.52
    execution time (avg/stddev):   0.6154/0.00

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Extra file open flags: 0
128 files, 24Mb each
3Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Threads started!
FATAL: Failed to read file! file: 3 pos: 0 errno = 0 (Q?)
FATAL: Failed to read file! file: 3 pos: 6340608 errno = 0 ()
FATAL: Failed to read file! file: 13 pos: 16531456 errno = 0 ()
FATAL: Failed to read file! file: 48 pos: 5537792 errno = 0 ()
FATAL: Failed to read file! file: 96 pos: 8503296 errno = 0 ()
FATAL: Failed to read file! file: 26 pos: 9568256 errno = 0 ()
FATAL: Failed to read file! file: 26 pos: 15794176 errno = 0 ()
FATAL: Failed to read file! file: 127 pos: 18300928 errno = 0 ()
FATAL: Failed to read file! file: 9 pos: 21331968 errno = 0 ()
FATAL: Failed to read file! file: 119 pos: 8421376 errno = 0 ()
FATAL: Failed to read file! file: 62 pos: 17448960 errno = 0 ()
FATAL: Failed to read file! file: 87 pos: 20398080 errno = 0 ()
FATAL: Failed to read file! file: 113 pos: 13156352 errno = 0 ()
FATAL: Failed to read file! file: 20 pos: 10649600 errno = 0 ()
FATAL: Failed to read file! file: 17 pos: 9060352 errno = 0 ()
FATAL: Failed to read file! file: 16 pos: 311296 errno = 0 ()
Done.

Operations performed:  0 Read, 0 Write, 0 Other = 0 Total
Read 0b  Written 0b  Total transferred 0b  (0b/sec)
    0.00 Requests/sec executed

Test execution summary:
    total time:                          0.0029s
    total number of events:              0
    total time taken by event execution: 0.0000
    per-request statistics:
         min:                            18446744073709.55ms
         avg:                                  0.00ms
         max:                                  0.00ms

Threads fairness:
    events (avg/stddev):           0.0000/0.00
    execution time (avg/stddev):   0.0000/0.00

sysbench 0.4.10:  multi-threaded system evaluation benchmark

Removing test files...

4 内存测试

sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run

上述参数指定了本次测试整个过程是在内存中传输 4G 的数据量，每个 block 大小为 8K。

5 mysql 性能测试

prepare data

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/home/data/mysql/mysql.sock --mysql-user=root --mysql-host=localhost \
--mysql-password=123456 --mysql-db=students --oltp-table-name=test prepare

sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'test'...
Creating 1000000 records in table 'test'...

run test

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/home/data/mysql/mysql.sock --mysql-user=root --mysql-host=localhost \
--mysql-password=123456 --mysql-db=students --oltp-table-name=test \
--max-requests=1000 --num-threads=100 run

sysbench 0.4.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
WARNING: Preparing of "BEGIN" is unsupported, using emulation
(last message repeated 99 times)
Running the test with following options:
Number of threads: 100

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 1000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            14000
        write:                           5000
        other:                           2000
        total:                           21000
    transactions:                        1000   (276.21 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000  (5247.91 per sec.)
    other operations:                    2000   (552.41 per sec.)

Test execution summary:
    total time:                          3.6205s
    total number of events:              1000
    total time taken by event execution: 356.3231
    per-request statistics:
         min:                                 55.01ms
         avg:                                356.32ms
         max:                               1223.34ms
         approx.  95 percentile:             764.73ms

Threads fairness:
    events (avg/stddev):           10.0000/0.76
    execution time (avg/stddev):   3.5632/0.03

clean data

sysbench --test=oltp --mysql-host=localhost  --mysql-user=root  --mysql-password=123456 \
--mysql-socket=/home/data/mysql/mysql.sock --mysql-db=students --oltp-table-name=test cleanup

以上测试内容参考 MySQL 中文网，结果为一台1核1G主机测试

Buttonwood commented 10 years ago

testing for record table size

host=localhost
port=3306
socket=/home/data/mysql/mysql.sock
user=root
password=123456
resultsdir=./results
sizes="1000000 2000000 3000000 4000000 5000000 6000000 7000000 8000000 9000000 10000000 11000000 12000000 13000000 14000000 15000000 16000000 17000000 18000000 19000000 20000000"

mkdir -p $resultsdir

for size in $sizes; do
    sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=$size --mysql-socket=$socket --mysql-user=$user --mysql-host=$host --mysql-password=$password --mysql-db=students --oltp-table-name=test$size prepare;
    sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=$size --mysql-socket=$socket --mysql-user=$user --mysql-host=$host --mysql-password=$password --mysql-db=students --oltp-table-name=test$size  --max-requests=1000 --num-threads=10 run | tee -a $resultsdir/sysbench.$size.report;
    sysbench --test=oltp --mysql-host=$host  --mysql-user=$user --mysql-password=$password --mysql-socket=$socket --mysql-db=students --oltp-table-name=test$size  cleanup;
done

statistics the report `stat.pl`

while (<>) {
    if (/^\s+read:\s+(\d+)/) {
        print "read:$1\n";
    } elsif (/^\s+write:\s+(\d+)/) {
        print "write:$1\n";
    } elsif(/^\s+other:\s+(\d+)/)   {
        print "other:$1\n";
    } elsif(/^\s+total:\s+(\d+)/)   {
        print "total:$1\n";
    } elsif(/^\s+transactions:\s+(\d+)\s+\((\d+\.(\d+){0,1})\s+per\s+sec\.\)/) {
        print "transactions:$1\ntransactions(per sec.):$2\n";
    } elsif(/^\s+deadlocks:\s+(\d+)/)   {
        print "deadlocks:$1\n";
    } elsif(/^\s+read\/write requests:\s+(\d+)\s+\((\d+\.(\d+){0,1})\s+per\s+sec\.\)/)   {
        print "read/write requests:$1\nread/write requests(per sec.):$2\n";
    } elsif(/^\s+other operations:\s+(\d+)\s+\((\d+\.(\d+){0,1})\s+per\s+sec\.\)/)   {
        print "other operations:$1\nother operations(per sec.):$2\n";
    } elsif(/^\s+total time:\s+(\d+\.(\d+){0,1})s/)   {
        print "total time:$1\n";
    } elsif(/^\s+total number of events:\s+(\d+)/)   {
        print "total number of events:$1\n";
    } elsif(/^\s+total time taken by event execution:\s+(\d+\.(\d+){0,1})/)   {
        print "total time taken by event execution:$1\n";
    } elsif(/^\s+min:\s+(\d+\.(\d+){0,1})ms/)   {
        print "min:$1\n";
    } elsif(/^\s+avg:\s+(\d+\.(\d+){0,1})ms/)   {
        print "avg:$1\n";
    } elsif(/^\s+max:\s+(\d+\.(\d+){0,1})ms/)   {
        print "max:$1\n";
    } elsif(/^\s+approx.  95 percentile:\s+(\d+\.(\d+){0,1})ms/)   {
        print "approx.  95 percentile:$1\n";
    } elsif(/^\s+events \(avg\/stddev\):\s+((\d+\.(\d+){0,1})\/(\d+\.(\d+){0,1}))/)   {
        print "events (avg/stddev):$1\n";
    } elsif(/^\s+execution time \(avg\/stddev\):\s+((\d+\.(\d+){0,1})\/(\d+\.(\d+){0,1}))/)   {
        print "execution time (avg/stddev):$1\n";
    } else{
        next;
    }
}

ll -rt *.report|awk '{print $NF}'|while read aa; do echo "perl test.pl $aa | awk -F : '{print \$2}' >$aa.stat " ;done |sh

paste $( ll -rt *.stat |awk '{print $NF}' | tr "\n" " " ) > all

# or

paste sysbench.1000000.report.stat sysbench.2000000.report.stat sysbench.3000000.report.stat sysbench.4000000.report.stat sysbench.5000000.report.stat sysbench.6000000.report.stat sysbench.7000000.report.stat sysbench.8000000.report.stat sysbench.9000000.report.stat sysbench.10000000.report.stat sysbench.11000000.report.stat sysbench.12000000.report.stat sysbench.13000000.report.stat sysbench.14000000.report.stat sysbench.15000000.report.stat sysbench.16000000.report.stat sysbench.17000000.report.stat sysbench.18000000.report.stat sysbench.19000000.report.stat sysbench.20000000.report.stat

Buttonwood commented 10 years ago

printf "threads,transactions,trns p/s,deadlocks,dls p/s,read/write requests,r/w reqs p/s,min,avg,max,99 percentile \n" ;

less ../nohup.out | \
egrep " cat|threads:|transactions:|deadlocks|read/write|min:|avg:|max:|percentile:" | \
sed '1 s/Number of threads: //' | \ 
tr -d "\n" | \
sed -e 's/Number of threads: /\n/g' \
-e 's/[A-Za-z\/]\{1,\}://g' \
-e 's/read\/write//g' \
-e 's/approx\.  95//g' \
-e 's/per sec.)//g' \
-e 's/ms//g' \
-e 's/(//g'  \
-e 's/  */,/g'

Buttonwood commented 10 years ago

For whole aggregate

host=localhost
port=3306
socket=/home/data/mysql/mysql.sock
user=root
password=123456

resultsdir=./results-thread

threads="8 16 32 64 128"

sizes="1000000 5000000 10000000 15000000 20000000 25000000 30000000"

printf "sizes,threads,transactions,trns p/s,deadlocks,dls p/s,read/write requests,r/w reqs p/s,min,avg,max,99 percentile \n" >> stat.txt

mkdir -p $resultsdir

for thread in $threads;do
        mkdir $resultsdir/thread-$thread
        for size in $sizes; do
            sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=$size  --mysql-socket=$socket --mysql-user=$user --mysql-host=$host --mysql-password=$password --mysql-db=students --oltp-table-name=test$size prepare;
             sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=$size --mysql-socket=$socket --mysql-user=$user --mysql-host=$host --mysql-password=$password --mysql-db=students --oltp-table-name=test$size  --max-requests=1000 --num-threads=$thread run | tee -a $resultsdir/thread-$thread/sysbench.$thread.$size.report;
             sysbench --test=oltp --mysql-host=$host  --mysql-user=$user --mysql-password=$password --mysql-socket=$socket --mysql-db=students --oltp-table-name=test$size  cleanup;

             cat $resultsdir/thread-$thread/sysbench.$thread.$size.report | \
    egrep " cat|threads:|transactions:|deadlocks|read/write|min:|avg:|max:|percentile:" | \
     sed  -e '1 s/Number of threads: //' | \
     tr -d "\n" | \
     sed -e 's/Number of threads: /\n/g' \
     -e 's/[A-Za-z\/]\{1,\}://g' \
     -e 's/read\/write//g' \
     -e 's/approx\.  95//g' \
     -e 's/per sec.)//g' \
     -e 's/ms//g' \
     -e 's/(//g'  \
     -e 's/  */,/g' | awk -v d=$size '{$0=d","$0}1' >> stat.txt

        done
done

Buttonwood commented 10 years ago

cat $( ll -rt  results-thread/thread-*/sys* | awk '{print $NF}' | tr "\n" " " ) | \ 
egrep " cat|threads:|transactions:|deadlocks|read/write|min:|avg:|max:|percentile:" | sed '1 s/Number of threads: //' | tr -d "\n" | sed -e 's/Number of threads: /\n/g' -e 's/[A-Za-z\/]\{1,\}://g'  -e 's/read\/write//g' -e 's/approx\.  95//g' -e 's/per sec.)//g' -e 's/ms//g' -e 's/(//g'  -e 's/  */,/g' |less

cat nohup.out | egrep " cat|threads:|transactions:|deadlocks|read/write|min:|avg:|max:|percentile:"|sed '1 s/Number of threads: //'|tr -d "\n"|sed -e 's/Number of threads: /\n/g' -e 's/[A-Za-z\/]\{1,\}://g' -e 's/read\/write//g' -e 's/approx\.  95//g'  -e 's/per sec.)//g'  -e 's/ms//g' -e 's/(//g'  -e 's/  */,/g'

SIZES,THREADS,TRANSACTIONS,TRNS P/S,DEADLOCKS,DLS P/S,READ/WRITE REQUESTS,R/W REQS P/S,MIN,AVG,MAX,99 PERCENTILE
1000000,8,1000,236.13,0,0.00,19000,4486.45,7.16,33.66,118.17,59.32
5000000,8,1000,265.81,0,0.00,19000,5050.47,6.35,29.99,99.14,46.96
10000000,8,1000,379.46,127,48.19,21413,8125.43,3.54,21.01,306.08,34.70
15000000,8,1000,202.33,0,0.00,19000,3844.31,8.76,39.36,113.15,62.77
20000000,8,1000,185.34,0,0.00,19000,3521.38,10.48,43.05,111.56,66.92
25000000,8,1000,171.11,0,0.00,19000,3251.01,7.46,46.69,506.47,74.60
30000000,8,1000,163.24,0,0.00,19000,3101.55,14.49,48.91,504.90,77.21
1000000,16,1000,288.82,0,0.00,19000,5487.51,7.82,54.95,168.84,89.33
5000000,16,1000,270.04,0,0.00,19000,5130.82,19.13,59.02,323.48,92.27
10000000,16,1000,231.99,0,0.00,19000,4407.90,5.73,68.45,329.40,112.02
15000000,16,1000,230.78,0,0.00,19000,4384.76,8.29,69.03,233.97,124.21
20000000,16,1000,170.32,0,0.00,19000,3236.17,9.48,93.60,799.09,158.71
25000000,16,1000,164.44,0,0.00,19000,3124.34,5.68,96.44,633.64,165.06
30000000,16,1000,174.87,0,0.00,19000,3322.48,9.50,90.89,520.92,151.65
1000000,32,1000,316.29,0,0.00,19000,6009.43,7.72,99.23,346.24,172.95
5000000,32,1000,248.03,0,0.00,19000,4712.60,23.21,125.06,383.60,208.35
10000000,32,1000,229.40,0,0.00,19000,4358.52,5.39,138.82,598.64,350.74
15000000,32,1000,211.46,0,0.00,19000,4017.80,13.27,150.13,501.56,284.44
20000000,32,1000,198.36,0,0.00,19000,3768.77,8.54,159.58,822.41,252.87
25000000,32,1000,181.85,0,0.00,19000,3455.19,21.85,174.35,891.91,271.78
30000000,32,1000,156.00,0,0.00,19000,2964.03,9.45,204.21,811.06,339.07
1000000,64,1000,311.37,0,0.00,19000,5916.07,19.86,198.48,562.27,369.82
5000000,64,1000,287.12,0,0.00,19000,5455.27,29.12,216.80,843.55,338.67
10000000,64,1000,222.90,0,0.00,19000,4235.16,13.82,285.81,975.70,773.48
15000000,64,1000,193.57,0,0.00,19000,3677.74,12.19,327.30,1143.78,846.66
20000000,64,1000,170.73,0,0.00,19000,3243.88,55.67,370.57,1217.91,1092.95
25000000,64,1000,157.72,0,0.00,19000,2996.74,11.59,401.83,1680.79,1306.81
30000000,64,1000,158.19,0,0.00,19000,3005.66,19.20,401.41,1304.60,1107.11
1000000,128,1000,302.16,0,0.00,19000,5741.04,52.05,408.05,1041.78,772.32
5000000,128,1000,251.01,0,0.00,19000,4769.20,26.99,489.92,1724.37,781.39
10000000,128,1000,207.77,0,0.00,19000,3947.55,102.64,604.39,1577.85,1285.85
15000000,128,1000,149.60,0,0.00,19000,2842.35,146.68,841.09,3348.05,2764.42
20000000,128,1000,145.41,0,0.00,19000,2762.83,132.31,865.04,3247.91,2620.98
25000000,128,1000,139.95,0,0.00,19000,2658.97,252.97,896.25,2942.05,2773.54
30000000,128,1000,115.80,0,0.00,19000,2200.18,221.51,1089.31,4408.08,4141.01

Buttonwood commented 10 years ago

plot for the statistics

library("ggplot2")
df <- read.csv("C:\\Users\\tanhao\\Documents\\GitHub\\DM\\mysql\\work\\stat.csv",head=TRUE)
ggplot(df, aes(x = factor(sizes), y = trns.p.s, fill=factor(threads))) + geom_bar(stat = "identity", position="dodge")

1核1G

1K1G

4核4G

20140728141952-stat-44

Buttonwood commented 10 years ago

A 数据表大小查询

# 1、进去指定schema 数据库（存放了其他的数据库的信息）
mysql> use information_schema;

# 2、查询所有数据的大小  
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB') as data from TABLES;

+-----------+
| data      |
+-----------+
| 1948.72MB |
+-----------+
1 row in set (0.31 sec)

# 3、查看指定数据库实例的大小，比如说数据库 forexpert 
mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB')
         -> as data from TABLES where table_schema='students';

+-----------+
| data      |
+-----------+
| 1948.02MB |
+-----------+
1 row in set (0.00 sec)

# 4、查看指定数据库的表的大小，比如说数据库 forexpert 中的 member 表
 mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') 
          -> as data from TABLES where table_schema='students'
          -> and table_name='test';

+-----------+
| data      |
+-----------+
| 1948.00MB |
+-----------+
1 row in set (0.00 sec)

eyeofdata commented 10 years ago

Perl DBI

##  A 不返回结果
##  INSERT
##  DELETE
##  UPDATE
my $count = $dbh->("UPDATE profile SET cats = cats + 1 WHERE name = 'Fred' ");
if ($count) { # 如果发生错误则输出行数
    $count += 0; # 0E0
    print "Number of row updated: %d\n", $count;
}

##  B 返回结果
##  SELECT
##  SHOW
##  EXPLAIN
##  DESCRIBE
##  1.首先,prepare()执行语句,
##      成功: 返回用于后续操作的statement句柄，
##      失败: 停止或返回undef
##  2.调用execute()执行语句，生成结果集
##  3.循环语句获取返回的行
##  4.finish() 释放资源

#1
my $sth = $dbh->prepare("SELECT id, name, cats FROM profile"); 
#2
$sth->execute();
my $count;
#3
# fetchrow_array()显示的定义select语句
# my $ref = $sth->fetchrow_arrayref()  通过$ref->[0]来访问
# my $ref = $sth->fetchrow_hashref()   通过$ref->{id}来访问，列名需唯一
while (my @val = $sth->fetchrow_array()) {
    print "id:$val[0], name:$val[1],cats:$val[2]\n";
    ++$count;
}
#4
$sth->finish();
print "Number of rows returned: %d\n", $count;

##  数据库句柄方法
#   selectrow_array() 
#   selectrow_arrayref()
#   selectcol_arrayref()
#   selectall_arrayref()
#   selectrow_hashref()
#   selectall_hashref()
my @val = $dbh->selectrow_array("SELECT id, name, cats FROM profile WHERE id = 3");

eyeofdata commented 10 years ago

Python DBI

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

"""
1 python DBI 数据库连接对象的获得一个游标对象；
2 游标的execute()向服务器发送语句，抛出异常或返回结果集
"""

##  A 不返回结果
##  INSERT
##  DELETE
##  UPDATE
cursor = conn.cursor()
cursor.execute("UPDATE profile SET cats = cats + 1 WHERE name = 'Fred'")
print "Number of rows updated: %d" % cursor.rowcount
cursor.close()

##  B 返回结果
##  SELECT
##  SHOW
##  EXPLAIN
##  DESCRIBE
##  fetchone() 顺序返回下一行
cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
while 1 :
    row = cursor.fetchone() #len(row)
    if row == None :
        break
    print "id: %s, name: %s, cats:%s" % (row[0],row[1],row[2])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close()

##  fetchall() 行序列方式返回整个结果集
cursor = conn.cursor()
cursor.execute("SELECT id, name, cats FROM profile")
rows= cursor.fetchall() #len(rows) 
for row in rows:  #rows[1][2]
    print "id: %s, name: %s, cats:%s" % (row[0],row[1],row[2])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close()

##  DictCursor通过列名来访问行值
cursor = conn.cursor(MySQLdb.cursor.DictCursor)
cursor.execute("SELECT id, name, cats FROM profile")
for row in cursor.fetchall():
    print "id: %s, name: %s, cats:%s" % (row[0],row[1],row[2])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close()

Buttonwood commented 9 years ago

OSX mysql cannot load

mysql> use mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database ‘mysql'

Then I do with this:

sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo /usr/local/mysql/bin//mysqld_safe --skip-grant-table
# start a new term
mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root’;
mysql> FLUSH PRIVILEGES;
mysql> \q

Helpers

Buttonwood commented 9 years ago

# 是否指定mysql数据盘
DATADIR="/home/data"
# 是否指定密码
PASSWORD='123456'
# 公网ip为1，局域网ip为0
EIP=0
# 安装区域
ZONE='GD1'
MYSQL='/usr/bin/mysql'
USER='root'
HOST='localhost'

if [[ $ZONE = 'PEK2' ]]; then
        if [[ $EIP = 1 ]]; then
                echo "# IN PEK2 and your ip is a eip ... "
                echo "wget http://bigone.50izqj.pek2.qingcloud.com:8081/download/MySQL-server-5.6.19-1.el6.x86_64.rpm"
                echo "wget http://bigone.50izqj.pek2.qingcloud.com:8081/download/MySQL-client-5.6.19-1.el6.x86_64.rpm"
        else
                echo "# IN PEK2 and your ip is a pip ... "
                echo "wget http://192.168.3.254:8080/download/MySQL-server-5.6.19-1.el6.x86_64.rpm"
                echo "wget http://192.168.3.254:8080/download/MySQL-client-5.6.19-1.el6.x86_64.rpm"
        fi
else
        echo "# NOT IN PEK2 ... "
        echo "wget http://119.254.110.32:8081/download/MySQL-server-5.6.19-1.el6.x86_64.rpm"
        echo "wget http://119.254.110.32:8081/download/MySQL-client-5.6.19-1.el6.x86_64.rpm"
fi

if [[ $PASSWORD = '' ]]; then
        echo "# YOU set no password and we set it to 123456 ... "
        PASSWORD='123456'
fi

if [[ -f "/etc/my.cnf" ]];then
        t=$(date +%Y%m%d%H%M%S)
        echo ""
        echo "mv /etc/my.cnf /etc/my.cnf.$t.bak"
        echo "cp /usr/my.cnf /etc/my.cnf"
fi

echo "yum remove mysql-libs-5.* -y"
echo "yum erase mysql MySQL -y"
echo "rpm -ivg MySQL-client-5.6.19-1.el6.x86_64.rpm"
echo "rpm -ivg MySQL-server-5.6.19-1.el6.x86_64.rpm"
echo "service mysql start"

echo "password=\`grep password /root/.mysql_secret|tail -1| awk -F \":\" '{print \$NF}' |less |sed 's/^ //'\`"
echo "echo \"# CHANGE default  password=>\$password TO $PASSWORD ... \""
echo "$MYSQL -u$USER -p\$password -h$HOST --connect-expired-password -e \"SET PASSWORD = PASSWORD('$PASSWORD');FLUSH PRIVILEGES;\""
#$MYSQL -u$USER -p$password -h$HOST mysql -ss -e "SET PASSWORD = PASSWORD($PASSWORD);FLUSH PRIVILEGES;"
echo ""
echo "service mysql stop"

if [[ -f "/etc/my.cnf" ]];then
        sleep 5
        t=$(date +%Y%m%d%H%M%S)
        echo "cp /etc/my.cnf /etc/my.cnf.$t.bak"
        echo "cp /usr/my.cnf /etc/my.cnf"
fi

if [[ $DATADIR != '' ]]; then
        if [[ ! -d $DATADIR ]]; then
                echo "mkdir $DATADIR"
        else
                echo "rm -rf $DATADIR"
                echo "mkdir $DATADIR"
        fi
        echo "cp /var/lib/mysql /var/lib/mysql.bak"
        echo "cp -Rap /var/lib/mysql $DATADIR"
        echo "chown -R mysql:mysql $DATADIR/mysql"
        echo "echo \"datadir=$DATADIR/mysql\" >>/etc/my.cnf"
        echo "echo \"socket=$DATADIR/mysql/mysql.sock\" >>/etc/my.cnf"
        #mkdir /var/lib/mysql/
        #echo "ln -s $datadir/mysql/mysql.sock /var/lib/mysql/mysql.sock"
fi

echo "echo \"\" >>/etc/my.cnf"
echo "echo \"slow_query_log = on \"  >>/etc/my.cnf"
echo "echo \"slow_query_log_file = slow-query.log\"  >>/etc/my.cnf"
echo "echo \"long_query_time = 5\"  >>/etc/my.cnf"

echo "echo \"max_connections = 250\"  >>/etc/my.cnf"
echo "echo \"wait_timeout = 128800\"  >>/etc/my.cnf"
echo "echo \"\" >>/etc/my.cnf"

echo "echo [client] >>/etc/my.cnf"
echo "echo \"default-character-set = utf8\" >>/etc/my.cnf"
echo "service mysql start"

Buttonwood commented 9 years ago

PASSWORD='123456'
DATADIR='/home/data'

wget http://119.254.110.32:8081/download/MySQL-server-5.6.19-1.el6.x86_64.rpm
wget http://119.254.110.32:8081/download/MySQL-client-5.6.19-1.el6.x86_64.rpm
yum remove mysql-libs-5.* -y
rpm -ivg MySQL-client-5.6.19-1.el6.x86_64.rpm
rpm -ivg MySQL-server-5.6.19-1.el6.x86_64.rpm

service mysql start
password=`grep password /root/.mysql_secret|tail -1| awk -F ":" '{print $NF}' |less |sed 's/^ //'`
mysql -uroot -p$password -hlocalhost --connect-expired-password -e "SET PASSWORD = PASSWORD('$PASSWORD');FLUSH PRIVILEGES;"

service mysql stop
cd  /var/lib/
cp -Rap mysql mysql.bak
cp -Rap /var/lib/mysql $DATADIR
chown -R mysql:mysql $DATADIR/mysql
ln -s $DATADIR/mysql/mysql.sock /var/lib/mysql/mysql.sock
cp /usr/my.cnf /etc/my.cnf

echo "datadir=$DATADIR/mysql" >>/etc/my.cnf
echo "socket=$DATADIR/mysql/mysql.sock" >>/etc/my.cnf
echo "" >>/etc/my.cnf
echo "slow_query_log = on "  >>/etc/my.cnf
echo "slow_query_log_file = slow-query.log"  >>/etc/my.cnf
echo "long_query_time = 5"  >>/etc/my.cnf
echo "max_connections = 250"  >>/etc/my.cnf
echo "wait_timeout = 128800"  >>/etc/my.cnf
echo "" >>/etc/my.cnf
echo "[client]" >>/etc/my.cnf
echo "default-character-set = utf8" >>/etc/my.cnf
service mysql start

iofdata / DM

MySQL #5

安装MySQL

mysql 报错 root@localhost 不能启动的问题

切换mysql目录到 /home/data

sysbench 安装

最终解决方案

CentOS mysql test

windows mysql test

1 登录及管理

2 查询结构生成xml或者html文档

3 perl DBI

4 python DB

sysbench 测试

1 cpu 性能测试

2 线程测试

3 磁盘IO性能测试，随机读写和删除

4 内存测试

5 mysql 性能测试

prepare data

run test

clean data

testing for record table size

statistics the report stat.pl

For whole aggregate

plot for the statistics

1核1G

4核4G

A 数据表大小查询

Perl DBI

Python DBI

statistics the report `stat.pl`