Re: INSERT/UPDATE within transactions (autocommit=0)
Posted by: Alexander Golubowitsch
Date: August 05, 2010 03:18AM
Date: August 05, 2010 03:18AM
Hi again,
my excuses for the late reply.
To answer a few of your questions:
We have made a lot of changes that pretty much solved the whole problem.
It seems that INSERT and general I/O load caused the problems.
- User Tracking:
We are using a table that stores information about each page request. INSERTs would regularly queue up at times. I have changed that to INSERT DELAYED, which helped great deal. The table is basically never read from, only written to.
- User Sessions:
We store Session data in a database table. The implementation causes one SELECT + 1 INSERT IGNORE + one UPDATE to that table for each request. That used to be done for any visitors, also spiders. Now we only do that for the small percentage of requests that are not from the top 5 spiders.
- Query Cache:
We have updated any queries we definitely do not want to be cached to read SELECT
SQL_NO_CACHE. DEMAND is a good point, but we'll have to wait a little before we tackle that.
As was to be expected, now there's a couple of new questions coming up. I'll leave them here even though they may get a little off-topic:
- Query Cache:
You mwentioned you're not a fan, I quite like it though. What puzzles me are the following investigations: It is set to 512MB - As long as it keeps filling up, all appears to be good. Once it hits its limit and starts removing queries to free space, Qcache_free_blocks goes up to some 60k, Qcache_free_memory goes up to about half of the 512MB, Qcache_queries_in_cache (was at ~400k) goes down again. If I issue a FLUSH QUERY CACHE, Qcache_free_blocks goes back to the former value of 1, and the cache starts filling again.
What's the point - just plain memory fragmentation?
Should I FLUSH QUERY CACHE once a day, or are the numbers just tricking me?
- Sleeping Clients / Aborted Connections:
Biggest problem right nowe - we are seeing sporadic PHP PDO Exceptions with the following message - google'ing for it didn't help much, haven't checked this forum, yet, though:
I have no idea where the problems are coming from, but the situation is pretty drastic. Can you give any hints?
PS:
my excuses for the late reply.
To answer a few of your questions:
The How big (GB) are all the tables combined? Perhaps your database is so small that it would all fit in the 3G you have now.~700 tables, about 16GB.
What is key_buffer_size? Probably 500M would be plenty.512MB.
We have made a lot of changes that pretty much solved the whole problem.
It seems that INSERT and general I/O load caused the problems.
- User Tracking:
We are using a table that stores information about each page request. INSERTs would regularly queue up at times. I have changed that to INSERT DELAYED, which helped great deal. The table is basically never read from, only written to.
mysql> SHOW VARIABLES LIKE "delayed%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | delayed_insert_limit | 100 | | delayed_insert_timeout | 90 | | delayed_queue_size | 200 | +------------------------+-------+
- User Sessions:
We store Session data in a database table. The implementation causes one SELECT + 1 INSERT IGNORE + one UPDATE to that table for each request. That used to be done for any visitors, also spiders. Now we only do that for the small percentage of requests that are not from the top 5 spiders.
- Query Cache:
We have updated any queries we definitely do not want to be cached to read SELECT
SQL_NO_CACHE. DEMAND is a good point, but we'll have to wait a little before we tackle that.
As was to be expected, now there's a couple of new questions coming up. I'll leave them here even though they may get a little off-topic:
- Query Cache:
You mwentioned you're not a fan, I quite like it though. What puzzles me are the following investigations: It is set to 512MB - As long as it keeps filling up, all appears to be good. Once it hits its limit and starts removing queries to free space, Qcache_free_blocks goes up to some 60k, Qcache_free_memory goes up to about half of the 512MB, Qcache_queries_in_cache (was at ~400k) goes down again. If I issue a FLUSH QUERY CACHE, Qcache_free_blocks goes back to the former value of 1, and the cache starts filling again.
What's the point - just plain memory fragmentation?
Should I FLUSH QUERY CACHE once a day, or are the numbers just tricking me?
- Sleeping Clients / Aborted Connections:
Biggest problem right nowe - we are seeing sporadic PHP PDO Exceptions with the following message - google'ing for it didn't help much, haven't checked this forum, yet, though:
SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'waiting for initial communication packet', system error: 95I see a dozen of these Exceptions thrown at visitors every hour or so. I have tried doubling max_connections to 512, but that didn't help.
max. concurrent connections 202 Failed attempts 1,766 Aborted 20 Total 2,130 k
mysql> SHOW VARIABLES LIKE "%connect%"; +--------------------------+-----------------+ | Variable_name | Value | +--------------------------+-----------------+ | connect_timeout | 30 | | init_connect | | | max_connect_errors | 10 | | max_connections | 512 | | max_user_connections | 0 | +--------------------------+-----------------+ mysql> SHOW VARIABLES LIKE "%timeout%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | connect_timeout | 30 | | delayed_insert_timeout | 90 | | innodb_lock_wait_timeout | 50 | | interactive_timeout | 28800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 28800 | +----------------------------+-------+
I have no idea where the problems are coming from, but the situation is pretty drastic. Can you give any hints?
PS:
mysql> SHOW VARIABLES; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 30 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 90 | | delayed_queue_size | 200 | | div_precision_increment | 4 | | keep_files_on_create | OFF | | engine_condition_pushdown | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 1 | | ft_query_expansion_limit | 20 | | group_concat_max_len | 4096 | | have_archive | NO | | have_bdb | NO | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv | NO | | have_dynamic_loading | YES | | have_example_engine | NO | | have_federated_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_merge_engine | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_ssl | DISABLED | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 25165824 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 3221225472 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_adaptive_hash_index | ON | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 0 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 67108864 | | innodb_log_file_size | 536870912 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 5000 | | innodb_rollback_on_timeout | OFF | | innodb_support_xa | OFF | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 536870912 | | 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_time_names | en_US | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 268435456 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 512 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 33554432 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | net_buffer_length | 8192 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 12266 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 1048576 | | read_only | OFF | | read_rnd_buffer_size | 6291456 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_load_tmpdir | /tmp/ | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slow_launch_time | 2 | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size | 4194304 | | sql_big_selects | ON | | sql_mode | | | sql_notes | ON | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | CEST | | table_cache | 6000 | | table_lock_wait_timeout | 50 | | table_type | MyISAM | | thread_cache_size | 48 | | thread_stack | 262144 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 33554432 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | updatable_views_with_limit | YES | | version | 5.0.60-log | | version_comment | Gentoo Linux mysql-5.0.60-r1 | | version_compile_machine | x86_64 | | version_compile_os | pc-linux-gnu | | wait_timeout | 28800 | +---------------------------------+--------------------------------------+ mysql> SHOW STATUS; +-----------------------------------+-------------+ | Variable_name | Value | +-----------------------------------+-------------+ | Aborted_clients | 22 | | Aborted_connects | 1781 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 21110 | | Bytes_sent | 397550 | | Com_admin_commands | 0 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_call_procedure | 0 | | Com_change_db | 0 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_create_user | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 0 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 1 | | Com_set_option | 2 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 0 | | Com_show_databases | 1 | | Com_show_errors | 0 | | Com_show_fields | 734 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_ndb_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1 | | Com_show_storage_engines | 0 | | Com_show_tables | 1 | | Com_show_triggers | 0 | | Com_show_variables | 6 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 0 | | Com_stmt_execute | 0 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 0 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 0 | | Com_update_multi | 0 | | 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 | OFF | | Connections | 2146576 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 117617 | | Created_tmp_tables | 9 | | Delayed_errors | 0 | | Delayed_insert_threads | 1 | | Delayed_writes | 1917457 | | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 1005 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 1129 | | Innodb_buffer_pool_pages_data | 152466 | | Innodb_buffer_pool_pages_dirty | 1 | | Innodb_buffer_pool_pages_flushed | 256219 | | Innodb_buffer_pool_pages_free | 1 | | Innodb_buffer_pool_pages_latched | 1 | | Innodb_buffer_pool_pages_misc | 44141 | | Innodb_buffer_pool_pages_total | 196608 | | Innodb_buffer_pool_read_ahead_rnd | 8637 | | Innodb_buffer_pool_read_ahead_seq | 10410 | | Innodb_buffer_pool_read_requests | 50044425340 | | Innodb_buffer_pool_reads | 534600 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 68853199 | | Innodb_data_fsyncs | 316457 | | Innodb_data_pending_fsyncs | 1 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 20360138752 | | Innodb_data_reads | 652060 | | Innodb_data_writes | 378021 | | Innodb_data_written | 11092307456 | | Innodb_dblwr_pages_written | 256219 | | Innodb_dblwr_writes | 24098 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 5425786 | | Innodb_log_writes | 228980 | | Innodb_os_log_fsyncs | 243342 | | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 2689167872 | | Innodb_page_size | 16384 | | Innodb_pages_created | 118352 | | Innodb_pages_read | 1242423 | | Innodb_pages_written | 256219 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 20883 | | Innodb_row_lock_time_avg | 10441 | | Innodb_row_lock_time_max | 16386 | | Innodb_row_lock_waits | 2 | | Innodb_rows_deleted | 14740 | | Innodb_rows_inserted | 11351904 | | Innodb_rows_read | 15173723255 | | Innodb_rows_updated | 300598 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 28215 | | Key_blocks_used | 428684 | | Key_read_requests | 1139162211 | | Key_reads | 1335992 | | Key_write_requests | 87513238 | | Key_writes | 4267065 | | Last_query_cost | 10.499000 | | Max_used_connections | 202 | | Not_flushed_delayed_rows | 0 | | Open_files | 262 | | Open_streams | 0 | | Open_tables | 3635 | | Opened_tables | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 60381 | | Qcache_free_memory | 198429408 | | Qcache_hits | 124848796 | | Qcache_inserts | 74927607 | | Qcache_lowmem_prunes | 72480227 | | Qcache_not_cached | 368353241 | | Qcache_queries_in_cache | 282692 | | Qcache_total_blocks | 626813 | | Questions | 584661739 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 9 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 0 | | Sort_scan | 0 | | 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 | 441419185 | | Table_locks_waited | 145341 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 28 | | Threads_connected | 21 | | Threads_created | 1808 | | Threads_running | 6 | | Uptime | 653205 | +-----------------------------------+-------------+ 248 rows in set (0.04 sec)
Subject
Views
Written By
Posted
2013
July 28, 2010 01:03PM
Re: INSERT/UPDATE within transactions (autocommit=0)
1791
August 05, 2010 03:18AM
1645
August 05, 2010 08:52AM
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.