Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 mins on the int and test environments but can take up to 90mins on the staging and production envs (occasionally it achieves 10 mins but usually 90).

They are identical schemas and datasets, the only difference is that stage and production have master/slave setups (int and test just the one DB) where the write master is taken out of the pool so there is no load on it (there is no load on stage anyway). The stage and production servers are also much higher spec'd machines.

After reviewing the documentation I can't find any leads as to why this might be.

Any ideas where I might look for the answer?

Someone asked for the servers configurations (The production box is a dedicated 24 core machine) :

| innodb_adaptive_flushing                          | ON                                                                                                                     |
| innodb_adaptive_hash_index                        | ON                                                                                                                     |
| innodb_additional_mem_pool_size                   | 20971520                                                                                                               |
| innodb_autoextend_increment                       | 8                                                                                                                      |
| innodb_autoinc_lock_mode                          | 1                                                                                                                      |
| innodb_buffer_pool_instances                      | 1                                                                                                                      |
| innodb_buffer_pool_size                           | 17179869184                                                                                                            |
| innodb_change_buffering                           | all                                                                                                                    |
| innodb_checksums                                  | ON                                                                                                                     |
| innodb_commit_concurrency                         | 0                                                                                                                      |
| innodb_concurrency_tickets                        | 500                                                                                                                    |
| innodb_data_file_path                             | ibdata1:10M:autoextend                                                                                                 |
| innodb_data_home_dir                              |                                                                                                                        |
| innodb_doublewrite                                | ON                                                                                                                     |
| innodb_fast_shutdown                              | 1                                                                                                                      |
| innodb_file_format                                | Barracuda                                                                                                              |
| innodb_file_format_check                          | ON                                                                                                                     |
| innodb_file_format_max                            | Antelope                                                                                                               |
| innodb_file_per_table                             | ON                                                                                                                     |
| innodb_flush_log_at_trx_commit                    | 2                                                                                                                      |
| innodb_flush_method                               |                                                                                                                        |
| innodb_force_recovery                             | 0                                                                                                                      |
| innodb_io_capacity                                | 400                                                                                                                    |
| innodb_large_prefix                               | OFF                                                                                                                    |
| innodb_lock_wait_timeout                          | 50                                                                                                                     |
| innodb_locks_unsafe_for_binlog                    | OFF                                                                                                                    |
| innodb_log_buffer_size                            | 8388608                                                                                                                |
| innodb_log_file_size                              | 262144000                                                                                                              |
| innodb_log_files_in_group                         | 2                                                                                                                      |
| innodb_log_group_home_dir                         | /sqllogs1/mysql-3306/innodb_logs                                                                                       |
| innodb_max_dirty_pages_pct                        | 50                                                                                                                     |
| innodb_max_purge_lag                              | 0                                                                                                                      |
| innodb_mirrored_log_groups                        | 1                                                                                                                      |
| innodb_old_blocks_pct                             | 37                                                                                                                     |
| innodb_old_blocks_time                            | 0                                                                                                                      |
| innodb_open_files                                 | 300                                                                                                                    |
| innodb_purge_batch_size                           | 20                                                                                                                     |
| innodb_purge_threads                              | 0                                                                                                                      |
| innodb_read_ahead_threshold                       | 56                                                                                                                     |
| innodb_read_io_threads                            | 10                                                                                                                     |
| innodb_replication_delay                          | 0                                                                                                                      |
| innodb_rollback_on_timeout                        | ON                                                                                                                     |
| innodb_rollback_segments                          | 128                                                                                                                    |
| innodb_spin_wait_delay                            | 6                                                                                                                      |
| innodb_stats_method                               | nulls_equal                                                                                                            |
| innodb_stats_on_metadata                          | ON                                                                                                                     |
| innodb_stats_sample_pages                         | 8                                                                                                                      |
| innodb_strict_mode                                | ON                                                                                                                     |
| innodb_support_xa                                 | ON                                                                                                                     |
| innodb_sync_spin_loops                            | 30                                                                                                                     |
| innodb_table_locks                                | ON                                                                                                                     |
| innodb_thread_concurrency                         | 0                                                                                                                      |
| innodb_thread_sleep_delay                         | 10000                                                                                                                  |
| innodb_use_native_aio                             | ON                                                                                                                     |
| innodb_use_sys_malloc                             | ON                                                                                                                     |
| innodb_version                                    | 1.1.8                                                                                                                  |
| innodb_write_io_threads                           | 10  
share|improve this question
1  
Is this with equal load too? –  Marcus Adams Jun 22 '12 at 19:50
    
@FahimParkar, Are you saying apps programmers shouldn't do inserts? Or that if they don't understand their performance, then they must ask their DBA? –  TerryE Jun 23 '12 at 10:11
    
InnoDB or MyISAM? Also, please post your my.cnf contents and the specs of the machine. –  Aaron Brown Jun 23 '12 at 18:43
1  
since Hibernate is not known for writing the best performing SQL, perhaps you should look at the SQl it is generating and optimize it. And are you doing these inserts/updates one record at a time or in sets? Sets are generally quicker, but with the volume you have, you might want to use batches. –  HLGEM Jul 26 '12 at 21:27
    
just out of curiosity, what is an "INT" setup? I'm familiar with DEV, TEST, UAT, PROD, etc, but have never heard of INT. –  Max Vernon Sep 11 '12 at 3:55
add comment

migrated from stackoverflow.com Jun 23 '12 at 17:17

This question came from our site for professional and enthusiast programmers.

1 Answer

FahimParkar is partly correct in that the exact reasons for this are perhaps the domain of the DBA, but you should be aware of the general performance issues around D/B replication. In essence this is giving the production environment a level of resilience and for some configurations performance scaling, but at a price. And that price is that insert/updates to one database must be correctly journalled and replicated to any slaves. This involve both increased logging and a significant remote RPC overhead between the master and slave machines. This is both volume and transaction related. Whilst there are techniques to optimise insert intensive batch processes, you have less freedom with update intensive batch processes and these should should be expected to experience a material performance hit.

You mention that the stage and production servers are higher spec'd machines, but the perhaps a key issue here is the network architecture and fabric for their interconnection.

From an applications viewpoint, perhaps all you can do is to review your design and ensure that you are only updating when actual change have occurred, or consider why this is a batch function.

Also there is a so-what? Q: It takes 90 mins. So what? It's a batch process; what is the downside of this?

share|improve this answer
    
Thanks for this. Do I understand correctly that the write-master will take a hit if it has replication switched on? Is it worth testing if this is the problem by switching on replication on the test environment or have I misunderstood you? Regarding "so what" about the 90 mins: the data needs to be available to clients as 'soon as', 90 mins is causing quality of service issues. –  TGW Jun 26 '12 at 18:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.