MySQL Forums
Forum List  »  Partitioning

Suggestion for optimization - reg
Posted by: suvendu mohapatra
Date: January 23, 2012 05:09AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Suggestion for optimization - reg
3240
January 23, 2012 05:09AM
1846
January 24, 2012 02:18AM
1691
January 25, 2012 09:09PM
1597
January 26, 2012 02:48PM
1758
January 27, 2012 08:41PM


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.