MySQL Forums
Forum List  »  Performance

Re: How to see key_buffer in action
Posted by: karen wang
Date: November 11, 2012 06:58PM

thank you for your answer
some information:
[ code ]
mysql> show create table base_info\G
*************************** 1. row ***************************
Table: base_info
Create Table: CREATE TABLE `base_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`phase` int(11) NOT NULL DEFAULT '1',
`code_region_oid` decimal(10,0) DEFAULT NULL,
`code_urban_rural_oid` decimal(10,0) DEFAULT NULL,
`exam_id` int(11) NOT NULL,
`exam_name` varchar(45) DEFAULT NULL,
`location` varchar(200) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth_date` date DEFAULT NULL,
`code_gender_oid` int(11) DEFAULT NULL,
`code_race_oid` int(11) DEFAULT NULL,
`code_education_level_oid` int(11) DEFAULT NULL,
`code_occupation_oid` int(11) DEFAULT NULL,
`code_marriage_oid` int(11) DEFAULT NULL,
`native_place` varchar(45) DEFAULT NULL,
`birthplace` varchar(200) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `base_info_indx1` (`code_gender_oid`,`code_race_oid`,`code_region_oid`),
KEY `base_info_indx2` (`id`,`code_gender_oid`,`code_race_oid`,`code_region_oid`)
) ENGINE=MyISAM AUTO_INCREMENT=40916 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[ /code ]
[ code ]
mysql> show create table physical_info\G
*************************** 1. row ***************************
Table: physical_info
Create Table: CREATE TABLE `physical_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`base_info_id` int(11) NOT NULL,
`height` decimal(11,2) DEFAULT NULL,
`weight` decimal(11,2) DEFAULT NULL,
`sitting_height` decimal(11,2) DEFAULT NULL,
`hip_circumference` decimal(11,2) DEFAULT NULL,
`chest_circumference` decimal(11,2) DEFAULT NULL,
`waist_circumference` decimal(11,2) DEFAULT NULL,
`systolic_blood_pressure` decimal(11,0) DEFAULT NULL,
`diastolic_blood_pressure` decimal(11,0) DEFAULT NULL,
`heart_rate` decimal(8,0) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_physical_info_base_info1` (`base_info_id`)
) ENGINE=MyISAM AUTO_INCREMENT=40916 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[ /code ]
[ code ]
mysql> show table status like 'base_info' \G;
*************************** 1. row ***************************
Name: base_info
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 40915
Avg_row_length: 59
Data_length: 2437416
Max_data_length: 281474976710655
Index_length: 2473984
Data_free: 0
Auto_increment: 40916
Create_time: 2012-11-12 08:37:08
Update_time: 2012-11-12 23:42:30
Check_time: 2012-11-12 08:37:09
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

ERROR:
No query specified
[ /code ]
[ code ]
mysql> show table status like 'physical_info' \G;
*************************** 1. row ***************************
Name: physical_info
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 40915
Avg_row_length: 54
Data_length: 2209410
Max_data_length: 15199648742375423
Index_length: 840704
Data_free: 0
Auto_increment: 40916
Create_time: 2012-11-10 13:59:27
Update_time: 2012-11-11 05:04:35
Check_time: 2012-11-10 14:02:50
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

ERROR:
No query specified
[ /code ]
[ code ]
mysql> explain select b.code_gender_oid, b.code_race_oid, b.code_region_oid, avg(a.height), stddev(a.height), avg(a.height) - 1.96 * STDDEV(a.height) refa, avg(a.height) + 1.96 * STDDEV(a.height) refb from base_info b left join physical_info a on b.id = a.base_info_id group by b.code_gender_oid, b.code_race_oid, b.code_region_oid;
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
| 1 | SIMPLE | b | index | NULL | base_info_indx2 | 20 | NULL | 40915 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | fk_physical_info_base_info1 | fk_physical_info_base_info1 | 4 | CAMS_physiology.b.id | 1 | |
+----+-------------+-------+-------+-----------------------------+-----------------------------+---------+----------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
[ /code ]
[ code ]
mysql> show variables like '%buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 134217728 |
| innodb_buffer_pool_size | 536870912 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 209715200 |
| key_buffer_size | 5368709120 |
| myisam_sort_buffer_size | 268435456 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 16777216 |
| read_rnd_buffer_size | 33554432 |
| sort_buffer_size | 16777216 |
| sql_buffer_result | OFF |
+-------------------------+------------+
12 rows in set (0.00 sec)
[ /code ]

Options: ReplyQuote


Subject
Views
Written By
Posted
2856
November 08, 2012 01:01PM
1040
November 09, 2012 05:54AM
1268
November 11, 2012 12:45PM
Re: How to see key_buffer in action
1017
November 11, 2012 06:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.