Performance Issue on UPDATEs

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

We recently encountered performance issues on UPDATEs on MySQL 5.1 as well as Percona 5.1.

When using Lucid deb package provided from Percona average update query takes about 50ms to complete on a table with ~500K records.

I compiled Percona from source with following flags:

CFLAGS=“-O2 -DBIG_JOINS=1 -march=nocona -mfpmath=sse -msse2 -mmmx"CXXFLAGS=”-O2 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -

Our server is a Dual Xeon CPU 3.40GHz with 4GB of RAM. Ubuntu 10.04 is installed.

The compiled version takes average 25ms to complete update query.

Here is output of the profiling:

±--------------------------±---------+| Status | Duration |±--------------------------±---------+| starting | 0.000081 || checking permissions | 0.000010 || Opening tables | 0.000019 || System lock | 0.000006 || Table lock | 0.000007 || init | 0.000100 || Searching rows for update | 0.004254 || Updating | 0.066596 || end | 0.000013 || query end | 0.000005 || freeing items | 0.042667 || logging slow query | 0.000004 || cleaning up | 0.000006 |±--------------------------±---------+

Same query takes 1ms per update on my macpro workstation.
I created a VM with Parallels installed Ubuntu 10.04 with 4 cpus and 4g ram and average update is 2ms, Percona was installed from deb package.

On server turning off the query cache won’t increase the performance.

Please let me know how I can provide more information to troubleshoot this performance issue.

Thank you

Moe

2

I’m guessing that you are running InnoDB tables?

If so then how big is your InnoDB_buffer_size?
And what is your innodb_flush_logs_at_trx_commit setting?

Basically providing us with the output from:

SHOW VARIABLES LIKE ‘innodb%’;

is a good start.

3

Thank you for your reply.

Yes I’m running InnoDB.

The buffer size is set at 1G.

innodb_flush_log_at_trx_commit was set to 1. I Noticed no innodb_flush_method was shown in SHOW VARIABLES LIKE ‘innodb%’; I set it to O_DSYNC and average update dropped from 25ms to 6ms.

Changing innodb_flush_method to O_DIRECT didn’t make any changes.

Here is the output of the SHOW VARIABLES LIKE ‘innodb%’;

±---------------------------------------±-----------------------+| Variable_name | Value |±---------------------------------------±-----------------------+| innodb_adaptive_checkpoint | estimate || innodb_adaptive_flushing | OFF || innodb_adaptive_hash_index | ON || innodb_additional_mem_pool_size | 8388608 || innodb_auto_lru_dump | 0 || innodb_autoextend_increment | 8 || innodb_autoinc_lock_mode | 1 || innodb_buffer_pool_size | 1073741824 || innodb_change_buffering | inserts || innodb_checkpoint_age_target | 0 || innodb_checksums | ON || innodb_commit_concurrency | 0 || innodb_concurrency_tickets | 500 || innodb_data_file_path | ibdata1:10M:autoextend || innodb_data_home_dir | || innodb_dict_size_limit | 0 || innodb_doublewrite | ON || innodb_doublewrite_file | || innodb_enable_unsafe_group_commit | 0 || innodb_expand_import | 0 || innodb_extra_rsegments | 0 || innodb_extra_undoslots | OFF || innodb_fast_checksum | OFF || innodb_fast_recovery | OFF || innodb_fast_shutdown | 1 || innodb_file_format | Antelope || innodb_file_format_check | Barracuda || innodb_file_per_table | ON || innodb_flush_log_at_trx_commit | 1 || innodb_flush_log_at_trx_commit_session | 3 || innodb_flush_method | O_DSYNC || innodb_flush_neighbor_pages | 1 || innodb_force_recovery | 0 || innodb_ibuf_accel_rate | 100 || innodb_ibuf_active_contract | 1 || innodb_ibuf_max_size | 536854528 || innodb_io_capacity | 200 || innodb_lock_wait_timeout | 50 || innodb_locks_unsafe_for_binlog | OFF || innodb_log_buffer_size | 8388608 || innodb_log_file_size | 5242880 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | ./ || innodb_max_dirty_pages_pct | 75 || 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_overwrite_relay_log_info | OFF || innodb_page_size | 16384 || innodb_pass_corrupt_table | 0 || innodb_read_ahead | linear || innodb_read_ahead_threshold | 56 || innodb_read_io_threads | 4 || innodb_recovery_stats | OFF || innodb_replication_delay | 0 || innodb_rollback_on_timeout | OFF || innodb_show_locks_held | 10 || innodb_show_verbose_locks | 0 || innodb_spin_wait_delay | 6 || innodb_stats_auto_update | 1 || innodb_stats_method | nulls_equal || innodb_stats_on_metadata | ON || innodb_stats_sample_pages | 8 || innodb_stats_update_need_lock | 1 || innodb_strict_mode | OFF || innodb_support_xa | ON || innodb_sync_spin_loops | 30 || innodb_table_locks | ON || innodb_thread_concurrency | 0 || innodb_thread_concurrency_timer_based | OFF || innodb_thread_sleep_delay | 10000 || innodb_use_purge_thread | 1 || innodb_use_sys_malloc | ON || innodb_use_sys_stats_table | OFF || innodb_version | 1.0.12-11.5 || innodb_write_io_threads | 4 |±---------------------------------------±-----------------------+

Thank you very much for your help.

Moe

4

If you are willing to trade a bit of robustness for speed you can set:

innodb_flush_log_at_trx_commit = 2

Or you can buy a better RAID controller with a battery backed up cache.

The reason why you had better performance on your workstation is probably due to that some OS fake the sync even though the hardware havn’t actually had time to write it to disk yet.

And you should increase the log_buffer to something like:

innodb_log_file_size = 32M

The default log buffer size that you have now is very small, but you shouldn’t go higher than what I suggest since it will not give you so much benefit while the restore/repair time in case of accidental shutdown will instead increase a lot.

5

Do not compare a server’s speed with a Mac OS laptop that lies about whether your data has actually been written to disk.

6

I do appreciate for your responses.

Thank you very much.

Moe