Hi,
We have a moodle site with 60-100 concurrent connections. Sometime, user get error message "Database connection failed ..." but it's ok after F5. In my installation: I have $CFG->dbpersist = false; in config.php and also increate some parameter for mysql for example max_connections, key_buffer_sort, table_cache,.. as some article wrote but still gots problem and sometime database is down automatically. Please give me your advices to stop that situation?.
By the way, I am deploying my site with mysql and apache in the same machine Xeon 3G core dual, RAM = 2G, ... Any advice for hardware improvement to speedup my site?
site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Number of replies: 30
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Iñaki Arenaza -
Make sure the MaxClients value in your Apache settings is not higher than the max_connections value in MySQL.
Saludos. Iñaki.
Saludos. Iñaki.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Myles Carrick -
Do you really have 60-100 concurrent users on a box with 2GB?
What's your MaxClients setting in your apache conf?
It's certain to be RAM that is a significant bottleneck for you...
Cheers,
MC
What's your MaxClients setting in your apache conf?
It's certain to be RAM that is a significant bottleneck for you...
Cheers,
MC
In reply to Myles Carrick
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thanks for replying quickly. Here is my httpd.conf, I see MaxClients =256
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 120
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive Off
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 15
##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 256
MaxClients 256
MaxRequestsPerChild 4000
</IfModule>
And when I use mysql> show variables and I take a look at max_connections parameter and see current value:
| max_connections | 1000
sometime I connect mysql from shell command line and get ERROR 2013 (HY000): Lost connection to MySQL server during query
Please give me some advices to solve it. Any special parameter need to change to serve about 100 concurrent connections?
#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 120
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive Off
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 15
##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 256
MaxClients 256
MaxRequestsPerChild 4000
</IfModule>
And when I use mysql> show variables and I take a look at max_connections parameter and see current value:
| max_connections | 1000
sometime I connect mysql from shell command line and get ERROR 2013 (HY000): Lost connection to MySQL server during query
Please give me some advices to solve it. Any special parameter need to change to serve about 100 concurrent connections?
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
I am using mysql 5.0.27 in redhat enterprise4. The problem occurs more frequence although I query mysql connections sometime but it only less than 50.
Thanks
Thanks
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
A few points:
- It's very rare for such a high value of max_connections (even on a very busy server). Try lowering it down to around your MaxClients value (say 300).
- Try taking a look at http://dev.mysql.com/doc/refman/5.0/en/gone-away.html. There are some general tips there about the cause of such a problem.
As Myles suggested, having this number of users for a 2GB server may be pushing things - however we run a server with this amount of RAM which is fine. So there may be a configuration problem (e.g. your buffer settings are too high for this number of users) or a max_questions problem. Can you please *attach* a text file containing your output from "mysqladmin -u root -p ext stat var". We'll then be able to see if this is the problem.
Thanks
Ken
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thanks for your response. Here is output information. Please give me some suggestion.
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 26 |
| Aborted_connects | 1687 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1763980107 |
| Bytes_sent | 3519992217 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 51 |
| Com_change_db | 378062 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 51 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 871 |
| 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 | 749682 |
| Com_insert_select | 79 |
| Com_kill | 2 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 21 |
| 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 | 12273200 |
| Com_set_option | 376630 |
| 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 | 1508 |
| Com_show_databases | 50 |
| Com_show_errors | 0 |
| Com_show_fields | 902689 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 336 |
| 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 | 32 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 37 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 42 |
| Com_show_triggers | 1508 |
| Com_show_variables | 15 |
| 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 | 19 |
| Com_update | 1798242 |
| 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 | 380001 |
| Created_tmp_disk_tables | 911051 |
| Created_tmp_files | 139 |
| Created_tmp_tables | 1051951 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 318873 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 93454 |
| Handler_read_key | 2537749191 |
| Handler_read_next | 1106214985 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4607265 |
| Handler_read_rnd_next | 4241702237 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2486423 |
| Handler_write | 10653843 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 245060 |
| Key_blocks_used | 87924 |
| Key_read_requests | 6704048605 |
| Key_reads | 100412 |
| Key_write_requests | 3202629 |
| Key_writes | 2123262 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 180 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 986 |
| Open_streams | 0 |
| Open_tables | 773 |
| Opened_tables | 280190 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 17006956 |
| Rpl_status | NULL |
| Select_full_join | 548 |
| Select_full_range_join | 726 |
| Select_range | 2029330 |
| Select_range_check | 0 |
| Select_scan | 1707797 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 463 |
| Sort_merge_passes | 67 |
| Sort_range | 1171654 |
| Sort_rows | 6695580 |
| Sort_scan | 70293 |
| 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 | 15118982 |
| Table_locks_waited | 273085 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 380000 |
| Threads_running | 1 |
| Uptime | 90762 |
+-----------------------------------+------------+
Uptime: 90762 Threads: 3 Questions: 17006957 Slow queries: 463 Opens: 280190 Flush tables: 1 Open tables: 773 Queries per second avg: 187.380
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| 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 | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| 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 | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| 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 | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| 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_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| 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_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| 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 | 1048576 |
| innodb_log_file_size | 5242880 |
| 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 | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 384385024 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| 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_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 30 |
| max_connections | 1000 |
| max_delayed_threads | 50 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 50 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| 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/lib/mysql/mysql.sock |
| sort_buffer_size | 32097144 |
| 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 | ICT |
| table_cache | 2000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| 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.27 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 26 |
| Aborted_connects | 1687 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1763980107 |
| Bytes_sent | 3519992217 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 51 |
| Com_change_db | 378062 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 51 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 871 |
| 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 | 749682 |
| Com_insert_select | 79 |
| Com_kill | 2 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 21 |
| 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 | 12273200 |
| Com_set_option | 376630 |
| 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 | 1508 |
| Com_show_databases | 50 |
| Com_show_errors | 0 |
| Com_show_fields | 902689 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 336 |
| 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 | 32 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 37 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 42 |
| Com_show_triggers | 1508 |
| Com_show_variables | 15 |
| 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 | 19 |
| Com_update | 1798242 |
| 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 | 380001 |
| Created_tmp_disk_tables | 911051 |
| Created_tmp_files | 139 |
| Created_tmp_tables | 1051951 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 318873 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 93454 |
| Handler_read_key | 2537749191 |
| Handler_read_next | 1106214985 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4607265 |
| Handler_read_rnd_next | 4241702237 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2486423 |
| Handler_write | 10653843 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 245060 |
| Key_blocks_used | 87924 |
| Key_read_requests | 6704048605 |
| Key_reads | 100412 |
| Key_write_requests | 3202629 |
| Key_writes | 2123262 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 180 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 986 |
| Open_streams | 0 |
| Open_tables | 773 |
| Opened_tables | 280190 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 17006956 |
| Rpl_status | NULL |
| Select_full_join | 548 |
| Select_full_range_join | 726 |
| Select_range | 2029330 |
| Select_range_check | 0 |
| Select_scan | 1707797 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 463 |
| Sort_merge_passes | 67 |
| Sort_range | 1171654 |
| Sort_rows | 6695580 |
| Sort_scan | 70293 |
| 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 | 15118982 |
| Table_locks_waited | 273085 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 3 |
| Threads_created | 380000 |
| Threads_running | 1 |
| Uptime | 90762 |
+-----------------------------------+------------+
Uptime: 90762 Threads: 3 Questions: 17006957 Slow queries: 463 Opens: 280190 Flush tables: 1 Open tables: 773 Queries per second avg: 187.380
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| 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 | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| 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 | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| 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 | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | YES |
| 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_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| 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_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| 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 | 1048576 |
| innodb_log_file_size | 5242880 |
| 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 | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 384385024 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| 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_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 30 |
| max_connections | 1000 |
| max_delayed_threads | 50 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 50 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| 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/lib/mysql/mysql.sock |
| sort_buffer_size | 32097144 |
| 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 | ICT |
| table_cache | 2000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| 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.27 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------------------------+
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
Digging into this output:
- The key_buffer_size (367M) may be too big. This is a global memory variable, so mysql will just eat up the amount you tell it to if it can. Because the value is so large, the server is only using 23% (130M) of that 367M to store the keys so try reducing to key_buffer_size to 156M (20% up from 130M to give some expansion space).
- Although query_cache_type is ON, the size of the query cache is zero, which means that there is effectively no cache to store the SELECT queries and their results. Try initially setting query_cache_size to 32M.
- The table_cache and max_connections values are also very large. Try reducing it to table_cache to 1556 and max_connections to 250. These values are inter-related, so it will take some time to get them right but these should be OK to start with. The max_used_connections recorded is 180, so 250 for max_connections will give headroom for expansion.
- The thread_cache_size is also zero. Please increase this to 128 to start with - hopefully this will reduce the very large number of threads created.
- Mysql is also creating a large number (86%) of its temporary tables that it needs to execute Moodle's more complex queries on disk - which will slow down the response time. This problem is related to three variables: sort_buffer_size, tmp_table_size and max_heap_table_size. Try changing as follows: sort_buffer_size = 16M, tmp_table_size=38M, max_heap_table_size=19M.
The above is specific to the data from your server - and there are some more variables which can be tuned (but start with these important ones first). In general it is possible to work out roughly how many users any moodle server can handle with this rough formula:
Available memory = Apache per process memory*MaxClients(Users) + MySQL global memory + MySQL per connection memory*Max_connections(users).Assuming you set MaxClients = Max_connections (as Iñaki suggested), then:
Users = (Available memory - MySQL global memory) / (Apache per process memory + MySQL per connection memory)Your available memory is shown in top. Apache per process memory is the private value from pmap -d for any apache processid. See the Mysql docs for more info about what counts as the global memory and per connection memory. There is also the memory which Moodle uses for cacheing, and any PHP accelerator cache you may have - these can be added to the formula for more fine-tuning if you want to.
So by my calculations, with 2GB of RAM, when the server hit 180 connections it swapped very bad - which may be why there was a lost connection. PHP probably simply timed-out the connection to Mysql. Increasing to 4GB should make the server handle 70 mysql connections without swapping to disk, and with 8GB of RAM it can handle 150 connections without swapping. To paraphrase Myles, memory is very important - but don't forget that it is just one part of the equation (a php accelerator being one of the other parts 
)
Hope this makes sense and helps to improve matters.
Ken
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thanks a lot. I've made some changes based on your advises. Please see my new output in attached file and let me know if any need to be improve.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
Things are looking better! Try these also:
- The 130M key_buffer_size setting is not getting used. Reduce to 64M to free up some memory. The key read and write ratios look good, so nothing to change there.
- Good to see the query cache being used so well (57% usage), but there's a high level of fragmentation in the cache (18%). Set query_cache_min_res_unit = 2K to reduce this problem. The query cache is stable overall and has an efficiency of 60% which is good.
- The table_cache of 600 is currently full. Try increasing to say 800.
- Although not as much as before, we're still getting many temporary tables created on disk rather than in memory. Change tmp_table_size = 78M and max_heap_table_size =19M. Keep an eye on created_tmp_disk_tables - it should be much less than created_tmp_tables - and continue adjusting until it is.
- The sort_buffer_size is very large. Try reducing to 64M - default is 2M and on a large site I have it set to 8M, so you may want to consider adjusting this down further.
- The thread_cache is also full. Try increasing to 250 - 300.
I notice you reduced your wait_timeout to 15secs - while this releases client connections (and therefore memory) be careful of the adverse effect of processor load. I remember making a change like this on both a Windows box and a Centos box (which is more or less RedHat) and processor utilisation was unacceptably high. An alternative is to try adding the line "memlock" in your my.cnf to lock mysql in memory and avoid process swapping - but leave wait_timeout as the default. Are you experiencing high processor loading as well?
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thank you very much. How about Aborted_clients? Sometime I query from shell and get message MySQL server has gone away No connection. Trying to reconnect...
By the way, any suggestion to speedup loading page? It seems lowly at the moment. I am looking to hear your experience, I am not expert at experiencing high processor loading.
Before trying to make the change as you recommend, I export an new output file. Please take a look at the attachment.
By the way, any suggestion to speedup loading page? It seems lowly at the moment. I am looking to hear your experience, I am not expert at experiencing high processor loading.
Before trying to make the change as you recommend, I export an new output file. Please take a look at the attachment.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
Yes, the aborted_clients value is very high - try leaving wait_timeout as the default value of 28800 and reduce max_allowed_packet to 8M (twice the recommended value from the Moodle docs).
The worrying variable is aborted_connects - this should be zero, and if it is not it indicates network problems or and invalid database/host/username/password combination. Are you running the mysql server on another server?
Looking again at your output (you have a very busy server - 204 queries per second!), the only two variables which need changing from the last post are:
- Try increasing max_connections to 360.
- Increase thread_cache_size to 300 also.
To speed up page loading, try a php accelerator such as eaccelerator (use a redhat rpm or build from source) with which moodle works well. I've had problems with both eaccelerator and APC at *very* high loads on linux: symptoms are apache seg faulting and half complete pages being displayed. I have switched to Xcache which I had to compile from source - no problems so far.
It is important to remember that we can spend money on RAM, faster disks and faster/more CPUs and also a short amount of time each month checking the mysql variables, running mysqlcheck and getting the moodle server as optimised as we can - but the most effective change is in the structure of the mysql queries and the actual php code. If there is an expensive or slow query (and you have 8305 of them taking more than 10 seconds to execute) or an iteration which consumes all the cpu power, then there's nothing we can do about it in terms of server variables. So much of the optimisation that has the greatest impact has to be done by the developers - see MDL-9617 as an example.
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
1 - I am runing Mysql and apache in the same server.
2 - It seems I have installed eccelerator. How to ensure it works properly?
3- I've configued some parameters as your suggestion yesterday. Here is the new output file for today. Thanks for some new suggestions to reduce aborted_connection above. I will try it. Hope get more from you.
Thank you very much.
2 - It seems I have installed eccelerator. How to ensure it works properly?
3- I've configued some parameters as your suggestion yesterday. Here is the new output file for today. Thanks for some new suggestions to reduce aborted_connection above. I will try it. Hope get more from you.
Thank you very much.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
If apache and mysql are on the same server and there are still some aborted_connects, this indicates something is not quite right (or the server is just overloaded). Maybe more RAM is the answer here?
To check that eaccelerator is working, take a look at your phpinfo output page from the moodle admin pages. It should be listed somewhere in the list (see http://eaccelerator.net/wiki/TroubleShooting#Veryfiing) for more info. There is also a web interface which displays the cache status, etc.
To summarise the settings:
key_buffer_size=64M <-
query_cache_size=32M
query_cache_min_res_unit=2K
table_cache=800
max_connections=360 <-
thread_cache_size=300
sort_buffer_size=16M <-
tmp_table_size=78M
max_heap_table_size=19M
wait_timeout=28800
max_allowed_packet=8M <-
memlock
If you want to cut back further on memory usage, try disabling innodb usage:
- Backup your moodle database
- Convert your innodb tables back to myisam (edit the admin/innodb.php file by changing the file type in there, save as myiasm.php and run this from the browser).
- Disable innodb tables (add skip-innodb in my.cnf).
- Restart mysql.
Hope all goes well!
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thank you very much for your supports.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
I am getting database connection failed again when set wait_timeout back to default and using memlock as well. I see in output file, it reduce aborted_clients but increase aborted_connections.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Hi experts,
Please take a look at my attached output file and give me some advise to improve the performance. I am looking forward to here from you.
Thanks,
Please take a look at my attached output file and give me some advise to improve the performance. I am looking forward to here from you.
Thanks,
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Geof Duncan -
I appear to be having the same issues as Mr Shortcut here. I also appear to be completely clueless as to what this all means. 
I've attached my own file. Could someone here help me get started???
My Server is a dedicated webhost server with the standard httpd.conf file. I'm also seeing 256 MaxClients. Specs are:
# AMD Athlon 64 Dual Core 3800
# 3 GB DDR2-800 RAM
# 250 GB SATA Hard Drive
The help desk at my webhost is saying that my load average isn't over 1 and that I still seem to have over 50% of memory available. So, they are really thinking I DON'T need extra RAM.
This leads me to believe that the settings here could be a "fix" for my issue, but, I'm a little freaked out in thinking that I might completely bork my server by picking the wrong settings, so, any help would be appreciated.
Clueless in the Carolinas,
-Geof
I've attached my own file. Could someone here help me get started???
My Server is a dedicated webhost server with the standard httpd.conf file. I'm also seeing 256 MaxClients. Specs are:
# AMD Athlon 64 Dual Core 3800
# 3 GB DDR2-800 RAM
# 250 GB SATA Hard Drive
The help desk at my webhost is saying that my load average isn't over 1 and that I still seem to have over 50% of memory available. So, they are really thinking I DON'T need extra RAM.
This leads me to believe that the settings here could be a "fix" for my issue, but, I'm a little freaked out in thinking that I might completely bork my server by picking the wrong settings, so, any help would be appreciated.
Clueless in the Carolinas,
-Geof
In reply to Geof Duncan
Re: site loading slowly and sometime I get database connection failed
by Geof Duncan -
We just discovered our web host would be happy to assign a technician (it ain't cheap, but, it does work) to optimize our settings for us.
This guy did make sense of the numbers.
This guy did make sense of the numbers.
In reply to Ken Wilson
Re: site loading slowly and sometime I get database connection failed
by Wayne Lee -
FAO Ken Wilson.
I seem to be having the same problems as others in this discussion. I was wondering if you would be so kind to look at my configuration settings also?
Many Thanks
In reply to Wayne Lee
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Yesterday, we served about 1000 concurrent users for an online-exam, we got bad performance.
Here is some infomation about our moodle site: We run moodle 1.7 +(include webserver + database) in one dual core server with 8GB RAM and set ServerLimit in apache config to 800.
Please take a look at my attachment give me some advisions, plz.
Thanks in advanced!
Here is some infomation about our moodle site: We run moodle 1.7 +(include webserver + database) in one dual core server with 8GB RAM and set ServerLimit in apache config to 800.
Please take a look at my attachment give me some advisions, plz.
Thanks in advanced!
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Myles Carrick -
Mate it'd be helpful to us if you can tell us more from your Apache config... have you looked at http://docs.moodle.org/en/Performance#Apache_performance ?
Also, it'd be useful to know about I/O stuff. What type, quantity and configuration of disks are you using?
Are you using a PHP cache/accelerator?
How is MySQL configured?
1000 concurrent users is a fair bit for a single server that includes the DB... some clever tweaking is going to be required.
For starters I can tell you that running Moodle 1.7+ is going to be very sub-optimal. Is something preventing you from running current generation Moodle? Even if you can't upgrade to 1.9, you _really_ need to be running a fully patched 1.7 system (i.e. 1.7.4+ at the time of writing).
Also - this is a forum where we use all use real names - helps to keep the communication open and genuine.
Cheers,
Myles
Also, it'd be useful to know about I/O stuff. What type, quantity and configuration of disks are you using?
Are you using a PHP cache/accelerator?
How is MySQL configured?
1000 concurrent users is a fair bit for a single server that includes the DB... some clever tweaking is going to be required.
For starters I can tell you that running Moodle 1.7+ is going to be very sub-optimal. Is something preventing you from running current generation Moodle? Even if you can't upgrade to 1.9, you _really_ need to be running a fully patched 1.7 system (i.e. 1.7.4+ at the time of writing).
Also - this is a forum where we use all use real names - helps to keep the communication open and genuine.
Cheers,
Myles
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Sylvio Runge -
>Queries per second avg: 1684.925
Upps...; now. The database is completely there overloaded. Here one should try the database by loadbalancing on several servers to distribute.
Moodle sql-querys is unfortunately often not optimized.
In newer versions (>1.9 see http://moodle.org/mod/forum/discuss.php?d=91667 ; or if necessary 1.7.4+) that is to be somewhat better....
S.
Upps...; now. The database is completely there overloaded. Here one should try the database by loadbalancing on several servers to distribute.
Moodle sql-querys is unfortunately often not optimized.
In newer versions (>1.9 see http://moodle.org/mod/forum/discuss.php?d=91667 ; or if necessary 1.7.4+) that is to be somewhat better....
S.
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Mr. Shortcut -
Thank you very much for the quick response.
Are you using a PHP cache/accelerator? We are using the PHP cache/accelerator.
How is MySQL configured? Please take a look at parameters in attached file in my previous post.
I am looking forward to hearing your comments in my output file. Please give me some analytics and advision.
Thanks,
Hung
Are you using a PHP cache/accelerator? We are using the PHP cache/accelerator.
How is MySQL configured? Please take a look at parameters in attached file in my previous post.
I am looking forward to hearing your comments in my output file. Please give me some analytics and advision.
Thanks,
Hung
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Tim Hunt -
If, by online exam, you mean the quiz module, then it is known to be pretty resource-intensive.
There are two reasons for this. First, what it is doing is fundamentally quite complicated, so it needs to hit the database quite a lot. Second, it has not had as much performance work done on it as some other parts of Moodle, so it hits the database a bit more than in necessary.
The worst possible scenario is when you have a timed test that all students start simultaneously. When a student click 'Submit all and finish' on a quiz, there is a lot of work that has to be done. In a timed test, every student will try to do this during the last minute of there time limit. If they all started together, they will all finish together. Ouch!
There are two reasons for this. First, what it is doing is fundamentally quite complicated, so it needs to hit the database quite a lot. Second, it has not had as much performance work done on it as some other parts of Moodle, so it hits the database a bit more than in necessary.
The worst possible scenario is when you have a timed test that all students start simultaneously. When a student click 'Submit all and finish' on a quiz, there is a lot of work that has to be done. In a timed test, every student will try to do this during the last minute of there time limit. If they all started together, they will all finish together. Ouch!
In reply to Tim Hunt
Re: site loading slowly and sometime I get database connection failed
by Heike Ebelt -
I have a very similar problem and don't want to open a new topic:
Our Moodle page has become VERY VERY SLOW recently. I am talking about minutes! Sometimes I receive even errors, such as that the course id couldn't be found, or it offers me the php to download or I get other error messages. We have only 20 users!!! And no-one of them does exercises because most of the courses still need content.
In addition, when I run the cron.php it won't finish and show me the results. However, if I reopen the admin page I see that the option for running the cron.php is gone, which I interpret as a successful attempt as I ran it.
I haven't changed any settings since the problem has started. However, I believe that it has to do with a SCORM exercise I uploaded recently. Around that time my problem occured. On the other hand, when I take it out and delete the file, Moodle continues to be very slow.
I am using Moodle 1.9 Beta 4, PHP 5 and MySQL. I somehow believe that the problem has to do with my MySCQL...
Please help me. I don't know much about the technical stuff...
Heike
(heikeebelt@yahoo.com)
Our Moodle page has become VERY VERY SLOW recently. I am talking about minutes! Sometimes I receive even errors, such as that the course id couldn't be found, or it offers me the php to download or I get other error messages. We have only 20 users!!! And no-one of them does exercises because most of the courses still need content.
In addition, when I run the cron.php it won't finish and show me the results. However, if I reopen the admin page I see that the option for running the cron.php is gone, which I interpret as a successful attempt as I ran it.
I haven't changed any settings since the problem has started. However, I believe that it has to do with a SCORM exercise I uploaded recently. Around that time my problem occured. On the other hand, when I take it out and delete the file, Moodle continues to be very slow.
I am using Moodle 1.9 Beta 4, PHP 5 and MySQL. I somehow believe that the problem has to do with my MySCQL...
Please help me. I don't know much about the technical stuff...
Heike
(heikeebelt@yahoo.com)
In reply to Mr. Shortcut
Re: site loading slowly and sometime I get database connection failed
by Đặng Quang Hùng -
Dear All,
Please drop me some advisions about configuration parameters based on the attachment.
Thanks a lot!
Please drop me some advisions about configuration parameters based on the attachment.
Thanks a lot!
In reply to Đặng Quang Hùng
Re: site loading slowly and sometime I get database connection failed
by Ken Wilson -
Hi Đặng
A couple of issues that jump out of those stats:
Good luck!
Ken
A couple of issues that jump out of those stats:
- Are you running the latest version of Moodle? The reason is that there are a relatively large number of table scans from joins and select queries, which can be reduced if Moodle is indexed correctly. Check your indexes in Site Admin -> Miscellaneous -> XMLDB editor.
- The value of key_buffer_size is currently set to 256M, and it's full (eek!). Try increasing it to 320M initially and see how that goes - but remember that the general rule is to set key_buffer_size to no more than 30% of your free RAM.
- 91% of the temporary tables were created on disk. Try increasing the value of sort_buffer_size to 48M and look for a drop in created_tmp_disk_tables compared to created_tmp_tables.
Good luck!
Ken
In reply to Đặng Quang Hùng
Re: site loading slowly and sometime I get database connection failed
by Sojiro Sato -
If you're using MySQL and your engine is MyISAM, I suggest converting to InnoDB. I've seen a drastic improvement in page loads, although some reads can be very slow (such as all of 10 million mdl_log records) but this performance decrease is a rare event.
In reply to Đặng Quang Hùng
Re: site loading slowly and sometime I get database connection failed
by Đặng Quang Hùng -
Thank you very much. I am trying with your suggestion now. Hope it's ok.
Best Rgds,
Best Rgds,
In reply to Đặng Quang Hùng
Re: site loading slowly and sometime I get database connection failed
by Đặng Quang Hùng -
Hi all,
Please take a look at the result after applying your suggestion and give me some comments. Thank you very much.
Best Rgds,
Hung
Please take a look at the result after applying your suggestion and give me some comments. Thank you very much.
Best Rgds,
Hung