Suggestion for optimization - reg
Hi,
I am trying to optimize a table which going to contain around 5 million of records. The details are as follows:
mysql> show create table population_backup\G;
*************************** 1. row ***************************
Table: population_backup
Create Table: CREATE TABLE `population_backup` (
`statecode` varchar(2) NOT NULL DEFAULT '',
`districtcode` varchar(2) NOT NULL DEFAULT '',
`tehsilcode` varchar(4) NOT NULL DEFAULT '',
`towncode` varchar(8) NOT NULL DEFAULT '',
`wardid` varchar(4) NOT NULL DEFAULT '0',
`mnicblockno` varchar(4) NOT NULL DEFAULT '0',
`mniccenterno` varchar(5) NOT NULL DEFAULT '0',
`house_no` varchar(10) NOT NULL DEFAULT '',
`policestation` varchar(50) NOT NULL DEFAULT '',
`scheduleno` varchar(3) NOT NULL DEFAULT '0',
`typeofhh` varchar(20) NOT NULL DEFAULT '',
`slnohhd` varchar(3) NOT NULL DEFAULT '0',
`slnomember` varchar(3) NOT NULL DEFAULT '0',
`name` varchar(99) NOT NULL DEFAULT '',
`fathernm` varchar(99) DEFAULT '',
`mothernm` varchar(99) DEFAULT '',
`spousenm` varchar(99) DEFAULT '',
`name_on_card` varchar(33) DEFAULT '',
`uidno` varchar(16) DEFAULT '',
`relname` varchar(99) DEFAULT '',
`genderid` char(1) NOT NULL DEFAULT '',
`dob` date DEFAULT NULL,
`dob_type` char(1) NOT NULL DEFAULT '',
`mstatusid` char(1) NOT NULL DEFAULT '',
`eduname` varchar(99) DEFAULT '',
`occuname` varchar(99) NOT NULL DEFAULT '',
`natname` varchar(99) NOT NULL DEFAULT '',
`villagename` varchar(50) DEFAULT '',
`village_town` varchar(1) DEFAULT '',
`tehsilname` varchar(50) DEFAULT '',
`districtname` varchar(50) DEFAULT '',
`statename` varchar(50) DEFAULT '',
`state_country` varchar(20) DEFAULT '',
`countrycode` varchar(3) DEFAULT '',
`countryname` varchar(50) DEFAULT '',
`addressline1` varchar(50) NOT NULL DEFAULT '',
`addressline2` varchar(50) NOT NULL DEFAULT '',
`addressline3` varchar(50) NOT NULL DEFAULT '',
`addressline4` varchar(50) NOT NULL DEFAULT '',
`addressline5` varchar(50) NOT NULL DEFAULT '',
`birth_pin` varchar(6) NOT NULL DEFAULT '0',
`respin` varchar(6) NOT NULL DEFAULT '0',
`paddressline1` varchar(50) NOT NULL DEFAULT '',
`paddressline2` varchar(50) NOT NULL DEFAULT '',
`paddressline3` varchar(50) NOT NULL DEFAULT '',
`paddressline4` varchar(50) NOT NULL DEFAULT '',
`paddressline5` varchar(50) NOT NULL DEFAULT '',
`respinp` varchar(6) NOT NULL DEFAULT '0',
`durationinyear` varchar(10) DEFAULT '0',
`residentid` varchar(3) NOT NULL DEFAULT '0',
`date_data_entered` date DEFAULT NULL,
`tin` varchar(27) NOT NULL DEFAULT '',
`fingerimage1` blob,
`fingerimage2` blob,
`fingerimage3` blob,
`fingerimage4` blob,
`fingerimage5` blob,
`fingerimage6` blob,
`fingerimage7` blob,
`fingerimage8` blob,
`fingerimage9` blob,
`fingerimage10` blob,
`fingerid1` varchar(2) DEFAULT NULL,
`fingerid2` varchar(2) DEFAULT NULL,
`fingerid3` varchar(2) DEFAULT NULL,
`fingerid4` varchar(2) DEFAULT NULL,
`fingerid5` varchar(2) DEFAULT NULL,
`fingerid6` varchar(2) DEFAULT NULL,
`fingerid7` varchar(2) DEFAULT NULL,
`fingerid8` varchar(2) DEFAULT NULL,
`fingerid9` varchar(2) DEFAULT NULL,
`fingerid10` varchar(2) DEFAULT NULL,
`bir` mediumblob,
`photo` mediumblob,
`capture_status` varchar(10) DEFAULT NULL,
`user_id` varchar(10) DEFAULT NULL,
`campround` varchar(10) DEFAULT NULL,
`age` varchar(10) DEFAULT NULL,
`flag1` varchar(10) DEFAULT NULL,
`flag2` varchar(10) DEFAULT NULL,
`flag3` varchar(10) DEFAULT NULL,
`flag4` varchar(10) DEFAULT NULL,
`flag5` varchar(10) DEFAULT NULL,
`flag6` varchar(10) DEFAULT NULL,
`flag7` varchar(10) DEFAULT NULL,
`flag8` varchar(10) DEFAULT NULL,
`flag9` varchar(10) DEFAULT NULL,
`flag10` varchar(10) DEFAULT NULL,
`local_registrar` varchar(50) DEFAULT NULL,
`reason_not_captured` varchar(50) DEFAULT NULL,
`birth_country` varchar(50) DEFAULT NULL,
`slno_respondent` varchar(3) DEFAULT '0',
`genderid_enu` varchar(1) DEFAULT NULL,
`signature` blob,
`signedon` datetime DEFAULT NULL,
`signedby` varchar(25) DEFAULT NULL,
`verified` varchar(1) DEFAULT 'N',
`NIN` varchar(16) NOT NULL,
`NIN_ISSUE_DATE` date DEFAULT NULL,
`NIN_STATUS` decimal(1,0) DEFAULT '0',
`process_flag` varchar(1) DEFAULT 'N',
`signature_nin` blob,
`sign_after_NIN_date` date DEFAULT NULL,
`signedby_hsm` varchar(500) DEFAULT NULL,
`activity_code` varchar(1) DEFAULT NULL,
`dateofbiometricenrolled` datetime DEFAULT NULL,
`version` varchar(1) DEFAULT NULL,
`minutia1` blob,
`minutia2` blob,
`minutia3` blob,
`minutia4` blob,
`minutia5` blob,
`minutia6` blob,
`minutia7` blob,
`minutia8` blob,
`minutia9` blob,
`minutia10` blob,
`face_biometrics` longblob,
`fp_biometrics` longblob,
`mrz_finger_id1` varchar(2) DEFAULT NULL,
`mrz_finger_id2` varchar(2) DEFAULT NULL,
`mrz_fingerimage1` blob,
`mrz_fingerimage2` blob,
`timestampbio` datetime DEFAULT NULL,
`place_of_issue` varchar(50) DEFAULT NULL,
`mrz_valid_upto` date DEFAULT NULL,
`cardsn` varchar(10) DEFAULT NULL,
`batchno` varchar(10) DEFAULT NULL,
PRIMARY KEY (`tin`),
KEY `k_nin` (`NIN`),
KEY `k_cardsn` (`cardsn`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=75769
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show table status\G;
*************************** 1. row ***************************
Name: population_backup
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 313966
Avg_row_length: 157746
Data_length: 49526898692
Max_data_length: 281474976710655
Index_length: 8870912
Data_free: 0
Auto_increment: NULL
Create_time: 2012-01-17 15:16:16
Update_time: 2012-01-21 18:13:28
Check_time: 2012-01-17 18:27:10
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: avg_row_length=75769
Comment:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> show variables like '%buffer%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 49283072 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 104857600 |
| myisam_sort_buffer_size | 36700160 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 2097152 |
| read_rnd_buffer_size | 6291456 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.01 sec)
Kindly suggest what steps I should take, so that there is limited chance of crash.
Most of the time the table is queried only. As of now there is no query speed issue.
I am operating on windows 2008 server Datacenter and using RAM of size 16GB. Out of which around 8GB is taken by various applications.
Mysql version is 5.1.61-community-GPL