Communications link failure. Last packet sent to the server was 0 ms ago.

[melinite]

This is not a railo issue, but an issue with jdbc and mysql.

googling it resulting in many people posting about it but no solid
solution.

can anyone help out?

[melinite]

solved.

[melinite]

it had to do with these mysql variables:

wait_timeout
interactive_timeout
net_write_timeout
net_read_timeout

my wait_timout,net_read_timeouts, net_write_timeouts were too low at
30seconds.

Some analysis queries were taking about 45 seconds and mysql closed
the connection whereas the jdbc driver was still assuming it was
opened.

increased those values to 60 resolved the issue.


Btw, if anyone is running linux based mysql's, I highly recommend the
ourdelta.org builds.

www.ourdelta.org

They are patched for better production logging, better innodb logging
and control, among other things.

If you are doing heavy transactions and have a 64bit dedicated mysql
server, look at the Percona Xtradb mysql build.
http://www.percona.com/docs/wiki/percona-xtradb:start.

Of course word of caution, please, please, please make sure you fully
test before upgrading to any new DB version.
Unless you have a solid upgrade path for db upgrades, i would
recommend against upgrading on production servers.

[Ryan Letulle]

I switched over to Railo CentOS MySQL about 30 days ago and it seems like I am not getting the response I was getting with CF7 Win 2003 MySQL.  It seems like I am waiting on the db results because my page loads until I get to the db results table and then I notice a significant pause waiting for the results.

BTW The linux machine is 4 times the win 2003 machine in memory and at least as much processor.

I am wondering if I could tweek a setting to speed it up?

Any ideas? 

--
Ryan

[Andrew Penhorwood]

Ryan,

Are the MySQL installs basically the same?  Same version, memory, etc.

Andrew Penhorwood

-- 

Best regards,

 Andrew                            mailto:and...@coldbits.com

[Ryan Letulle]

The centos would be the latest, win from about 2 1/2 years ago.  Both default installs.  MySQL was installed using yum for centos.  

It's odd that I have a 1 second pause waiting for a db response even if it's only 20 records.  It's bearable but it seems like it could be faster.

I'm running on a Liquid computing virtual server setup right now which is all new to me but everything else seems very responsive just the db results that are a little slow.

Are there optimal config settings that I could benchmark against.  I've never noticed this before with CF or MySQL.

thx  

--
Ryan

[Andrew Penhorwood]

Ryan,

Hum, not really sure.  If you work with MySQL server directly (via some type of admin GUI) do you see the same speed issues?

Andrew Penhorwood

[Ryan Letulle]

No, not really, and I now hit the server remotely whereas I used to remote desktop into win.  Maybe it's not a MySQL issue?  Maybe it's an issue between Railo and MySQL.

--
Ryan

[Chris Velevitch]

On Fri, Apr 17, 2009 at 13:05, Ryan Letulle <bayo...@gmail.com> wrote:
> ...  Maybe it's an issue
> between Railo and MySQL.

Have you looked at the logs for anything usual?

Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
www.apugs.org.au

Adobe Platform Users Group, Sydney
April meeting: Mate Application Framework
Date: Mon 27th April 6pm for 6:30 start
Details and RSVP on http://groups.adobe.com/posts/353d7bc050

[Andrew Penhorwood]

Ryan,

Yes it could be related to the JDBC driver.  Not sure what Railo is using. I always install my own on CF.

Andrew Penhorwood

[Ryan Letulle]

@Chris 

Haven't had a MySQL log entry since 090403

@Andrew

I'll check into the JDBC driver  to see if I can do better

thx

--
Ryan

[melinite]

upgrade to connector/j 5.1.7
http://dev.mysql.com/downloads/connector/j/5.1.html

then in your railo admin or railo-web.xml.cfm file under WEB-INF/railo
use class="com.mysql.jdbc.driver" dsn="jdbc:mysql://{host}:{port}/
{database}?zeroDateTimeBehavior=convertToNull"

you can also do this under railo admin under OTHER JDBC driver

also can you please dump your SHOW VARIABLES for mysql.

and what version of mysql did yum install provide?

> >  Andrew                            mailto:and...@coldbits.com<and...@coldbits.com>

>
> > --
>
> > Ryan
>
> > --
>
> > Best regards,
>

> >  Andrew                            mailto:and...@coldbits.com<and...@coldbits.com>

>
> > --
>
> > Ryan
>
> > --
>
> > Best regards,
>

> >  Andrew                            mailto:and...@coldbits.com<and...@coldbits.com>
>
> --
> Ryan

[melinite]

the zerodatetimebehavior allows null datetime fields to be returned in
a readable format for the jdbc.

On Apr 16, 11:24 pm, melinite <melin...@gmail.com> wrote:
> upgrade to connector/j 5.1.7http://dev.mysql.com/downloads/connector/j/5.1.html

[Ryan Letulle]

MYSQL Version: 

5.045 via TCP/IP 

Show Variables Dump:

"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","utf8"

"character_set_connection","utf8"

"character_set_database","latin1"

"character_set_filesystem","binary"

"character_set_results","utf8"

"character_set_server","latin1"

"character_set_system","utf8"

"character_sets_dir","/usr/share/mysql/charsets/"

"collation_connection","utf8_general_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_ssl","DISABLED"

"have_query_cache","YES"

"have_raid","NO"

"have_rtree_keys","YES"

"have_symlink","YES"

"hostname","localhost.localdomain"

"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_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","8388600"

"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","18446744073709551615"

"max_binlog_size","1073741824"

"max_connect_errors","10"

"max_connections","100"

"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","9223372036854775807"

"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"

"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","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"

"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","CDT"

"table_cache","64"

"table_lock_wait_timeout","50"

"table_type","MyISAM"

"thread_cache_size","0"

"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.45"

"version_bdb","Sleepycat Software: Berkeley DB 4.1.24: (July  4, 2007)"

"version_comment","Source distribution"

"version_compile_machine","x86_64"

"version_compile_os","redhat-linux-gnu"

"wait_timeout","28800"

thx, I'll try the jdbc suggestions.

--
Ryan

[melinite]

first of all i would recommend you manual install mysql to version
5.077 at least.
The version in your yum repository is outdated. your version is 2-3
years old i believe.
Probably not the issue though, so don't sweat it.

Add log-slow-queries to your my.cnf. Possible query execution
bottleneck.


Also try using 127.0.0.1 in your connection dsn if you haven't
already.

Only do this if your mysql is on same box.

under railo-web-xml.cfm you will see your db connection you created in
xml format.
change host="localhost" to host="127.0.0.1"
restart your servlet container and see if that helps.

Sometimes mysql has dns resolve issues. If that helps then I suggest
adding
skip-name-resolve

to your my.cnf

Caution though if you add that and reboot mysql root@localhost will
not have access
you will need to create ro...@127.0.0.1 user


and just curious are you running a 64bit jvm?
Your show variables dump doesn't seem out of the ordinary.
How much ram is allocated to your vps?

> ...
>
> read more »

[Ryan Letulle]

I just noticed in the web admin that Railo only reports 248mb of memory.  Is this correct or can I beef that up for better performance with MySQL.

I am running a 64 bit jvm. 

1.6.0_13 (Sun Microsystems Inc.)  

Railo community 3.0.2.001 final

Linux (2.6.18-92.1.22.el5)

Liquid Computing Dedicated Virtual Server:

CentOS Extras Package / 2 Gig of memory / 2.6 GHz Opteron / 20 GB SATA Beast Storage.

I've been thinking that most of the site is dynamic and created from the database.  I guess it's only when I am looping that I see the pause.  I typically loop through the records and lay them out in a table (i.e. users, etc) and that's when I see a "slight" delay.  It just seems like it could be faster to me.  Maybe I have my sights set too high.

thx  

--
Ryan

[Ryan Letulle]

Railo Debugging:

I have some really small queries running for 2-3 seconds.  They are joining tables and filtering on some text fields but there are very few records in the tables.  I haven't seen this delay before.  Maybe there is something off with my jdbc driver.   

--
Ryan

[melinite]

the jvm memory reported by railo shouldn't cause delay in mysql
connections unless your database connection pool is filled by all the
queries your running.

this is my startup params for my jetty instance on Sun JVM 6u11 64bit
Centos, again these are optimal for my server so far.

-server -Xmx256m -Xms256m -XX:PermSize=128m -XX:MaxPermSize=128m -
Dsun.rmi.dgc.client.gcInterval=600000 -
Dsun.rmi.dgc.server.gcInterval=600000

Again these probably won't help your sql connections. You said you
weren't experiencing these delays under win server. Are the queries
the same?

If yes, then I'm assuming its that your jdbc connections are idling
out and the timeout of mysql isn't short enough to kill them, thus the
delay when you get to your small queries.

Try adjusting these variables in mysql

wait_timeout=120
interactive_timeout=120
net_read_timeout=60
connect_timeout=45

also you have query cache on but no size
set
query_cache_size=8M (you most likely will want to tune this later
if this helps, unless you are explicitly stating SQL Cache directives
in your sql.)

Are you using primarily MYISAM or INNODB tables, your variables dump
show standard config for MYISAM, if you are doing INNODB transaction
tables then i bet your innodb log file size is too small and mysql is
rotating the ib0 data files too much.

In the end it could just be some bad queries that somehow your linux
instance just isn't responding well too. I can tell you from my
experience, most mysql deployments in my experience perform much more
stable in Linux environment.

So finally try replacing your jdbc driver to the latest connector/j
mysql, as I mentioned previously. Stop your servlet container
instance, delete the old mysql connector-j WGET the new one, restart
and adjust your railo dsn connections as i mentioned before.

odd that you should have a degrading experience with mysql when you
moved from windows to linux.

On Apr 17, 12:40 pm, Ryan Letulle <bayous...@gmail.com> wrote:
> Railo Debugging:
> I have some really small queries running for 2-3 seconds.  They are joining
> tables and filtering on some text fields but there are very few records in
> the tables.  I haven't seen this delay before.  Maybe there is something off
> with my jdbc driver.
>
>
>
> On Fri, Apr 17, 2009 at 11:28 AM, Ryan Letulle <bayous...@gmail.com> wrote:
> > I just noticed in the web admin that Railo only reports 248mb of memory.
> >  Is this correct or can I beef that up for better performance with MySQL.
>
> > I am running a 64 bit jvm.  1.6.0_13 (Sun Microsystems Inc.)
> > Railo community 3.0.2.001 final
> > Linux (2.6.18-92.1.22.el5)
>
> > Liquid Computing Dedicated Virtual Server:
> > CentOS Extras Package / 2 Gig of memory / 2.6 GHz Opteron / 20 GB SATA
> > Beast Storage.
>
> > I've been thinking that most of the site is dynamic and created from the
> > database.  I guess it's only when I am looping that I see the pause.  I
> > typically loop through the records and lay them out in a table (i.e. users,
> > etc) and that's when I see a "slight" delay.  It just seems like it could be
> > faster to me.  Maybe I have my sights set too high.
>
> > thx
>

> > On Thu, Apr 16, 2009 at 11:23 PM, melinite <melin...@gmail.com> wrote:
>
> >> first of all i would recommend you manual install mysql to version
> >> 5.077 at least.
> >> The version in your yum repository is outdated. your version is 2-3
> >> years old i believe.
> >> Probably not the issue though, so don't sweat it.
>
> >> Add log-slow-queries to your my.cnf. Possible query execution
> >> bottleneck.
>
> >> Also try using 127.0.0.1 in your connection dsn if you haven't
> >> already.
>
> >> Only do this if your mysql is on same box.
>
> >> under railo-web-xml.cfm you will see your db connection you created in
> >> xml format.
> >> change host="localhost" to host="127.0.0.1"
> >> restart your servlet container and see if that helps.
>
> >>  Sometimes mysql has dns resolve issues. If that helps then I suggest
> >> adding
> >> skip-name-resolve
>
> >> to your my.cnf
>
> >> Caution though if you add that and reboot mysql root@localhost will
> >> not have access

> >> you will need to create r...@127.0.0.1 user

> ...
>
> read more »