[opencms-dev] MySQLNonTransientConnectionException - too many connections
Paul-Inge Flakstad
flakstad at npolar.no
Wed May 4 09:53:21 CEST 2011
Well, things are not well...
The site is EXTREMELY slow again, no doubt due to the amount of traffic.
It was fine yesterday, and earlier this morning. (I'm guessing something happened the moment that the amount of requests increased as people arrived at work around 9am.)
When attempting to open a page:
1.) Page starts loading, but takes forever to complete OR
2.) Page never completes, instead you get a 503 (from Apache, not OpenCms)
As far as I can see, Java/MySQL is not hogging CPU/memory during "hanging requests".
This site should be able to handle tens of thousands pageviews per day, and should handle about 3000-5000 pageviews per day. Most request are made during working hours.
As of now, I'm guessing we're nowhere near those numbers.
NOTE: There are also 2 other opencms instances, hosting smaller sites, running on the same Tomcat.
I tried restarting MySQL. It stopped, and won't start now. Looking into it now.
Oh, and I found this in the Apache log:
[Wed May 04 08:27:45 2011] [error] (70007)The timeout specified has expired: ajp_ilink_receive() can't receive header
[Wed May 04 08:27:45 2011] [error] ajp_read_header: ajp_ilink_receive failed
[Wed May 04 08:27:45 2011] [error] (120006)APR does not understand this error code: proxy: read response failed from 127.0.0.1:8009 (localhost)
[Wed May 04 08:27:48 2011] [error] (70007)The timeout specified has expired: ajp_ilink_receive() can't receive header
[Wed May 04 08:27:48 2011] [error] ajp_read_header: ajp_ilink_receive failed
[Wed May 04 08:27:48 2011] [error] (120006)APR does not understand
MySQL:
######### BEGIN my.cnf ##########
[root at abel ~]# vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#skip-name-resolve
# Set some reasonable max-sizes
max_allowed_packet=128M
max_connections=200
max_connect_errors=100
max_tmp_tables=128M
# In-memory cache
query_cache_size=128M
table_cache=512
thread_cache_size=128
key_buffer_size=4096M # size of the buffer used for index blocks: aka "key cache"
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
# Enable logging of slow queres, default 10 seconds
log-slow-queries
# Log queries taking longer than 5 seconds
long_query_time=5
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
############# END my.cnf ############
############# BEGIN mysqld-slow.log ############
# Query_time: 60 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
use npcms;
INSERT INTO CMS_RESOURCE_LOCKS (RESOURCE_PATH,USER_ID,PROJECT_ID,LOCK_TYPE) VALUES ('/sites/np/en/people/oystein.varpe/index.html','bfe07a29-724e-11e0-806c-00237dea8b58','66017d61-6cb8-11df-91d1-00237dea8b58',4);
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/libexec/mysqld, Version: 5.0.77-log (Source distribution). started with:
Tcp p
############# END mysqld-slow.log ############
mysql> show processlist;
+-----+------+-----------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------------+---------+---------+------+-------+------------------+
| 60 | root | localhost:50548 | npcms | Sleep | 7487 | | NULL |
| 90 | root | localhost:59594 | npcms | Sleep | 7490 | | NULL |
| 91 | root | localhost:59595 | npcms | Sleep | 7134 | | NULL |
| 100 | root | localhost:59604 | npcms | Sleep | 980 | | NULL |
| 253 | root | localhost:51290 | opencms | Sleep | 1 | | NULL |
| 254 | root | localhost:51291 | opencms | Sleep | 1564 | | NULL |
| 256 | root | localhost:56245 | npcms | Sleep | 379 | | NULL |
| 257 | root | localhost:56246 | npcms | Sleep | 7487 | | NULL |
| 258 | root | localhost:56249 | npcms | Sleep | 980 | | NULL |
| 259 | root | localhost:56250 | npcms | Sleep | 7487 | | NULL |
| 260 | root | localhost:56251 | npcms | Sleep | 4 | | NULL |
| 261 | root | localhost:56254 | npcms | Sleep | 7489 | | NULL |
| 262 | root | localhost:56255 | npcms | Sleep | 7487 | | NULL |
| 263 | root | localhost:56256 | npcms | Sleep | 7489 | | NULL |
| 266 | root | localhost:56259 | npcms | Sleep | 980 | | NULL |
| 267 | root | localhost:56260 | npcms | Sleep | 7488 | | NULL |
| 270 | root | localhost:56263 | npcms | Sleep | 3729 | | NULL |
| 271 | root | localhost:56264 | npcms | Sleep | 7487 | | NULL |
| 272 | root | localhost:56265 | npcms | Sleep | 7134 | | NULL |
| 274 | root | localhost:56267 | npcms | Sleep | 7487 | | NULL |
| 276 | root | localhost:56269 | npcms | Sleep | 7489 | | NULL |
| 277 | root | localhost:56270 | npcms | Sleep | 7487 | | NULL |
| 278 | root | localhost:56271 | npcms | Sleep | 7487 | | NULL |
| 279 | root | localhost:56272 | npcms | Sleep | 7488 | | NULL |
| 280 | root | localhost:56273 | npcms | Sleep | 7134 | | NULL |
| 281 | root | localhost:56274 | npcms | Sleep | 7488 | | NULL |
| 282 | root | localhost:40350 | opencms | Sleep | 417 | | NULL |
| 290 | root | localhost:42042 | accms | Sleep | 610 | | NULL |
| 296 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+------+-----------------+---------+---------+------+-------+------------------+
29 rows in set (0.00 sec)
mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 58 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 144 |
| Bytes_sent | 6446 |
| 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 | 0 |
| 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 | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| 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 | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| 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 | 295 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 51 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| 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 | 250 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 381 |
| Innodb_buffer_pool_pages_data | 25 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1 |
| Innodb_buffer_pool_pages_free | 487 |
| 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 | 554 |
| Innodb_buffer_pool_reads | 18 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
| Innodb_data_fsyncs | 7 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2592768 |
| Innodb_data_reads | 31 |
| Innodb_data_writes | 7 |
| Innodb_data_written | 35328 |
| Innodb_dblwr_pages_written | 1 |
| Innodb_dblwr_writes | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 2 |
| Innodb_os_log_fsyncs | 5 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1024 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 25 |
| Innodb_pages_written | 1 |
| 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 | 25 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 3413964 |
| Key_blocks_used | 15508 |
| Key_read_requests | 48359034 |
| Key_reads | 31139 |
| Key_write_requests | 227591 |
| Key_writes | 130452 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 124 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 413 |
| Open_streams | 0 |
| Open_tables | 315 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 65 |
| Qcache_free_memory | 36690328 |
| Qcache_hits | 13165331 |
| Qcache_inserts | 1098712 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 235351 |
| Qcache_queries_in_cache | 32218 |
| Qcache_total_blocks | 64620 |
| Queries | 14543660 |
| Questions | 3 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| 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 | 2995944 |
| Table_locks_waited | 852 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 95 |
| Threads_connected | 29 |
| Threads_created | 124 |
| Threads_running | 1 |
| Uptime | 60386 |
| Uptime_since_flush_status | 14543660 |
+-----------------------------------+----------+
249 rows in set (0.00 sec)
mysql> show variables;
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| bdb_cache_size | 8384512 |
| bdb_log_buffer_size | 262144 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| 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 | 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_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 | 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_adaptive_hash_index | ON |
| 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_rollback_on_timeout | OFF |
| 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 | 4294967296 |
| 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 | ON |
| log_warnings | 1 |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 134217728 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 100 |
| max_connections | 200 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| 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 | 134217728 |
| 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 | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1234 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | |
| 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 | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| 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/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| 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 | 512 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 128 |
| 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.77-log |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------------------------+
239 rows in set (0.00 sec)
Relevant parts of the config files:
-----------------------------------
######### BEGIN opencms.properties ##########
#
# Configuration of the driver manager
#################################################################################
driver.vfs=db
driver.project=db
driver.user=db
driver.history=db
#
# Declaration of database pools
#################################################################################
db.pools=default
#
# Configuration of the default database pool
#################################################################################
# name of the JDBC driver
db.pool.default.jdbcDriver=org.gjt.mm.mysql.Driver
# URL of the JDBC driver
db.pool.default.jdbcUrl=jdbc:mysql://localhost:3306/npcms
# optional parameters for the URL of the JDBC driver
db.pool.default.jdbcUrl.params=?characterEncoding\=UTF-8
# user name to connect to the database
db.pool.default.user=xxx
# password to connect to the database
db.pool.default.password=xxx
# the URL to make the JDBC DriverManager return connections from the DBCP pool
db.pool.default.poolUrl=opencms:default
# the maximum number of objects that can be borrowed from the pool
# NOTE: The value should not increase MySQL's max_connections!
#db.pool.default.maxActive=25
db.pool.default.maxActive=200
# the maximum amount of time before throwing an exception when the pool is exhausted
db.pool.default.maxWait=2000
# the minimum number of objects that will kept connected
db.pool.default.minIdle=3
# the maximum number of objects that can sit idled in the pool
db.pool.default.maxIdle=100
# action to take when the pool is exhausted {grow|block|fail}
db.pool.default.whenExhaustedAction=grow
# connections will be validated before they are borrowed from the pool
db.pool.default.testOnBorrow=false
# connections will be validated by evictor thread
db.pool.default.testWhileIdle=true
# number of milliseconds to sleep between runs of the evictor thread
# -1 means no idle connection evictor thread will be run
db.pool.default.timeBetweenEvictionRuns=1800000
# number of connections tested in a run of the evictor thread
db.pool.default.numTestsPerEvictionRun=3
# minimum amount of time in milliseconds a connection may be idle in the pool before it is eligable for eviction
db.pool.default.minEvictableIdleTime=600000
# the query to validate connections
db.pool.default.testQuery=SELECT STRUCTURE_ID FROM CMS_OFFLINE_STRUCTURE WHERE RESOURCE_PATH = '/'
#db.pool.default.testQuery=
# number of attempts to connect to the database during startup (default = 10)
db.pool.default.connects=10
# sleep time between two attempts to connect to the database during startup in ms (default = 5000)
db.pool.default.wait=5000
#
# Configuration for statement pooling
#################################################################################
db.statements.default.pooling=true
#db.statements.default.maxActive=200
db.statements.default.maxActive=900
db.statements.default.maxWait=2000
db.statements.default.maxIdle=100
#db.statements.default.whenExhaustedAction=block
db.statements.default.whenExhaustedAction=grow
#
# Configuration of the database driver manager
#################################################################################
db.name=mysql_41
db.vfs.driver=org.opencms.db.mysql.CmsVfsDriver
db.vfs.pool=opencms:default
db.vfs.sqlmanager=org.opencms.db.mysql.CmsSqlManager
db.project.driver=org.opencms.db.mysql.CmsProjectDriver
db.project.pool=opencms:default
db.project.sqlmanager=org.opencms.db.mysql.CmsSqlManager
db.user.driver=org.opencms.db.mysql.CmsUserDriver
db.user.pool=opencms:default
db.user.sqlmanager=org.opencms.db.mysql.CmsSqlManager
db.history.driver=org.opencms.db.mysql.CmsHistoryDriver
db.history.pool=opencms:default
db.history.sqlmanager=org.opencms.db.mysql.CmsSqlManager
db.cos.pool=opencms:default
######### END opencms.properties ##########
######### BEGIN server.xml ##########
<!-- Define a non-SSL HTTP/1.1 Connector on port 8080 -->
<Connector port="8080" maxHttpHeaderSize="8192"
maxThreads="512" minSpareThreads="10" maxSpareThreads="100"
maxProcessors="512" acceptCount="400"
enableLookups="false" redirectPort="8443"
connectionTimeout="20000" disableUploadTimeout="true" />
<!-- Define an AJP 1.3 Connector on port 8009 -->
<Connector port="8009"
maxThreads="512" minSpareThreads="10" maxSpareThreads="100"
maxProcessors="512" acceptCount="400"
emptySessionPath="true" protocol="AJP/1.3" URIEncoding="utf-8" />
######### END server.xml ##########
######### BEGIN httpd.conf ##########
<IfModule prefork.c>
StartServers 8
MinSpareServers 50
MaxSpareServers 200
ServerLimit 1024
MaxClients 512
MaxRequestsPerChild 4000
</IfModule>
<IfModule worker.c>
StartServers 2
MaxClients 512
MinSpareThreads 50
MaxSpareThreads 200
ThreadsPerChild 25
MaxRequestsPerChild 4000
</IfModule>
######### END httpd.conf ##########
Thanks,
Paul
> -----Original Message-----
> From: opencms-dev-bounces at opencms.org
> [mailto:opencms-dev-bounces at opencms.org] On Behalf Of
> Paul-Inge Flakstad
> Sent: 3. mai 2011 17:10
> To: The OpenCms mailing list
> Subject: Re: [opencms-dev]
> MySQLNonTransientConnectionException - too many connections
>
> Hi Hernan,
>
> I used all of yesterday to dig deep in the config of all
> Tomat, Apache, MySQL and OpenCms. Experimented for a while, it
> worked better (today I started seeing "Too many files open"
> exceptions though).
>
> After implementing the changes you suggested here, it seems
> everything is A LOT better - probably very much due to the
> key_buffer_size, which provided the major change after
> yesterday's experimenting.
>
> I'll just have to wait and see if things are stable now.
>
> Thank you so much for your help! :)
>
> Best regards,
> Paul
>
> > -----Original Message-----
> > From: opencms-dev-bounces at opencms.org
> > [mailto:opencms-dev-bounces at opencms.org] On Behalf Of HERNAN - TFSLA
> > Sent: 3. mai 2011 00:22
> > To: opencms-dev at opencms.org
> > Subject: Re: [opencms-dev]
> > MySQLNonTransientConnectionException - too many connections
> >
> >
> > If both JAVA and MYSQL are hunging at the same time, most
> > probably, you have
> > an issue with a process / coding rather than config.
> > Nevertheless, your
> > config is not a very happy one :)
> >
> > 1) First of all, you should probably enable SLOW QUERIES and
> > during the high
> > cpu usage show a process list on MYSQL (queries on execution).
> >
> > 2) if you are using MYISAM table format, adjust the
> > KEY_BUFFER_SIZE value to
> > 4gb (1/3 of the total ram). If you are using INNODB, do that for
> > INNODB_BUFFER_POOL_SIZE.
> >
> > 3) change your TABLE_CACHE to something like 512,
> > thread_cache to 32 and
> > change max_tmp_tables to 128mb.
> >
> > 4) change max_connections to something like 200. There is no
> > reason to put
> > it on 2000 for a small site.
> >
> > 5) change MAX_ACTIVE on the opencms.properties to 200 (it should be
> > sufficient with 25 for small sites, but it depends on a lot
> > of factors such
> > as connection types, cycling, session duration, etc).
> >
> > 6) on my.cnf add the following line: --skip-name-resolve
> >
> > I'm also assuming your flexcache configuration is in place
> > for every major
> > JSP and you have an Apache cache / content expiration strategy.
> >
> > Please, change all on we continue from there. If the problem
> > presents itself
> > again, please post the SHOW PROCESS LIST and slow queries results.
> >
> >
> > Paul-Inge Flakstad wrote:
> > >
> > > Hi,
> > >
> > > Thanks for the help, I appreciate it very much!
> > >
> > > Our server admin says Java and MySQL are both at 100% CPU
> > when the system
> > > hangs.
> > >
> > > The box is a new dual-core with 16GB RAM.
> > >
> > > Here's the config (I removed some irrelevant parts):
> > >
> > >
> > > The connector is AJP 1.3 - defined in server.xml:
> > > <Connector port="8009" emptySessionPath="true" protocol="AJP/1.3"
> > > URIEncoding="utf-8" />
> > >
> > >
> > > opencms.properties
> > > ------------------
> > > #
> > > # Configuration of the driver manager
> > >
> > ##############################################################
> > ###################
> > > driver.vfs=db
> > > driver.project=db
> > > driver.user=db
> > > driver.history=db
> > >
> > > #
> > > # Declaration of database pools
> > >
> > ##############################################################
> > ###################
> > > db.pools=default
> > >
> > > #
> > > # Configuration of the default database pool
> > >
> > ##############################################################
> > ###################
> > > # name of the JDBC driver
> > > db.pool.default.jdbcDriver=org.gjt.mm.mysql.Driver
> > >
> > > # URL of the JDBC driver
> > > db.pool.default.jdbcUrl=jdbc:mysql://localhost:3306/npcms
> > >
> > > # optional parameters for the URL of the JDBC driver
> > > db.pool.default.jdbcUrl.params=?characterEncoding\=UTF-8
> > >
> > > # user name to connect to the database
> > > db.pool.default.user=xxxxxxxxx
> > >
> > > # password to connect to the database
> > > db.pool.default.password=xxxxxxxxxx
> > >
> > > # the URL to make the JDBC DriverManager return connections
> > from the DBCP
> > > pool
> > > db.pool.default.poolUrl=opencms:default
> > >
> > > # the maximum number of objects that can be borrowed from the pool
> > > db.pool.default.maxActive=25
> > >
> > > # the maximum amount of time before throwing an exception
> > when the pool is
> > > exhausted
> > > db.pool.default.maxWait=2000
> > >
> > > # the minimum number of objects that will kept connected
> > > db.pool.default.minIdle=3
> > >
> > > # the maximum number of objects that can sit idled in the pool
> > > db.pool.default.maxIdle=10
> > >
> > > # action to take when the pool is exhausted {grow|block|fail}
> > > db.pool.default.whenExhaustedAction=grow
> > >
> > > # connections will be validated before they are borrowed
> > from the pool
> > > db.pool.default.testOnBorrow=false
> > >
> > > # connections will be validated by evictor thread
> > > db.pool.default.testWhileIdle=true
> > >
> > > # number of milliseconds to sleep between runs of the
> evictor thread
> > > # -1 means no idle connection evictor thread will be run
> > > db.pool.default.timeBetweenEvictionRuns=3600000
> > >
> > > # number of connections tested in a run of the evictor thread
> > > db.pool.default.numTestsPerEvictionRun=3
> > >
> > > # minimum amount of time in milliseconds a connection may
> > be idle in the
> > > pool before it is eligable for eviction
> > > db.pool.default.minEvictableIdleTime=1800000
> > >
> > > # the query to validate connections
> > > db.pool.default.testQuery=SELECT STRUCTURE_ID FROM
> > CMS_OFFLINE_STRUCTURE
> > > WHERE RESOURCE_PATH = '/'
> > > #db.pool.default.testQuery=
> > >
> > > # number of attempts to connect to the database during
> > startup (default =
> > > 10)
> > > db.pool.default.connects=10
> > >
> > > # sleep time between two attempts to connect to the
> database during
> > > startup in ms (default = 5000)
> > > db.pool.default.wait=5000
> > >
> > > #
> > > # Configuration for statement pooling
> > >
> > ##############################################################
> > ###################
> > > db.statements.default.pooling=true
> > > db.statements.default.maxActive=200
> > > db.statements.default.maxWait=2000
> > > db.statements.default.maxIdle=100
> > > db.statements.default.whenExhaustedAction=block
> > >
> > > #
> > > # Configuration of the database driver manager
> > >
> > ##############################################################
> > ###################
> > > db.name=mysql_41
> > >
> > > db.vfs.driver=org.opencms.db.mysql.CmsVfsDriver
> > > db.vfs.pool=opencms:default
> > > db.vfs.sqlmanager=org.opencms.db.mysql.CmsSqlManager
> > >
> > > db.project.driver=org.opencms.db.mysql.CmsProjectDriver
> > > db.project.pool=opencms:default
> > > db.project.sqlmanager=org.opencms.db.mysql.CmsSqlManager
> > >
> > > db.user.driver=org.opencms.db.mysql.CmsUserDriver
> > > db.user.pool=opencms:default
> > > db.user.sqlmanager=org.opencms.db.mysql.CmsSqlManager
> > >
> > > db.history.driver=org.opencms.db.mysql.CmsHistoryDriver
> > > db.history.pool=opencms:default
> > > db.history.sqlmanager=org.opencms.db.mysql.CmsSqlManager
> > >
> > > db.cos.pool=opencms:default
> > > #
> > > # End of opencms.properties
> > >
> > ##############################################################
> > ###################
> > >
> > >
> > > mysql> show variables;
> > >
> > +---------------------------------+---------------------------
> > ---------------------------------+
> > > | Variable_name | Value
> >
> > > |
> > >
> > +---------------------------------+---------------------------
> > ---------------------------------+
> > > | auto_increment_increment | 1
> >
> > > |
> > > | auto_increment_offset | 1
> >
> > > |
> > > | automatic_sp_privileges | ON
> >
> > > |
> > > | back_log | 50
> >
> > > |
> > > | basedir | /usr/
> >
> > > |
> > > | bdb_cache_size | 8384512
> >
> > > |
> > > | bdb_home | /var/lib/mysql/
> >
> > > |
> > > | bdb_log_buffer_size | 262144
> >
> > > |
> > > | bdb_logdir |
> >
> > > |
> > > | bdb_max_lock | 10000
> >
> > > |
> > > | bdb_shared_data | OFF
> >
> > > |
> > > | bdb_tmpdir | /tmp/
> >
> > > |
> > > | binlog_cache_size | 32768
> >
> > > |
> > > | bulk_insert_buffer_size | 8388608
> >
> > > |
> > > | completion_type | 0
> >
> > > |
> > > | concurrent_insert | 1
> >
> > > |
> > > | connect_timeout | 10
> >
> > > |
> > > | default_week_format | 0
> >
> > > |
> > > | delay_key_write | ON
> >
> > > |
> > > | delayed_insert_limit | 100
> >
> > > |
> > > | delayed_insert_timeout | 300
> >
> > > |
> > > | delayed_queue_size | 1000
> >
> > > |
> > > | 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 | 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_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 | 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_adaptive_hash_index | ON
> >
> > > |
> > > | 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_rollback_on_timeout | OFF
> >
> > > |
> > > | 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 | 8384512
> >
> > > |
> > > | 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
> >
> > > |
> > > | 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 | 67108864
> >
> > > |
> > > | max_binlog_cache_size | 18446744073709547520
> >
> > > |
> > > | max_binlog_size | 1073741824
> >
> > > |
> > > | max_connect_errors | 10
> >
> > > |
> > > | max_connections | 2048
> >
> > > |
> > > | max_delayed_threads | 20
> >
> > > |
> > > | max_error_count | 64
> >
> > > |
> > > | max_heap_table_size | 16777216
> >
> > > |
> > > | 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 | 16384
> >
> > > |
> > > | net_read_timeout | 30
> >
> > > |
> > > | net_retry_count | 10
> >
> > > |
> > > | net_write_timeout | 60
> >
> > > |
> > > | new | OFF
> >
> > > |
> > > | old_passwords | ON
> >
> > > |
> > > | open_files_limit | 10240
> >
> > > |
> > > | optimizer_prune_level | 1
> >
> > > |
> > > | optimizer_search_depth | 62
> >
> > > |
> > > | pid_file |
> > /var/run/mysqld/mysqld.pid
> > > |
> > > | plugin_dir |
> >
> > > |
> > > | 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 | 0
> >
> > > |
> > > | query_cache_type | ON
> >
> > > |
> > > | query_cache_wlock_invalidate | OFF
> >
> > > |
> > > | query_prealloc_size | 8192
> >
> > > |
> > > | range_alloc_block_size | 4096
> >
> > > |
> > > | read_buffer_size | 131072
> >
> > > |
> > > | read_only | OFF
> >
> > > |
> > > | read_rnd_buffer_size | 262144
> >
> > > |
> > > | 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/lib/mysql/mysql.sock
> > > |
> > > | sort_buffer_size | 2097144
> >
> > > |
> > > | 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
> >
> > > |
> > > | table_cache | 64
> >
> > > |
> > > | table_lock_wait_timeout | 50
> >
> > > |
> > > | table_type | MyISAM
> >
> > > |
> > > | thread_cache_size | 0
> >
> > > |
> > > | thread_stack | 262144
> >
> > > |
> > > | timed_mutexes | OFF
> >
> > > |
> > > | tmp_table_size | 33554432
> >
> > > |
> > > | transaction_alloc_block_size | 8192
> >
> > > |
> > > | transaction_prealloc_size | 4096
> >
> > > |
> > > | tx_isolation | REPEATABLE-READ
> >
> > > |
> > > | updatable_views_with_limit | YES
> >
> > > |
> > > | wait_timeout | 28800
> >
> > > |
> > >
> > +---------------------------------+---------------------------
> > ---------------------------------+
> > >
> > >
> > > mysql> show status;
> > > +-----------------------------------+-----------+
> > > | Variable_name | Value |
> > > +-----------------------------------+-----------+
> > > | Aborted_clients | 34 |
> > > | Aborted_connects | 1 |
> > > | Binlog_cache_disk_use | 0 |
> > > | Binlog_cache_use | 0 |
> > > | Bytes_received | 128 |
> > > | Bytes_sent | 162 |
> > > | 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 | 0 |
> > > | 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 | 0 |
> > > | Com_show_errors | 0 |
> > > | Com_show_fields | 0 |
> > > | 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 | 0 |
> > > | Com_show_triggers | 0 |
> > > | Com_show_variables | 0 |
> > > | 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 | 942289 |
> > > | Created_tmp_disk_tables | 0 |
> > > | Created_tmp_files | 93 |
> > > | Created_tmp_tables | 1 |
> > > | Delayed_errors | 0 |
> > > | Delayed_insert_threads | 0 |
> > > | Delayed_writes | 0 |
> > > | 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 | 0 |
> > > | Handler_rollback | 0 |
> > > | Handler_savepoint | 0 |
> > > | Handler_savepoint_rollback | 0 |
> > > | Handler_update | 0 |
> > > | Handler_write | 132 |
> > > | Innodb_buffer_pool_pages_data | 25 |
> > > | Innodb_buffer_pool_pages_dirty | 0 |
> > > | Innodb_buffer_pool_pages_flushed | 1 |
> > > | Innodb_buffer_pool_pages_free | 487 |
> > > | 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 | 560 |
> > > | Innodb_buffer_pool_reads | 18 |
> > > | Innodb_buffer_pool_wait_free | 0 |
> > > | Innodb_buffer_pool_write_requests | 1 |
> > > | Innodb_data_fsyncs | 7 |
> > > | Innodb_data_pending_fsyncs | 0 |
> > > | Innodb_data_pending_reads | 0 |
> > > | Innodb_data_pending_writes | 0 |
> > > | Innodb_data_read | 2592768 |
> > > | Innodb_data_reads | 31 |
> > > | Innodb_data_writes | 7 |
> > > | Innodb_data_written | 35328 |
> > > | Innodb_dblwr_pages_written | 1 |
> > > | Innodb_dblwr_writes | 1 |
> > > | Innodb_log_waits | 0 |
> > > | Innodb_log_write_requests | 0 |
> > > | Innodb_log_writes | 2 |
> > > | Innodb_os_log_fsyncs | 5 |
> > > | Innodb_os_log_pending_fsyncs | 0 |
> > > | Innodb_os_log_pending_writes | 0 |
> > > | Innodb_os_log_written | 1024 |
> > > | Innodb_page_size | 16384 |
> > > | Innodb_pages_created | 0 |
> > > | Innodb_pages_read | 25 |
> > > | Innodb_pages_written | 1 |
> > > | 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 | 25 |
> > > | Innodb_rows_updated | 0 |
> > > | Key_blocks_not_flushed | 0 |
> > > | Key_blocks_unused | 4158 |
> > > | Key_blocks_used | 6694 |
> > > | Key_read_requests | 696459654 |
> > > | Key_reads | 1824563 |
> > > | Key_write_requests | 4009303 |
> > > | Key_writes | 118979 |
> > > | Last_query_cost | 0.000000 |
> > > | Max_used_connections | 166 |
> > > | Not_flushed_delayed_rows | 0 |
> > > | Open_files | 69 |
> > > | Open_streams | 0 |
> > > | Open_tables | 64 |
> > > | Opened_tables | 0 |
> > > | Prepared_stmt_count | 0 |
> > > | 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 |
> > > | Queries | 24309937 |
> > > | Questions | 2 |
> > > | Rpl_status | NULL |
> > > | Select_full_join | 0 |
> > > | Select_full_range_join | 0 |
> > > | Select_range | 0 |
> > > | Select_range_check | 0 |
> > > | Select_scan | 1 |
> > > | 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 | 33647849 |
> > > | Table_locks_waited | 1979 |
> > > | Tc_log_max_pages_used | 0 |
> > > | Tc_log_page_size | 0 |
> > > | Tc_log_page_waits | 0 |
> > > | Threads_cached | 0 |
> > > | Threads_connected | 85 |
> > > | Threads_created | 942288 |
> > > | Threads_running | 2 |
> > > | Uptime | 16392 |
> > > | Uptime_since_flush_status | 24309937 |
> > > +-----------------------------------+-----------+
> > >
> > >
> > > Best regards,
> > > Paul
> > >
> > >> -----Original Message-----
> > >> From: opencms-dev-bounces at opencms.org
> > >> [mailto:opencms-dev-bounces at opencms.org] On Behalf Of
> > HERNAN - TFSLA
> > >> Sent: 2. mai 2011 14:19
> > >> To: opencms-dev at opencms.org
> > >> Subject: Re: [opencms-dev]
> > >> MySQLNonTransientConnectionException - too many connections
> > >>
> > >>
> > >> Please post your:
> > >> * opencms.properties
> > >> * mysql variables status (show status;)
> > >> * j/connector version
> > >>
> > >> Most likely you have your opencms.properties 'default' and is
> > >> not getting
> > >> along with some mysql configuration such as wait_timeout.
> > >>
> > >> Regards
> > >> Hernán.
> > >>
> > >>
> > >> Paul-Inge Flakstad wrote:
> > >> >
> > >> > Hi guys,
> > >> >
> > >> > We recently launched our primary website on OpenCms (yay!).
> > >> It is curretly
> > >> > at ~3000 pageviews a day. Not _that_ much traffic, but
> > >> every so often (way
> > >> > too often), it "hangs" - often for as long as a minute or more.
> > >> >
> > >> > This is what happens:
> > >> >
> > >> >
> > com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Data
> > >> > source rejected establishment of connection, message from
> > >> server: "Too
> > >> > many connections"
> > >> > at
> > >> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
> > >> > at
> com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1070)
> > >> > at
> > >> com.mysql.jdbc.Connection.createNewIO(Connection.java:2748)
> > >> > at
> com.mysql.jdbc.Connection.<init>(Connection.java:1553)
> > >> > at
> > >> >
> > >> com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriv
> > >> er.java:285)
> > >> > at
> > >> java.sql.DriverManager.getConnection(DriverManager.java:582)
> > >> > at
> > >> java.sql.DriverManager.getConnection(DriverManager.java:185)
> > >> > at
> > >> >
> > >> org.apache.commons.dbcp.DriverManagerConnectionFactory.createC
> > >> onnection(DriverManagerConnectionFactory.java:65)
> > >> > at
> > >> >
> > >> org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(P
> > >> oolableConnectionFactory.java:294)
> > >> > at
> > >> >
> > >> org.apache.commons.pool.impl.GenericObjectPool.borrowObject(Ge
> > >> nericObjectPool.java:1148)
> > >> > at
> > >> >
> > >>
> >
> org.apache.commons.dbcp.PoolingDriver.connect(PoolingDriver.java:176)
> > >> > at
> > >> java.sql.DriverManager.getConnection(DriverManager.java:582)
> > >> > at
> > >> java.sql.DriverManager.getConnection(DriverManager.java:207)
> > >> > at
> > >> >
> > >> org.opencms.db.CmsSqlManager.getConnectionByUrl(CmsSqlManager.
> > >> java:104)
> > >> > at
> > >> >
> > >> org.opencms.db.generic.CmsSqlManager.getConnection(CmsSqlManag
> > >> er.java:231)
> > >> > at
> > >> >
> > >> org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.
> > >> java:1931)
> > >> > ... 75 more
> > >> >
> > >> > The exception was not present in the several months of
> > >> development and
> > >> > beta testing, so it's no doubt the traffic that's causing
> > >> it. I've never
> > >> > seen this before, but then again, I've been working mostly
> > >> on smaller
> > >> > sites with much less traffic.
> > >> >
> > >> > After learning of the setting in MySQL, I have (just now) set
> > >> > max_connections to 2048. No max_connections was present in
> > >> my.cnf from
> > >> > before, so I'm guessing it was 100 (the default value).
> > >> >
> > >> > Will this fix things? Should I do anything else, in order
> > >> to allow many
> > >> > concurrent users / avoid this exception?
> > >> >
> > >> > Best regards,
> > >> > Paul
> > >> >
> > >> > _______________________________________________
> > >> > This mail is sent to you from the opencms-dev mailing list
> > >> > To change your list options, or to unsubscribe from the
> > >> list, please visit
> > >> > http://lists.opencms.org/mailman/listinfo/opencms-dev
> > >> >
> > >> >
> > >>
> > >> --
> > >> View this message in context:
> > >> http://old.nabble.com/MySQLNonTransientConnectionException---t
> > > oo-many-connections-tp31521630p31523002.html
> > >> Sent from the OpenCMS - Dev mailing list archive at Nabble.com.
> > >>
> > >>
> > >> _______________________________________________
> > >> This mail is sent to you from the opencms-dev mailing list
> > >> To change your list options, or to unsubscribe from the list,
> > >> please visit
> > >> http://lists.opencms.org/mailman/listinfo/opencms-dev
> > >
> > > _______________________________________________
> > > This mail is sent to you from the opencms-dev mailing list
> > > To change your list options, or to unsubscribe from the
> > list, please visit
> > > http://lists.opencms.org/mailman/listinfo/opencms-dev
> > >
> > >
> >
> > --
> > View this message in context:
> > http://old.nabble.com/MySQLNonTransientConnectionException---t
> oo-many-connections-tp31521630p31527995.html
> > Sent from the OpenCMS - Dev mailing list archive at Nabble.com.
> >
> >
> > _______________________________________________
> > This mail is sent to you from the opencms-dev mailing list
> > To change your list options, or to unsubscribe from the list,
> > please visit
> > http://lists.opencms.org/mailman/listinfo/opencms-dev
>
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list,
> please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev
>
More information about the opencms-dev
mailing list