Hi,
I would like to join in and give another view.
I feel that the connection errors are all related to the relationship
between the hibernate pooling stratergy in place and mysql db.
If not specified in the hibernate properties hibernate uses its
default pooling. This is not production worthy.
So it can be disabled,
<snip>
Which ConnnectionProvider are you using? If its not
DatasourceConnectionProvider, then it is probably doing connection
pooling. This can cause problems in certain environments. Pooling may
be disabled for DriverManagerConnectionProvider by setting
hibernate.connection.pool_size=0 in hibernate.properties.
</snip>
or better a third party pool can be used like C3PO.
Using the Hibernate SessionFactory.openSession() method as it is done
in Mifos HibernateUtil to obtain connections, should be causing
Hibernate to:
<snip>
obtain (and pool) connections using java.sql.DriverManager
</snip>
The question which I ask myself is why if a new JDBC connection is
created per openSession() should there be connection errors, to me
this is an indication of pooling happening under the covers. So
looking at
<snip>
When I leave Hibernate running overnight, I come back and find
that it can no longer connect to the database.
(This is particularly common in the case of MySQL, wich times out
JDBC connections after a short time.)
</snip>
and
<snip>
<!-- The JDBC connection url for connecting to your MySQL dB. The
autoReconnect=true argument to the url makes sure that the mm.mysql JDBC
Driver will automatically reconnect if mysqld closed the connection.
mysqld by default closes idle connections after 8 hours.-->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/venispringtestDB?autoReconnect=true&
useUnicode=true&characterEncoding=utf-8</value>
</parameter>
</snip>
note autoReconnect=true
I am lead to believe this to be a strong contender for the errors we see.
Thanks,
Dion
On Nov 16, 2007 12:01 AM, Aliya Walji <[EMAIL PROTECTED]> wrote:
> > I'm sure we can put something into place in addition to the existing
> > monitoring. --aB
>
> In the summer time, Greg Steffensen put together a python script to gather
> periodic statistics from the MySQL admin tool in order to monitor the DB and
> troubleshoot issues.
>
> This tool provides statistics on the number of threads running in the DB each
> time it polls. This could be useful for getting more information into the
> issue that Naganand raised.
>
> The tool can be run on a remote server, but it requires that the 'mysqladmin'
> tool is available on your local machine (this is in the 'bin' directory of
> your local MySQL server installation).
>
> Here are some instructions on how to use the tool:
>
> 1. Install python 2.5.1 or greater on the machine you are going to run the
> script from (note that the tool can be run remotely, so python and the script
> do not have to be installed on your DB server).
>
> 2. Save the mysqlmonitor.py python script (attached) on your machine
>
> 3. Create a shortcut link to your mysqladmin.exe file (MySQL Administrator
> program) in the same location as the python script. This file is usually in
> the 'bin' directory of your local MySQL Server installation.
>
> 4. Run the mysqlmonitor.py tool in the command line as follows:
>
> mysqlmonitor.py -s <URL of remote or local MySQL server> -u <mysql username>
> -p <mysql password> -i <polling interval in seconds> -f <log file name to
> where results will be written>
>
> If you don't specify the server, it will assume localhost. If you don't
> specify the filename, it will write to 'mysqlstats.log' in the current
> directory, if you don't specify polling interval, it will poll at 10 second
> intervals, I think.
>
> Running with just the "-h" option will give information on how to use the
> tool.
>
> Note also that the log files get large very fast if you poll frequently so
> you will only want to run this for short period of time if you need to pull
> stats frequently.
>
> Amy - I had provided GK with this information in the summer and installed on
> their test machines, so it may still be there to use. Naganand, JS or
> Praveen may still remember where it is located.
>
> Attached to this email is the mysqlmonitor.py script and below is sample
> output from the log file that it provides so you can get a sense for the
> information it provides. I think the information should also be up on
> Mifos.org somewhere (I had created a troubleshooting page with this
> information). If it's not, I will post it when I next have an internet
> connection.
>
> Thanks,
>
> Aliya
>
> # mysqlmonitor log started 2007-11-15 16:49:07.656000
> # host localhost
> # dbuser root
>
> # mysqlmonitor output 2007-11-15 16:49:08.312000
> Aborted_clients 0
> Aborted_connects 0
> Binlog_cache_disk_use 0
> Binlog_cache_use 0
> Bytes_received 97
> Bytes_sent 80
> 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 0
> 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 2
> Created_tmp_disk_tables 0
> Created_tmp_files 5
> 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 3
> Handler_read_key 0
> Handler_read_next 0
> Handler_read_prev 0
> Handler_read_rnd 0
> Handler_read_rnd_next 7
> Handler_rollback 0
> Handler_savepoint 0
> Handler_savepoint_rollback 0
> Handler_update 0
> Handler_write 0
> Innodb_buffer_pool_pages_data 101
> Innodb_buffer_pool_pages_dirty 0
> Innodb_buffer_pool_pages_flushed 0
> Innodb_buffer_pool_pages_free 985
> Innodb_buffer_pool_pages_latched 0
> Innodb_buffer_pool_pages_misc 2
> Innodb_buffer_pool_pages_total 1088
> Innodb_buffer_pool_read_ahead_rnd 1
> Innodb_buffer_pool_read_ahead_seq 0
> Innodb_buffer_pool_read_requests 2401
> Innodb_buffer_pool_reads 50
> 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 3837952
> Innodb_data_reads 111
> 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 101
> 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 8981
> Key_blocks_used 0
> Key_read_requests 0
> Key_reads 0
> Key_write_requests 0
> Key_writes 0
> Last_query_cost 0.000000
> Max_used_connections 1
> Not_flushed_delayed_rows 0
> Open_files 12
> Open_streams 0
> Open_tables 6
> Opened_tables 12
> 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
> Questions 1
> 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 12
> Table_locks_waited 0
> Tc_log_max_pages_used 0
> Tc_log_page_size 0
> Tc_log_page_waits 0
> Threads_cached 0
> Threads_connected 1
> Threads_created 1
> Threads_running 1
> Uptime 23
> Uptime_since_flush_status 23
>
> # process list
> +----+------+----------------+----+---------+------+-------+------------------+
> | Id | User | Host | db | Command | Time | State | Info
> |
> +----+------+----------------+----+---------+------+-------+------------------+
> | 2 | root | localhost:3561 | | Query | 0 | | show processlist
> |
> +----+------+----------------+----+---------+------+-------+------------------+
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
