[MYSQL] grote insert/delete beinvloed alle db's

[styno]

Situatie:
OS: Ubuntu Server 12.04.3 LTS (x86_64)
CPU: 2x Xeon X5620 (8 cores, 16 threads)
RAM: 32 GB
Storage: 6x SSD Intel, hardware RAID-5, geen TRIM

DB: MySQL 5.5.31 (1 instantie) met master/slave replicatie

In de MySQL instantie zitten zo'n 150 databases met elk zo'n 50 tabellen die overwegend van het myisam type zijn. Per database zijn er 4 merge tabellen met ~20 onderliggende tabellen waarin meerdere gigabytes aan data zit. Er worden concurrent selects, updates en inserts gedaan op alle databases met zo'n 500 connecties. CPU gebruik varieert normaal tussen 200 en 300% (van de 1600%) en de schrijfactiviteit is zo'n 2 MB/s met uitschieters naar 20 MB/s.

Probleem: wanneer voor onderhoud één hele grote insert of delete van een paar miljoen records in één myisam (merge) tabel gebeurt vertragen ook andere databases in dezelfde MySQL instantie gigantisch, zodanig dat er allemaal queries open blijven in verschillende standen van 'waiting for table lock' tot 'sending data' en de bijbehorende applicaties lijken te hangen.

We krijgen maar geen gevoel van waar nu de bottleneck zit. Uit o.a. Munin/top/iotop etc. lijkt de hardware nauwelijks belast. CPU gebruik komt niet boven de 500% (van de beschikbare 1600%) uit en disk-utilization nauwelijks boven de 10%. Het lijkt wel alsof er ergens een soort van global lock is maar uren zoeken naar informatie levert maar weinig op tot dusver. Misschien dat een MySQL guru een tip heeft?

Globale status variabelen:

Aborted_clients	23560
Aborted_connects	3790624
Binlog_cache_disk_use	1
Binlog_cache_use	731357939
Binlog_stmt_cache_disk_use	0
Binlog_stmt_cache_use	1918221567
Bytes_received	4201506597427
Bytes_sent	10056106128918
Com_admin_commands	148624399
Com_assign_to_keycache	0
Com_alter_db	2
Com_alter_db_upgrade	0
Com_alter_event	0
Com_alter_function	0
Com_alter_procedure	0
Com_alter_server	0
Com_alter_table	3146
Com_alter_tablespace	0
Com_analyze	1
Com_begin	2136913
Com_binlog	0
Com_call_procedure	0
Com_change_db	921048559
Com_change_master	0
Com_check	1
Com_checksum	0
Com_commit	2137487
Com_create_db	20
Com_create_event	0
Com_create_function	0
Com_create_index	0
Com_create_procedure	3
Com_create_server	0
Com_create_table	4010
Com_create_trigger	47
Com_create_udf	0
Com_create_user	0
Com_create_view	381
Com_dealloc_sql	16421510
Com_delete	235894451
Com_delete_multi	0
Com_do	0
Com_drop_db	7
Com_drop_event	0
Com_drop_function	0
Com_drop_index	0
Com_drop_procedure	6
Com_drop_server	0
Com_drop_table	1998
Com_drop_trigger	41
Com_drop_user	0
Com_drop_view	1
Com_empty_query	0
Com_execute_sql	108956733
Com_flush	151
Com_grant	9
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	924
Com_insert	1626905233
Com_insert_select	69977
Com_install_plugin	0
Com_kill	285
Com_load	0
Com_lock_tables	184
Com_optimize	25
Com_preload_keys	0
Com_prepare_sql	34393528
Com_purge	110
Com_purge_before_date	0
Com_release_savepoint	0
Com_rename_table	7
Com_rename_user	0
Com_repair	16
Com_replace	209959745
Com_replace_select	9293
Com_reset	0
Com_resignal	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	114
Com_rollback_to_savepoint	0
Com_savepoint	0
Com_select	2824508061
Com_set_option	415019757
Com_signal	0
Com_show_authors	0
Com_show_binlog_events	32
Com_show_binlogs	21846
Com_show_charsets	11660
Com_show_collations	11684
Com_show_contributors	0
Com_show_create_db	257
Com_show_create_event	0
Com_show_create_func	0
Com_show_create_proc	0
Com_show_create_table	9682039
Com_show_create_trigger	0
Com_show_databases	13791
Com_show_engine_logs	0
Com_show_engine_mutex	0
Com_show_engine_status	20206
Com_show_events	66
Com_show_errors	0
Com_show_fields	113466111
Com_show_function_status	442
Com_show_grants	4717
Com_show_keys	26285
Com_show_master_status	8593
Com_show_open_tables	408
Com_show_plugins	150
Com_show_privileges	0
Com_show_procedure_status	442
Com_show_processlist	1248
Com_show_profile	0
Com_show_profiles	0
Com_show_relaylog_events	0
Com_show_slave_hosts	8
Com_show_slave_status	45254
Com_show_status	110104
Com_show_storage_engines	83
Com_show_table_status	21285452
Com_show_tables	46167269
Com_show_triggers	21129795
Com_show_variables	68857
Com_show_warnings	1740
Com_slave_start	1
Com_slave_stop	0
Com_stmt_close	16429152
Com_stmt_execute	108964433
Com_stmt_fetch	0
Com_stmt_prepare	34401221
Com_stmt_reprepare	60
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	75
Com_uninstall_plugin	0
Com_unlock_tables	184
Com_update	704661623
Com_update_multi	3
Com_xa_commit	0
Com_xa_end	0
Com_xa_prepare	0
Com_xa_recover	0
Com_xa_rollback	0
Com_xa_start	0
Compression	ON
Connections	75500470
Created_tmp_disk_tables	134712889
Created_tmp_files	512
Created_tmp_tables	237307920
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	3948536
Flush_commands	20
Handler_commit	5498668569
Handler_delete	1686266767
Handler_discover	0
Handler_prepare	1397810522
Handler_read_first	2648631657
Handler_read_key	718801292800
Handler_read_last	103903
Handler_read_next	2321299428692
Handler_read_prev	414989269
Handler_read_rnd	2328188793
Handler_read_rnd_next	655123849822
Handler_rollback	47683517
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	5356528656
Handler_write	24589814483
Innodb_buffer_pool_pages_data	8116
Innodb_buffer_pool_bytes_data	132972544
Innodb_buffer_pool_pages_dirty	409
Innodb_buffer_pool_bytes_dirty	6701056
Innodb_buffer_pool_pages_flushed	84187184
Innodb_buffer_pool_pages_free	3
Innodb_buffer_pool_pages_misc	73
Innodb_buffer_pool_pages_total	8192
Innodb_buffer_pool_read_ahead_rnd	0
Innodb_buffer_pool_read_ahead	62922657
Innodb_buffer_pool_read_ahead_evicted	8419149
Innodb_buffer_pool_read_requests	1242884663924
Innodb_buffer_pool_reads	892842391
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	5346913816
Innodb_data_fsyncs	1404330839
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	15674187976704
Innodb_data_reads	896017765
Innodb_data_writes	1435654990
Innodb_data_written	3784076752896
Innodb_dblwr_pages_written	84187184
Innodb_dblwr_writes	6030605
Innodb_have_atomic_builtins	ON
Innodb_log_waits	0
Innodb_log_write_requests	620741395
Innodb_log_writes	1388375889
Innodb_os_log_fsyncs	1389319829
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	1024934576128
Innodb_page_size	16384
Innodb_pages_created	7541814
Innodb_pages_read	956676423
Innodb_pages_written	84187184
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	177849376
Innodb_row_lock_time_avg	84
Innodb_row_lock_time_max	51985
Innodb_row_lock_waits	2099389
Innodb_rows_deleted	1207980
Innodb_rows_inserted	67036769
Innodb_rows_read	1415992390542
Innodb_rows_updated	684761741
Innodb_truncated_status_writes	0
Key_blocks_not_flushed	0
Key_blocks_unused	3264923
Key_blocks_used	3429472
Key_read_requests	734608775025
Key_reads	160986623
Key_write_requests	13227418349
Key_writes	4826974724
Last_query_cost	0
Max_used_connections	2175
Not_flushed_delayed_rows	0
Open_files	4242
Open_streams	0
Open_table_definitions	7439
Open_tables	7900
Opened_files	1745104620
Opened_table_definitions	256748
Opened_tables	499109
Performance_schema_cond_classes_lost	0
Performance_schema_cond_instances_lost	0
Performance_schema_file_classes_lost	0
Performance_schema_file_handles_lost	0
Performance_schema_file_instances_lost	0
Performance_schema_locker_lost	0
Performance_schema_mutex_classes_lost	0
Performance_schema_mutex_instances_lost	0
Performance_schema_rwlock_classes_lost	0
Performance_schema_rwlock_instances_lost	0
Performance_schema_table_handles_lost	0
Performance_schema_table_instances_lost	0
Performance_schema_thread_classes_lost	0
Performance_schema_thread_instances_lost	0
Prepared_stmt_count	0
Qcache_free_blocks	2033
Qcache_free_memory	3317024
Qcache_hits	1894123413
Qcache_inserts	423712095
Qcache_lowmem_prunes	329061851
Qcache_not_cached	2397690444
Qcache_queries_in_cache	4630
Qcache_total_blocks	12799
Queries	9222599274
Questions	9172445340
Rpl_status	AUTH_MASTER
Select_full_join	896755209
Select_full_range_join	53636
Select_range	319353723
Select_range_check	121879
Select_scan	2083343013
Slave_heartbeat_period	0
Slave_open_temp_tables	0
Slave_received_heartbeats	0
Slave_retried_transactions	0
Slave_running	ON
Slow_launch_threads	0
Slow_queries	1441472
Sort_merge_passes	15
Sort_range	13596141
Sort_rows	3003884039
Sort_scan	53624730
Ssl_accept_renegotiates	0
Ssl_accepts	0
Ssl_callback_cache_hits	0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects	0
Ssl_connect_renegotiates	0
Ssl_ctx_verify_depth	0
Ssl_ctx_verify_mode	0
Ssl_default_timeout	0
Ssl_finished_accepts	0
Ssl_finished_connects	0
Ssl_session_cache_hits	0
Ssl_session_cache_misses	0
Ssl_session_cache_mode	NONE
Ssl_session_cache_overflows	0
Ssl_session_cache_size	0
Ssl_session_cache_timeouts	0
Ssl_sessions_reused	0
Ssl_used_session_cache_entries	0
Ssl_verify_depth	0
Ssl_verify_mode	0
Ssl_version
Table_locks_immediate	51443087903
Table_locks_waited	19219107
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	159
Threads_cached	4
Threads_connected	460
Threads_created	15269337
Threads_running	3
Uptime	9665143
Uptime_since_flush_status	9665143

Globale variabelen:

auto_increment_increment	2
auto_increment_offset	1
autocommit	ON
automatic_sp_privileges	ON
back_log	50
basedir	/usr
big_tables	OFF
binlog_cache_size	32768
binlog_direct_non_transactional_updates	OFF
binlog_format	STATEMENT
binlog_stmt_cache_size	32768
bulk_insert_buffer_size	1073741824
character_set_client	latin1
character_set_connection	latin1
character_set_database	latin1
character_set_filesystem	binary
character_set_results	latin1
character_set_server	latin1
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/
collation_connection	latin1_swedish_ci
collation_database	latin1_swedish_ci
collation_server	latin1_swedish_ci
completion_type	NO_CHAIN
concurrent_insert	AUTO
connect_timeout	10
datadir	/var/lib/mysql/
date_format	%Y-%m-%d
datetime_format	%Y-%m-%d %H:%i:%s
default_storage_engine	InnoDB
default_week_format	0
delay_key_write	ON
delayed_insert_limit	100
delayed_insert_timeout	300
delayed_queue_size	1000
div_precision_increment	4
engine_condition_pushdown	ON
event_scheduler	OFF
expire_logs_days	30
flush	OFF
flush_time	0
foreign_key_checks	ON
ft_boolean_syntax	`+ -><()~*:""&|
ft_max_word_len	84
ft_min_word_len	4
ft_query_expansion_limit	20
ft_stopword_file	(built-in)
general_log	OFF
general_log_file	/var/lib/mysql/mysql-master.log
group_concat_max_len	1024
have_compress	YES
have_crypt	YES
have_csv	YES
have_dynamic_loading	YES
have_geometry	YES
have_innodb	YES
have_ndbcluster	NO
have_openssl	DISABLED
have_partitioning	YES
have_profiling	YES
have_query_cache	YES
have_rtree_keys	YES
have_ssl	DISABLED
have_symlink	YES
hostname	mysql-master
ignore_builtin_innodb	OFF
init_connect
init_file
init_slave
innodb_adaptive_flushing	ON
innodb_adaptive_hash_index	ON
innodb_additional_mem_pool_size	8388608
innodb_autoextend_increment	8
innodb_autoinc_lock_mode	1
innodb_buffer_pool_instances	1
innodb_buffer_pool_size	134217728
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	Antelope
innodb_file_format_check	ON
innodb_file_format_max	Antelope
innodb_file_per_table	ON
innodb_flush_log_at_trx_commit	1
innodb_flush_method
innodb_force_load_corrupted	OFF
innodb_force_recovery	0
innodb_io_capacity	200
innodb_large_prefix	OFF
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_print_all_deadlocks	OFF
innodb_purge_batch_size	20
innodb_purge_threads	0
innodb_random_read_ahead	OFF
innodb_read_ahead_threshold	56
innodb_read_io_threads	4
innodb_replication_delay	0
innodb_rollback_on_timeout	OFF
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	OFF
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	OFF
innodb_use_sys_malloc	ON
innodb_version	5.5.31
innodb_write_io_threads	4
interactive_timeout	28800
join_buffer_size	1073741824
keep_files_on_create	OFF
key_buffer_size	4294967296
key_cache_age_threshold	300
key_cache_block_size	1024
key_cache_division_limit	100
large_files_support	ON
large_page_size	0
large_pages	OFF
lc_messages	en_US
lc_messages_dir	/usr/share/mysql/
lc_time_names	en_US
license	GPL
local_infile	ON
lock_wait_timeout	31536000
locked_in_memory	OFF
log	OFF
log_bin	ON
log_bin_trust_function_creators	OFF
log_error	/var/log/mysql/error.log
log_output	FILE
log_queries_not_using_indexes	OFF
log_slave_updates	OFF
log_slow_queries	OFF
log_warnings	1
long_query_time	1
low_priority_updates	OFF
lower_case_file_system	OFF
lower_case_table_names	1
max_allowed_packet	16777216
max_binlog_cache_size	18446744073709500000
max_binlog_size	104857600
max_binlog_stmt_cache_size	18446744073709500000
max_connect_errors	10
max_connections	4000
max_delayed_threads	20
max_error_count	64
max_heap_table_size	4294967296
max_insert_delayed_threads	20
max_join_size	18446744073709500000
max_length_for_sort_data	1024
max_long_data_size	16777216
max_prepared_stmt_count	16382
max_relay_log_size	0
max_seeks_for_key	18446744073709500000
max_sort_length	1024
max_sp_recursion_depth	0
max_tmp_tables	32
max_user_connections	0
max_write_lock_count	18446744073709500000
metadata_locks_cache_size	1024
min_examined_row_limit	0
multi_range_count	256
myisam_data_pointer_size	6
myisam_max_sort_file_size	9223372036853720000
myisam_mmap_size	18446744073709500000
myisam_recover_options	BACKUP
myisam_repair_threads	1
myisam_sort_buffer_size	4294967296
myisam_stats_method	nulls_unequal
myisam_use_mmap	OFF
net_buffer_length	16384
net_read_timeout	30
net_retry_count	10
net_write_timeout	60
new	OFF
old	OFF
old_alter_table	OFF
old_passwords	OFF
open_files_limit	135078
optimizer_prune_level	1
optimizer_search_depth	62
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
performance_schema	OFF
performance_schema_events_waits_history_long_size	10000
performance_schema_events_waits_history_size	10
performance_schema_max_cond_classes	80
performance_schema_max_cond_instances	1000
performance_schema_max_file_classes	50
performance_schema_max_file_handles	32768
performance_schema_max_file_instances	10000
performance_schema_max_mutex_classes	200
performance_schema_max_mutex_instances	1000000
performance_schema_max_rwlock_classes	30
performance_schema_max_rwlock_instances	1000000
performance_schema_max_table_handles	100000
performance_schema_max_table_instances	50000
performance_schema_max_thread_classes	50
performance_schema_max_thread_instances	1000
pid_file	/var/run/mysqld/mysqld.pid
plugin_dir	/usr/lib/mysql/plugin/
port	3306
preload_buffer_size	32768
profiling	OFF
profiling_history_size	15
protocol_version	10
query_alloc_block_size	8192
query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	16777216
query_cache_type	ON
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192
range_alloc_block_size	4096
read_buffer_size	1073741824
read_only	OFF
read_rnd_buffer_size	268435456
relay_log	mysql-master-relay-bin
relay_log_index
relay_log_info_file	relay-log.info
relay_log_purge	ON
relay_log_recovery	OFF
relay_log_space_limit	0
report_host	mysql-master.[domain].nl
report_password
report_port	3306
report_user
rpl_recovery_rank	0
secure_auth	OFF
secure_file_priv
server_id	1
skip_external_locking	ON
skip_name_resolve	OFF
skip_networking	OFF
skip_show_database	OFF
slave_compressed_protocol	OFF
slave_exec_mode	STRICT
slave_load_tmpdir	/tmp
slave_max_allowed_packet	1073741824
slave_net_timeout	3600
slave_skip_errors	1062
slave_transaction_retries	10
slave_type_conversions
slow_launch_time	2
slow_query_log	OFF
slow_query_log_file	/var/lib/mysql/mysql-master-slow.log
socket	/var/run/mysqld/mysqld.sock
sort_buffer_size	1073741824
sql_auto_is_null	OFF
sql_big_selects	ON
sql_big_tables	OFF
sql_buffer_result	OFF
sql_log_bin	ON
sql_log_off	OFF
sql_low_priority_updates	OFF
sql_max_join_size	18446744073709500000
sql_mode
sql_notes	ON
sql_quote_show_create	ON
sql_safe_updates	OFF
sql_select_limit	18446744073709500000
sql_slave_skip_counter	0
sql_warnings	OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine	InnoDB
stored_program_cache	256
sync_binlog	0
sync_frm	ON
sync_master_info	0
sync_relay_log	0
sync_relay_log_info	0
system_time_zone	CEST
table_definition_cache	16384
table_open_cache	262136
thread_cache_size	8
thread_concurrency	10
thread_handling	one-thread-per-connection
thread_stack	196608
time_format	%H:%i:%s
time_zone	SYSTEM
timed_mutexes	OFF
tmp_table_size	4294967296
tmpdir	/tmp
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
tx_isolation	REPEATABLE-READ
unique_checks	ON
updatable_views_with_limit	YES
version	5.5.31-0ubuntu0.12.04.1-log
version_comment	(Ubuntu)
version_compile_machine	x86_64
version_compile_os	debian-linux-gnu
wait_timeout	28800

[Schnoop]

Ik heb persoonlijk geen ervaring met zulke grote databases maar wellicht dat dit artikel helpt.

So you understand how much having data in memory changed things here is small example with numbers. If you have your data fully in memory you could perform over 300.000 of random lookups per second from single thread depending on system and table structure. Now if you data fully on disk (both data and index) you would need 2+ IOs to retrieve the row which means you get about 100 rows/sec. Note multiple drives do not really help a lot as we’re speaking about single thread/query here. So difference is 3.000 times! It might be a bit too much as there are few completely uncached workloads but 100+ times difference is quite frequent.

Wat vaak het probleem is is dat die insert/update vanuit 1 thread gedaan wordt. Als je data dus niet in memory staat worden telkens een aantal rows van de schijf gehaald+behandeld. Hierbij krijg je overhead welke niet te traceren is naar CPU/IO.

Kijk dus even naar het memory gebruik. Wat wellicht ook zou kunnen helpen is bij een update de update verdelen over verschillende queries zodat er per query een thread ge spawned wordt. Dit zou in theory sneller moet werken als je data niet in memory staat (Althans dit haal ik uit her verhaal).

[styno]

Ik heb het artikel gelezen maar twijfel of het van toepassing is. Het is geen probleem als die ene insert/delete query traag is, wat mij stoort is dat de andere connecties/queries er problemen door krijgen. Volgens de instellingen zou dat niet moeten:

Status:
Threads_connected..... 463
Max_used_connections..... 585

Systeem variabele:
thread_handling: one-thread-per-connection

Als die queries werkelijk los van elkaar staan dan zou -simpel gedacht- de vertraging moeten komen omdat de I/O niet sneller kan maar dat zie ik niet terug in de logs waar de disk-utilization ver van de 100% blijft.

[Tukk]

Zonder enige MYSql kennis (wel veel oracle, sql server).

Voer je alle inserts in 1 keer achter elkaar uit? Stop er dan eens een paar commits in (bv om de 100.000) zodat het geheugen vrijgegeven kan worden.

[ Voor 4% gewijzigd door Tukk op 17-09-2013 16:01 ]

[styno]

Er zit geen commit na [x] records, dat zou bij een delete/update nog wel eens kunnen helpen inderdaad.

Maar ik heb op dit moment hetzelfde probleem met een ALTER TABLE waarbij ik een primary key toevoeg aan een table met een paar 300 miljoen records: alle databases in de instance zijn zo traag als dikke str*nt. En dat los je helaas niet op met een tussen-commit. Er staan nu zo'n 40 queries in verschillende stadia van afhandeling open waarvan sommige al bijna 500 seconden duren (en die normaal in een fractie van een seconde afgehandeld worden).

tabel:
x - int(4) PK
y - int(4) PK
stamp - datetime PK
value - float

Een eenvoudige benchmark van de storage geeft tegelijkertijd met de ALTER TABLE nog ruim 200 MB/s:

code:

time dd if=/dev/zero of=ddfile bs=8k count=500000 && sync
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 16.0199 s, 256 MB/s

real    0m16.974s
user    0m0.084s
sys     0m6.212s

iostat -x 10 geeft:

code:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          13.81   0.00  0.60    0.03     0.00     85.57

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   114.90    1.30  242.40    93.60  1654.80    14.35     0.05    0.20    0.62    0.19   0.04   0.92

[Piemol]

Ik denk dat Schnoop op zich wel een punt heeft.
MySql heeft maar een bepaald aantal Gb's aan geheugen beschikbaar.
Als die ene grote insert/delete query laten we zeggen 75% van het MySql geheugen op snoept,
hebben alle andere queries nog maar 25% van het geheugen beschikbaar.

Staan je myisam_sort_buffer_size en sort_buffer_size niet wat groot ingesteld?
(als het in bytes is uitgedrukt heb je 4 en 1Gb hiervoor?)
http://stackoverflow.com/...-size-vs-sort-buffer-size

Als de grote query dan voor 'wat' vertraging zorgt, en daardoor andere threads ook iets langer open blijven staan dan is binnen notime al je geheugen op.

Verder wist ik niet wat MERGE tables precies waren, dus heb ik even opgezocht in de documentatie.
En toen snapte ik niet wat dan precies voor- en nadelen zijn van zo'n tabel... (ja ik wist het echt niet wat het was!).
Toen las ik in de documentatie:
MERGE tables use more file descriptors than MyISAM tables. If 10 clients are using a MERGE table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)

En op een MySql forum:
A MERGE table doesn't need a fd for itself, but one or two for every MyISAM table. So the first session opening your MERGE table takes 1000 fds, every concurrent session on the same MERGE table takes just 500 additional fds. However only the MERGE table needs a TABLE structure in the table_cache. The underlying MyISAM tables don't appear in the table_cache unless they are used directly (not through the MERGE table).

Dus als ik het goed begrijp moet je ook genoeg open files toe staan.

hmmm... je open_files_limit staat erg hoog, en al zou je 500 connections hebben die 'toevallig' alle 150 databases de 4 MERGE tabellen querie'en met ~20 onderliggende tabbellen zou je op 40000 open files komen.
Dus dat 'lijkt' mij ook niet heel erg fout te gaan.
Dat je geen error krijgt over 'too many connections' zou alsnog kunnen, zolang er een wachtrij kan zijn treedt die niet op.


Draaien de queries/threads wel onder verschillende users?
Dat je niet alles vanuit dezelfde MySql user runt en die tegen een limiet aanloopt?

Voor nu zijn m'n ideeën even op

Ohnee: is de server een VM ?
Zoja, weet je zeker dat die de resources heeft die je verwacht dat die heeft?
Stel dat je de opgegeven specs denkt te hebben, en VMWare (of een vergelijkbaar programma) geeft hem maar 4 cores en 4GB geheugen, dan weet Linux daar helemaal niks van af.

[Wim-Bart]

Even er van uit gaande dat CPU niet de bottleneck is, Kunnen de SSD's dan niet de bottleneck zijn. Ik weet net wat de stripe size is, maar met zulke gigantische updates zijn je SSD's intern ontzettend met data aan het schuiven. Je zegt dat je raid 5 hebt, dan zijn 2 ssd's aan het schrijven. Omdat de raid stripe size altijd kleiner dan 4K is wil dat zeggen dat iedere schrijfactie bestaat uit DISKS x 4K lezen, 2 x 4K schrijven en omdat de Stripe geen 4K is, zullen dus 2 SSD's dus altijd 4K extra lezen en deze op een andere 4K Cell wegschrijven. Met als gevolg dat je IO ontzettend inzakt. Daarnaast is het te hopen dat je partitie alligned is zodat je geen double reads krijgt op je storage. Ook al is het SSD, de performance kan behoorlijk tegenvallen en bij schrijven zelfs onder traditionele HDD performance vallen. Daarnaast helpt het niet hebben van TRIM ook niet echt mee.

[Tukk]

Storage: 6x SSD Intel, hardware RAID-5, geen TRIM

Dit is ook niet de beste opstelling om performance te halen!

Data op een Raid-5 is logisch, aangezien je uptime hoog wil houden. Wat je niet moet doen is je OS en indexen op deze RAID-5 zetten. Een query die alleen je indexen gebruikt (of als je indexen gaat herbouwen) zit dan het data ophalen in de weg. Wat ik meestal doe zijn drie raids in 1 server:

Mirrored: OS (zekerheid + sneller lezen)
Striped: Indexen (snelheid, snelheid, snelheid)
Raid-5: Data. (zekerheid, dan maar een beetje langzamer).

Indien je CPU en Memory niet in de max zitten zou je de data ook nog kunnen verdelen over twee raid-5 systemen.

Snelste winst op dit moment: Haal de indexen van je datadisks af, desnoods prik je er 1 ssd bij en zet je ze daar op.

[styno]

Piemol schreef op dinsdag 17 september 2013 @ 19:40:
Ik denk dat Schnoop op zich wel een punt heeft.
MySql heeft maar een bepaald aantal Gb's aan geheugen beschikbaar.
Als die ene grote insert/delete query laten we zeggen 75% van het MySql geheugen op snoept,
hebben alle andere queries nog maar 25% van het geheugen beschikbaar.

Mogelijk, maar waar dan?

Staan je myisam_sort_buffer_size en sort_buffer_size niet wat groot ingesteld?
(als het in bytes is uitgedrukt heb je 4 en 1Gb hiervoor?)
http://stackoverflow.com/...-size-vs-sort-buffer-size

Deze buffers zijn al eens vergroot als reactie op deze problemen. Ik zal eens kijken of een veel kleinere sort_buffer_size impact heeft.

Als de grote query dan voor 'wat' vertraging zorgt, en daardoor andere threads ook iets langer open blijven staan dan is binnen notime al je geheugen op.

Mogelijk zie ik dat ook wel in Munin. Na het killen van de langlopende alter table heeft het systeem zo'n 6 GB usused RAM terwijl daarvoor al het ram voor cache gebruikt werd. Mijn gevoel zegt me dat 6GB gebruik voor zo'n zware query prima is, er is dan nog dik 20 GB beschikbaar voor de andere queries. Maar misschien spelen er andere zaken dan alleen de hoeveelheid geheugen maar ook bandbreedte en/of caches?

Verder wist ik niet wat MERGE tables precies waren, dus heb ik even opgezocht in de documentatie.
En toen snapte ik niet wat dan precies voor- en nadelen zijn van zo'n tabel... (ja ik wist het echt niet wat het was!).
Toen las ik in de documentatie:
MERGE tables use more file descriptors than MyISAM tables. If 10 clients are using a MERGE table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)

En op een MySql forum:
A MERGE table doesn't need a fd for itself, but one or two for every MyISAM table. So the first session opening your MERGE table takes 1000 fds, every concurrent session on the same MERGE table takes just 500 additional fds. However only the MERGE table needs a TABLE structure in the table_cache. The underlying MyISAM tables don't appear in the table_cache unless they are used directly (not through the MERGE table).

Dus als ik het goed begrijp moet je ook genoeg open files toe staan.

hmmm... je open_files_limit staat erg hoog, en al zou je 500 connections hebben die 'toevallig' alle 150 databases de 4 MERGE tabellen querie'en met ~20 onderliggende tabbellen zou je op 40000 open files komen.
Dus dat 'lijkt' mij ook niet heel erg fout te gaan.

De merge tables worden gebruikt om redelijk straffeloos een periode aan data te kunnen weggooien en het gebruik ervan stamt van voor de introductie van partitions in MySQL. Helaas moet je met merge tables nog steeds recente data moven/deleten en dat is waar een deel van de problemen ontstaan. Partitions zouden dat kunnen oplossen door MySQL direct al de data in de juiste tabel te laten plaatsen. Maar ook dat lost het onderliggende probleem in de kern niet op.

Filedescriptors hebben we inderdaad zat beschikbaar:)
Linux:

code:

cat /proc/sys/fs/file-max
3247301

en MySQL:

code:

open_files_limit    135078

Dat je geen error krijgt over 'too many connections' zou alsnog kunnen, zolang er een wachtrij kan zijn treedt die niet op.

In MySQL logging is niks interessants te vinden.

Draaien de queries/threads wel onder verschillende users? Dat je niet alles vanuit dezelfde MySql user runt en die tegen een limiet aanloopt?

Ja, de meeste wel en zeker de zware alter table.

is de server een VM ?
Zoja, weet je zeker dat die de resources heeft die je verwacht dat die heeft?
Stel dat je de opgegeven specs denkt te hebben, en VMWare (of een vergelijkbaar programma) geeft hem maar 4 cores en 4GB geheugen, dan weet Linux daar helemaal niks van af.

Nee, het is een (ondertussen wat oudere) dedicated machine voor de database.

[ Voor 6% gewijzigd door styno op 18-09-2013 09:58 ]

[styno]

Wim-Bart en Tukk: ik ben me bewust dat dit in deze tijden geen optimale setup is en voor een nieuwe database machine zouden inderdaad andere keuzes gemaakt worden (geen hardware raid-5 bijvoorbeeld). Maar, als de SSD's de bottleneck zouden zijn, is de benchmark dan nog steeds heel snel tijdens de zware alter table? Valt dat met elkaar te rijmen, goede sequentiele performance terwijl de storage tegelijkertijd qua iops zwaar belast wordt?

Een eenvoudige benchmark van de storage geeft tegelijkertijd met de ALTER TABLE nog ruim 200 MB/s:

code:

time dd if=/dev/zero of=ddfile bs=8k count=500000 && sync
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 16.0199 s, 256 MB/s

real    0m16.974s
user    0m0.084s
sys     0m6.212s

iostat -x 10 geeft:

code:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          13.81   0.00  0.60    0.03     0.00     85.57

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   114.90    1.30  242.40    93.60  1654.80    14.35     0.05    0.20    0.62    0.19   0.04   0.92

We zouden inderdaad de setup kunnen wijzigen om (cosmetisch) mogelijk het probleem op te lossen, maar ik wil graag weten waarom een relatieve simpele maar zware load de performance van de hele instance onderuit haalt terwijl CPU/storage er niet echt van onder de indruk lijken te zijn.

Edit I:
Kwam MySQLTuner tegen in één van de links en tijdens een zware operatie laten lopen. Dit is de output:

code:

 >>  MySQLTuner 1.0.1 - Major Hayden <[e-mail]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials passed on the command line

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-0ubuntu0.12.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 373G (Tables: 8215)
[--] Data in MRG_MYISAM tables: 335G (Tables: 229)
[--] Data in InnoDB tables: 5G (Tables: 7607)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 8)
[!!] Total fragmented tables: 661

-------- Performance Metrics -------------------------------------------------
[--] Up for: 112d 19h 3m 57s (9B q [950.127 qps], 76M conn, TX: 10161B, RX: 4238B)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 8.2G global + 3.3G per thread (4000 max threads)
[!!] Maximum possible memory usage: 13008.9G (41477% of installed RAM)
[OK] Slow queries: 0% (1M/9B)
[OK] Highest usage of available connections: 16% (658/4000)
[OK] Key buffer size / total MyISAM indexes: 4.0G/237.0G
[OK] Key buffer hit rate: 99.9% (6B cached / 3M reads)
[!!] Query cache efficiency: 0.7% (18M cached / 2B selects)
[!!] Query cache prunes per day: 29759
[OK] Sorts requiring temporary tables: 0% (15 temp sorts / 67M sorts)
[!!] Joins performed without indexes: 904290732
[!!] Temporary tables created on disk: 36% (136M on disk / 375M total)
[OK] Thread cache hit rate: 79% (15M created / 76M connections)
[!!] Table cache hit rate: 2% (14K open / 506K opened)
[OK] Open file limit used: 9% (12K/135K)
[OK] Table locks acquired immediately: 99% (561M immediate / 561M locks)
[!!] InnoDB data size / buffer pool: 5.7G/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 16M)
    join_buffer_size (> 1.0G, or always use indexes with joins)
    table_cache (> 524288)
    innodb_buffer_pool_size (>= 5G)

Ahem: Er lijkt nog wel wat ruimte voor optimalisatie

Edit II:
Zo'n tuning script is ook niet alles, de table cache instelling kan niet hoger dan 524288 en toch hebben we maar een hit-rate van 2%. De MySQL Tuning Blog heeft hier wat over te zeggen (is wel een ouder artikel, dus kan niet meer actueel zijn). Als je toch een lage hit-rate hebt die je niet kunt oplossen met een grotere cache dan kun je beter de cache kleiner maken...

[ Voor 66% gewijzigd door styno op 18-09-2013 10:52 ]

[Piemol]

Hallo Styno,

helaas voor je kan ik er zelf nogal slecht tegen als iets onopgelost blijft
Dus heb vanavond nog is zitten nadenken en googlen...
Kan Munin zelf het probleem niet zijn? Ik weet niet of je dat al geïnstalleerd had voor de traagheidsproblemen?
Om de hoeveel minuten verzamelt Munin statistieken, en hoe lang duurt 'de grote insert' ?

Zie dit topic
Zelf heb ik (achteraf gezien) ook ervaren dat Munin voor behoorlijke pieken (in de load) en vertragingen kan zorgen.

Wellicht zou je diverse monitoring programma's kunnen uitschakelen en kijken wat het met je queries / performance doet.
Al zou ik zeggen dat als een monitoring tool inderdaad voor problemen zorgt, je ook in de serverload véél meer zou moeten zien... maar goed bovengenoemd topic had daar ook geen aanknopingspunt bij.

[ Voor 3% gewijzigd door Piemol op 18-09-2013 21:01 ]

[styno]

Bedankt voor de vasthoudendheid Piemol

Piemol schreef op woensdag 18 september 2013 @ 21:01:
Hallo Styno,

helaas voor je kan ik er zelf nogal slecht tegen als iets onopgelost blijft
Dus heb vanavond nog is zitten nadenken en googlen...
Kan Munin zelf het probleem niet zijn? Ik weet niet of je dat al geïnstalleerd had voor de traagheidsproblemen?
Om de hoeveel minuten verzamelt Munin statistieken, en hoe lang duurt 'de grote insert' ?

De meeste Mysql Munin stats zijn pas toegevoegd na de problemen ontdekt waren. De 'grote insert' duurt zo 10-20 minuten.

Zie dit topic
Zelf heb ik (achteraf gezien) ook ervaren dat Munin voor behoorlijke pieken (in de load) en vertragingen kan zorgen.

Klopt, maar dat wordt al deels ondervangen met een aparte VM waar de Munin in draait. Ook zie ik Munin-node niet veel in top voorbij komen.

Wellicht zou je diverse monitoring programma's kunnen uitschakelen en kijken wat het met je queries / performance doet.
Al zou ik zeggen dat als een monitoring tool inderdaad voor problemen zorgt, je ook in de serverload véél meer zou moeten zien... maar goed bovengenoemd topic had daar ook geen aanknopingspunt bij.

Dat zou een optie zijn, maarrrr...

De oorzaak lijkt gevonden te zijn in de key-cache van MySQL. Als we nu onderhoud gaan doen dan maken we eerst een aparte key-cache aan voor de indexen die we verwachten te gaan gebruiken en zetten na afloop de aparte key-cache grootte weer op 0. Daarmee lijkt het blokkeren van de hele instance opgelost.

Het lijkt dus een gevalletje waarbij mutexen in de code van MySQL te grof zijn (teveel code binnen één mutex) waardoor threads op elkaar moeten wachten om toegang te krijgen tot MySQL functionaliteit.

[Piemol]

Oké, nooit van iets dergelijks gehoord.
Kun je dan ook aan je onderhouds-thread aangeven dat die de aparte key-cache moet gaan gebruiken of iets dergelijks?
Of gaat MySql zelf kijken of een key-cache uitleesbaar is of niet, zodat alle andere threads automatisch de key-cache gaan gebruiken die niet gebruikt wordt door de onderhoudsthread?

[styno]

Piemol schreef op vrijdag 20 september 2013 @ 14:42:
Oké, nooit van iets dergelijks gehoord.
Kun je dan ook aan je onderhouds-thread aangeven dat die de aparte key-cache moet gaan gebruiken of iets dergelijks?
Of gaat MySql zelf kijken of een key-cache uitleesbaar is of niet, zodat alle andere threads automatisch de key-cache gaan gebruiken die niet gebruikt wordt door de onderhoudsthread?

Het is eigenlijk voor een database ook te gek voor woorden, maar ja, MySQL is in de kern een simpele database uit de single-core tijdperk. Handmatig indexen in verschillende caches plaatsen is natuurlijk een enorme en ondertussen doen andere DB's het beter, mogelijk MariaDB ook als drop-in vervanger. Daar keken we toch al naar.

Je zult zelf indexen handmatig aan de niet-default key-cache moeten toevoegen dus MySQL gaat die niet 'zomaar' gebruiken, maar alleen voor queries die de betrokken indexen betreft. Dat is voor onze situatie gelukkig geen probleem. Het aanmaken van een aparte key-cache en het opruimen daarvan wordt opgenomen in het onderhoudsscript, dat is gelukkig maar een kleine wijziging.

Overigens, dank aan allen die hun input gaven!

[ Voor 4% gewijzigd door styno op 20-09-2013 15:08 ]