分类： Mysql/postgreSQL
2011-09-15 11:58:10
# sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-db=test --oltp-table-name=dbtest --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-engine-trx=yes --mysql-user=root --mysql-socket=/data/mysqldata/mysql.sock --mysql-port=3306 --num-threads=100 prepare
2、
(root@localhost:)[test]> show table status from test\G
*************************** 1. row ***************************
Name: dbtest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10000060
Avg_row_length: 224
Data_length: 2247098368 2143M
Max_data_length: 0 data_length+index_length=2273.672 (2.22038G)
Index_length: 137019392 130.6719M
Data_free: 4194304 4M
Auto_increment: 10000001
Create_time: 2011-09-15 11:20:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
3、
> select count(*) from dbtest;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.13 sec)
4、
# du -sh /data1/mysqldata/data/test/dbtest.ibd
2.3G /data1/mysqldata/data/test/dbtest.ibd
4、
> delete from dbtest where id <=5000000;
Query OK, 5000000 rows affected (33.72 sec)
5、第一次查看表的status
(root@localhost:)[test]> show table status \G
*************************** 1. row ***************************
Name: dbtest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8503925
Avg_row_length: 224
Data_length: 1911799808
Max_data_length: 0
Index_length: 117604352
Data_free: 357564416
Auto_increment: 10000001
Create_time: 2011-09-15 11:20:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
6、第二次看表的status有变化
(root@localhost:)[test]> show table status \G
*************************** 1. row ***************************
Name: dbtest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5000071
Avg_row_length: 225
Data_length: 1125154816 1073.031
Max_data_length: 0 data_length+index_length=1139.141 (1.112442G)
Index_length: 69320704 66.10.8
Data_free: 1192230912 1137M (1.11035G)
Auto_increment: 10000001
Create_time: 2011-09-15 11:20:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
7、
> select count(*) from dbtest;
+----------+
| count(*) |
+----------+
| 5000000 |
+----------+
1 row in set (1.07 sec)
8、
# du -sh /data1/mysqldata/data/test/dbtest.ibd
2.3G /data1/mysqldata/data/test/dbtest.ibd
9、
> alter table dbtest engine=innodb;
Query OK, 5000000 rows affected (46.84 sec)
Records: 5000000 Duplicates: 0 Warnings: 0
10、
> show table status \G
*************************** 1. row ***************************
Name: dbtest
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5000071
Avg_row_length: 224
Data_length: 1124073472 1072M
Max_data_length: 0 data_length+index_length=1137.594M(1.11093)
Index_length: 68780032 65.59375M
Data_free: 4194304 4M
Auto_increment: 10000001
Create_time: 2011-09-15 11:31:53
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
11、
# du -sh /data1/mysqldata/data/test/dbtest.ibd
1.2G /data1/mysqldata/data/test/dbtest.ibd
