sysbench-tpccでできるテーブル定義確認, SSD買う

SSD

実験用にSSDが余っていなかったので買う、結構時間を使ってしまったが大した話ではないので省略

sysbench-tpccの中身

時間がぎりぎり担ってしまったので、確認したテーブルの定義を貼っておく

definitions

customer

mysql> show create table customer1\G
*************************** 1. row ***************************
       Table: customer1
Create Table: CREATE TABLE `customer1` (
  `c_id` int(11) NOT NULL,
  `c_d_id` tinyint(4) NOT NULL,
  `c_w_id` smallint(6) NOT NULL,
  `c_first` varchar(16) DEFAULT NULL,
  `c_middle` char(2) DEFAULT NULL,
  `c_last` varchar(16) DEFAULT NULL,
  `c_street_1` varchar(20) DEFAULT NULL,
  `c_street_2` varchar(20) DEFAULT NULL,
  `c_city` varchar(20) DEFAULT NULL,
  `c_state` char(2) DEFAULT NULL,
  `c_zip` char(9) DEFAULT NULL,
  `c_phone` char(16) DEFAULT NULL,
  `c_since` datetime DEFAULT NULL,
  `c_credit` char(2) DEFAULT NULL,
  `c_credit_lim` bigint(20) DEFAULT NULL,
  `c_discount` decimal(4,2) DEFAULT NULL,
  `c_balance` decimal(12,2) DEFAULT NULL,
  `c_ytd_payment` decimal(12,2) DEFAULT NULL,
  `c_payment_cnt` smallint(6) DEFAULT NULL,
  `c_delivery_cnt` smallint(6) DEFAULT NULL,
  `c_data` text,
  PRIMARY KEY (`c_w_id`,`c_d_id`,`c_id`),
  KEY `idx_customer1` (`c_w_id`,`c_d_id`,`c_last`,`c_first`),
  CONSTRAINT `fkey_customer_1_1` FOREIGN KEY (`c_w_id`, `c_d_id`) REFERENCES `district1` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

distinct

mysql> show create table district1\G
*************************** 1. row ***************************
       Table: district1
Create Table: CREATE TABLE `district1` (
  `d_id` tinyint(4) NOT NULL,
  `d_w_id` smallint(6) NOT NULL,
  `d_name` varchar(10) DEFAULT NULL,
  `d_street_1` varchar(20) DEFAULT NULL,
  `d_street_2` varchar(20) DEFAULT NULL,
  `d_city` varchar(20) DEFAULT NULL,
  `d_state` char(2) DEFAULT NULL,
  `d_zip` char(9) DEFAULT NULL,
  `d_tax` decimal(4,2) DEFAULT NULL,
  `d_ytd` decimal(12,2) DEFAULT NULL,
  `d_next_o_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`d_w_id`,`d_id`),
  CONSTRAINT `fkey_district_1_1` FOREIGN KEY (`d_w_id`) REFERENCES `warehouse1` (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

history

mysql> show create table history1\G
*************************** 1. row ***************************
       Table: history1
Create Table: CREATE TABLE `history1` (
  `h_c_id` int(11) DEFAULT NULL,
  `h_c_d_id` tinyint(4) DEFAULT NULL,
  `h_c_w_id` smallint(6) DEFAULT NULL,
  `h_d_id` tinyint(4) DEFAULT NULL,
  `h_w_id` smallint(6) DEFAULT NULL,
  `h_date` datetime DEFAULT NULL,
  `h_amount` decimal(6,2) DEFAULT NULL,
  `h_data` varchar(24) DEFAULT NULL,
  KEY `fkey_history_11` (`h_c_w_id`,`h_c_d_id`,`h_c_id`),
  KEY `fkey_history_21` (`h_w_id`,`h_d_id`),
  CONSTRAINT `fkey_history_1_1` FOREIGN KEY (`h_c_w_id`, `h_c_d_id`, `h_c_id`) REFERENCES `customer1` (`c_w_id`, `c_d_id`, `c_id`),
  CONSTRAINT `fkey_history_2_1` FOREIGN KEY (`h_w_id`, `h_d_id`) REFERENCES `district1` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

item

mysql> show create table item1\G
*************************** 1. row ***************************
       Table: item1
Create Table: CREATE TABLE `item1` (
  `i_id` int(11) NOT NULL,
  `i_im_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

new_orders

mysql> show create table new_orders1\G
*************************** 1. row ***************************
       Table: new_orders1
Create Table: CREATE TABLE `new_orders1` (
  `no_o_id` int(11) NOT NULL,
  `no_d_id` tinyint(4) NOT NULL,
  `no_w_id` smallint(6) NOT NULL,
  PRIMARY KEY (`no_w_id`,`no_d_id`,`no_o_id`),
  CONSTRAINT `fkey_new_orders_1_1` FOREIGN KEY (`no_w_id`, `no_d_id`, `no_o_id`) REFERENCES `orders1` (`o_w_id`, `o_d_id`, `o_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

order_line

mysql> show create table order_line1\G
*************************** 1. row ***************************
       Table: order_line1
Create Table: CREATE TABLE `order_line1` (
  `ol_o_id` int(11) NOT NULL,
  `ol_d_id` tinyint(4) NOT NULL,
  `ol_w_id` smallint(6) NOT NULL,
  `ol_number` tinyint(4) NOT NULL,
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` smallint(6) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` tinyint(4) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  KEY `fkey_order_line_21` (`ol_supply_w_id`,`ol_i_id`),
  CONSTRAINT `fkey_order_line_1_1` FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`) REFERENCES `orders1` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `fkey_order_line_2_1` FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`) REFERENCES `stock1` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

orders

mysql> show create table orders1\G
*************************** 1. row ***************************
       Table: orders1
Create Table: CREATE TABLE `orders1` (
  `o_id` int(11) NOT NULL,
  `o_d_id` tinyint(4) NOT NULL,
  `o_w_id` smallint(6) NOT NULL,
  `o_c_id` int(11) DEFAULT NULL,
  `o_entry_d` datetime DEFAULT NULL,
  `o_carrier_id` tinyint(4) DEFAULT NULL,
  `o_ol_cnt` tinyint(4) DEFAULT NULL,
  `o_all_local` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),
  KEY `idx_orders1` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),
  CONSTRAINT `fkey_orders_1_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer1` (`c_w_id`, `c_d_id`, `c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

stock

mysql> show create table stock1\G
*************************** 1. row ***************************
       Table: stock1
Create Table: CREATE TABLE `stock1` (
  `s_i_id` int(11) NOT NULL,
  `s_w_id` smallint(6) NOT NULL,
  `s_quantity` smallint(6) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  `s_ytd` decimal(8,0) DEFAULT NULL,
  `s_order_cnt` smallint(6) DEFAULT NULL,
  `s_remote_cnt` smallint(6) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`),
  KEY `fkey_stock_21` (`s_i_id`),
  CONSTRAINT `fkey_stock_1_1` FOREIGN KEY (`s_w_id`) REFERENCES `warehouse1` (`w_id`),
  CONSTRAINT `fkey_stock_2_1` FOREIGN KEY (`s_i_id`) REFERENCES `item1` (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

warehouse

mysql> show create table warehouse1\G
*************************** 1. row ***************************
       Table: warehouse1
Create Table: CREATE TABLE `warehouse1` (
  `w_id` smallint(6) NOT NULL,
  `w_name` varchar(10) DEFAULT NULL,
  `w_street_1` varchar(20) DEFAULT NULL,
  `w_street_2` varchar(20) DEFAULT NULL,
  `w_city` varchar(20) DEFAULT NULL,
  `w_state` char(2) DEFAULT NULL,
  `w_zip` char(9) DEFAULT NULL,
  `w_tax` decimal(4,2) DEFAULT NULL,
  `w_ytd` decimal(12,2) DEFAULT NULL,
  PRIMARY KEY (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

.