Re: Insert into table on duplicate key update - performance issue
mysql> show create table user_profile \G;
*************************** 1. row ***************************
Table: user_profile
Create Table: CREATE TABLE `user_profile` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`uuid` int(10) unsigned NOT NULL,
`gender` char(1) default NULL,
`age` char(3) default NULL,
`us_state` varchar(2) default NULL,
`zip` varchar(5) default NULL COMMENT 'Zip',
`country` varchar(2) default NULL COMMENT 'Country',
`ethnicity` varchar(1) default NULL,
`education` varchar(2) default NULL,
`marital_status` varchar(2) default NULL,
`score_clk` float default NULL,
`score_evc` float default NULL,
`friend_count` int(10) unsigned default NULL,
`comment_count` int(10) unsigned default NULL,
`first_seen` datetime default NULL COMMENT ,
`last_login` datetime default NULL COMMENT 'Date of most recent update',
`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP,
`segments` varchar(128) default NULL,
`is_processed` tinyint(1) default '0',
`score_buddy_count` int(4) default NULL,
`score_strong_ties` float default NULL,
`share_count` int(4) unsigned default NULL,
`score_msg_vol` float default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=239846658 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show table status like 'user_profile' \G;
*************************** 1. row ***************************
Name: user_profile
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 39794316
Avg_row_length: 54
Data_length: 2170552320
Max_data_length: 0
Index_length: 1269825536
Data_free: 0
Auto_increment: 239846658
Create_time: 2009-06-24 12:06:27
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 7168 kB
1 row in set (12.05 sec)
mysql> show variables like 'key%' \G;
*************************** 1. row ***************************
Variable_name: key_buffer_size
Value: 367001600
*************************** 2. row ***************************
Variable_name: key_cache_age_threshold
Value: 300
*************************** 3. row ***************************
Variable_name: key_cache_block_size
Value: 1024
*************************** 4. row ***************************
Variable_name: key_cache_division_limit
Value: 100
mysql> show variables like 'innodb%' \G;
*************************** 1. row ***************************
Variable_name: innodb_additional_mem_pool_size
Value: 16777216
*************************** 2. row ***************************
Variable_name: innodb_autoextend_increment
Value: 8
*************************** 3. row ***************************
Variable_name: innodb_buffer_pool_awe_mem_mb
Value: 0
*************************** 4. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 8589934592
*************************** 5. row ***************************
Variable_name: innodb_checksums
Value: ON
*************************** 6. row ***************************
Variable_name: innodb_commit_concurrency
Value: 0
*************************** 7. row ***************************
Variable_name: innodb_concurrency_tickets
Value: 500
*************************** 8. row ***************************
Variable_name: innodb_data_file_path
Value: ibdata:500M:autoextend
*************************** 9. row ***************************
Variable_name: innodb_data_home_dir
Value:
*************************** 10. row ***************************
Variable_name: innodb_doublewrite
Value: ON
*************************** 11. row ***************************
Variable_name: innodb_fast_shutdown
Value: 1
*************************** 12. row ***************************
Variable_name: innodb_file_io_threads
Value: 4
*************************** 13. row ***************************
Variable_name: innodb_file_per_table
Value: ON
*************************** 14. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
*************************** 15. row ***************************
Variable_name: innodb_flush_method
Value:
*************************** 16. row ***************************
Variable_name: innodb_force_recovery
Value: 0
*************************** 17. row ***************************
Variable_name: innodb_lock_wait_timeout
Value: 50
*************************** 18. row ***************************
Variable_name: innodb_locks_unsafe_for_binlog
Value: OFF
*************************** 19. row ***************************
Variable_name: innodb_log_arch_dir
Value:
*************************** 20. row ***************************
Variable_name: innodb_log_archive
Value: OFF
*************************** 21. row ***************************
Variable_name: innodb_log_buffer_size
Value: 8388608
*************************** 22. row ***************************
Variable_name: innodb_log_file_size
Value: 314572800
*************************** 23. row ***************************
Variable_name: innodb_log_files_in_group
Value: 2
*************************** 24. row ***************************
Variable_name: innodb_log_group_home_dir
Value: ./
*************************** 25. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 90
*************************** 26. row ***************************
Variable_name: innodb_max_purge_lag
Value: 0
*************************** 27. row ***************************
Variable_name: innodb_mirrored_log_groups
Value: 1
*************************** 28. row ***************************
Variable_name: innodb_open_files
Value: 300
*************************** 29. row ***************************
Variable_name: innodb_rollback_on_timeout
Value: OFF
*************************** 30. row ***************************
Variable_name: innodb_support_xa
Value: ON
*************************** 31. row ***************************
Variable_name: innodb_sync_spin_loops
Value: 20
*************************** 32. row ***************************
Variable_name: innodb_table_locks
Value: ON
*************************** 33. row ***************************
Variable_name: innodb_thread_concurrency
Value: 8
*************************** 34. row ***************************
Variable_name: innodb_thread_sleep_delay
Value: 10000
34 rows in set (0.00 sec)
I didn't quiet well understand, when you say batch the inserts. we do the insert into table on duplicate key update through a shell script