MySQL 4.0.x to 4.1.x

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

MySQL 4.0.x to 4.1.x

Simon Forster
Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As  
background, we have an issue where MySQL stops responding due to too  
many connections building up. I've futzed with wait_timeout (amongst  
other things) but the version of MySQL installed on this server  
ignores the setting. In fact, AFAICT, it never times out connections.

TIA

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Brian Loomis
The password hash changes, this makes connecting from external GUI  
clients a change and you may need to use the actual value to connect.

On May 9, 2006, at 9:14 AM, Simon Forster wrote:

> Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As  
> background, we have an issue where MySQL stops responding due to  
> too many connections building up. I've futzed with wait_timeout  
> (amongst other things) but the version of MySQL installed on this  
> server ignores the setting. In fact, AFAICT, it never times out  
> connections.
>
> TIA
>
> Simon Forster
> _______________________________________________________
>  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
>  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
> _______________________________________________________
>
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Marc Pinnell-3
In reply to this post by Simon Forster
Watch your collations. Big change from 4 to 4.1, got me big time.  
There is a note about it in the release notes on the MySQL website  
(don't have it handy, sorry). Basically if I remember right, 4.0  
didn't have multiple collations (languages), but 4.1 does and if you  
don't fix your tables after conversion you will get some really weird  
search results. With some help from Miles (thanx again!), I found you  
had to set everything to Latin1_Swedish to get good results.

Marc


On May 9, 2006, at 8:14 AM, Simon Forster wrote:

> Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As  
> background, we have an issue where MySQL stops responding due to  
> too many connections building up. I've futzed with wait_timeout  
> (amongst other things) but the version of MySQL installed on this  
> server ignores the setting. In fact, AFAICT, it never times out  
> connections.
>
> TIA
>
> Simon Forster
> _______________________________________________________
>  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
>  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
> _______________________________________________________
>
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

jasonhuck
In reply to this post by Simon Forster
Also, the way some column sizes are calculated is different. I forget
the specifics, but there are some definitions that used to be
calculated in characters and are now calculated in bytes and/or vice
versa. If you're not careful you can accidentally truncate data
because of this.

Oh, and there are differences in some of the date formats, IIRC.

It's definitely not something to take lightly. The good news is, once
you're up and running on 4.1.x, moving to 5.x is a lot easier.

JLH

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
> Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As
> background, we have an issue where MySQL stops responding due to too
> many connections building up. I've futzed with wait_timeout (amongst
> other things) but the version of MySQL installed on this server
> ignores the setting. In fact, AFAICT, it never times out connections.
>
> TIA

MySql will use the same paramater for client timeout.
To solve your problems you may try to see why client doesn't send
mysql_close() method to mysql.
Also what you can do (if it's possible on your system)
Use Max connection to tell mysql to accept more than default connection.
max_connections=xxx

Then you will have to determine which time out to use
connect_timeout, wait_timeout or interactive_timeout or bad
max_allowed_packet (or modify all timeout value)

But you should try to see which problem can cause the problem (network,
software ....)

So check your Mysql Error Log.



---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Jess Mooers
In reply to this post by Simon Forster
Simon Forster <[hidden email]> wrote on Tuesday, May 9, 2006:

>Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As  
>background, we have an issue where MySQL stops responding due to too  
>many connections building up. I've futzed with wait_timeout (amongst  
>other things) but the version of MySQL installed on this server  
>ignores the setting. In fact, AFAICT, it never times out connections.
>
>TIA
>
>Simon Forster

Simon,

As previously stated by others, this is not a light upgrade.  We upgraded to MySQL 4.1 and LP 8.1 and had 3 sites that had serious data encoding issues.  After weeks of trial and error, I wrote an article and posted it on ldml.org you may want to check it out.  It is for UTF-8 encoding though.  So if you need some other encoding, it may still in theory help you out.

Encoding with Lasso 8.1 and MySQL 4.1.x
http://www.ldml.org/articles/encoding_lp81_mysql41x.lasso

Good Luck

Regards, Jess Mooers
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Landmann InterActive
1423 S. Park St., Madison, WI 53715
W 608-257-1558
www.landmanninteractive.com

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
>> Are there any obvious gotchas moving from MySQL 4.0.x to 4.1.19? As
>> background, we have an issue where MySQL stops responding due to too
>> many connections building up. I've futzed with wait_timeout (amongst
>> other things) but the version of MySQL installed on this server
>> ignores the setting. In fact, AFAICT, it never times out connections.
>>
>> TIA
>
> MySql will use the same paramater for client timeout.
> To solve your problems you may try to see why client doesn't send
> mysql_close() method to mysql.
> Also what you can do (if it's possible on your system)
> Use Max connection to tell mysql to accept more than default
> connection. max_connections=xxx
>
> Then you will have to determine which time out to use
> connect_timeout, wait_timeout or interactive_timeout or bad
> max_allowed_packet (or modify all timeout value)
>
> But you should try to see which problem can cause the problem (network,
> software ....)
>
> So check your Mysql Error Log.


So my conclusion is Before trying to a long upgrade to MySql 4.1.x try to
review your current Config.
And check also that the new parameters are running

Chesk SHOW VARIABLES and SHOW STATUS.


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster
Many thanks to all who responded.

OK. So a "quick fix" of upgrading to MySQL 4.1 may not be so quick  
after all. More sleuthing req'd I guess.

As always, Olivier's waded in with some fine suggestions. Some of  
them I've already applied. As I'm lazy, I've tweaked the  
interactive_timeout to see whether that fixes any issues  
(wait_timeout has already been changed).

We seem to be falling foul of 2 issues:

1) Lasso connector not closing connections cleanly
2) MySQL not timing out old connections

If I can address one of these 2 issues, the problem should go away.

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
> Many thanks to all who responded.
>
> OK. So a "quick fix" of upgrading to MySQL 4.1 may not be so quick
> after all. More sleuthing req'd I guess.
>
> As always, Olivier's waded in with some fine suggestions. Some of
> them I've already applied. As I'm lazy, I've tweaked the
> interactive_timeout to see whether that fixes any issues
> (wait_timeout has already been changed).
>
> We seem to be falling foul of 2 issues:
>
> 1) Lasso connector not closing connections cleanly
> 2) MySQL not timing out old connections
>
> If I can address one of these 2 issues, the problem should go away.
>
> Simon Forster

Check error log and slow query log
and also your mysql status and variable

other type increase the max_connection variables.



---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster

On 9 May 2006, at 18:09, Olivier Miossec wrote:

>> We seem to be falling foul of 2 issues:
>>
>> 1) Lasso connector not closing connections cleanly
>> 2) MySQL not timing out old connections
>>
>> If I can address one of these 2 issues, the problem should go away.
>>
>> Simon Forster
>
> Check error log and slow query log

<sigh />

OK. I'll be a little less lazy and check these. You do appreciate  
that it means I've got to turn these on - at least 2 minutes work ;-)

> and also your mysql status and variable

I keep an eye on these. At the moment they're telling me nothing new.  
Sleeping connections slowly ramp up until we hit the max_connection  
limit. The various timeouts don't seem to kill off these sleeping  
connections.

> other type increase the max_connection variables.

Already done. This just delays the day of judgement.

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
And the number of Aborded client ?


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster

On 10 May 2006, at 10:33, Olivier Miossec wrote:

> And the number of Aborded client ?

Aborted_clients 0

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
>
> On 10 May 2006, at 10:33, Olivier Miossec wrote:
>
>> And the number of Aborded client ?
>
> Aborted_clients 0
>
> Simon Forster

Well in this case
If there is anything in slow query log in error logs
there is may be something else.

We should see if possible Server variables and status.




---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster

On 10 May 2006, at 13:59, Olivier Miossec wrote:

> We should see if possible Server variables and status.

As you wish (variables):

Variable_name Value
----------------------------------
back_log 50
basedir /usr/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis
tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251
danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
win1250 croat cp1257 latin5
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /var/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
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_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_table_locks ON
interactive_timeout 300
join_buffer_size 131072
key_buffer_size 16777216
language /usr/share/mysql/english/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries ON
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system ON
lower_case_table_names 2
max_allowed_packet 8387584
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 250
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 8192
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 1260
pid_file /var/mysql/web03.ultradesign.net.pid
log_error /var/log/mysqld-error.log
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
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 258048
read_only OFF
read_rnd_buffer_size 520192
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 524280
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 40
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone BST
tmp_table_size 33554432
tmpdir /var/tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.24-log
version_comment Source distribution
version_compile_os apple-darwin7.0
wait_timeout 300

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Johan Solve
In reply to this post by Simon Forster
On 5/10/06, Simon Forster <[hidden email]> wrote:
> As you wish (variables):

You might want to try with --skip-name-resolve

--
     Johan Sölve    [FSA Partner, Lasso Partner]
     Web Application/Lasso/FileMaker Developer
     MONTANIA SOFTWARE & SOLUTIONS
http://www.montania.se   mailto:[hidden email]
 (spam-safe email address, replace '-' with 'a')

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
>
> On 10 May 2006, at 13:59, Olivier Miossec wrote:
>
>> We should see if possible Server variables and status.
>
> As you wish (variables):

Your wait_timeout and interactive_timeout is set to 300 second !!



---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
> On 5/10/06, Simon Forster <[hidden email]> wrote:
>> As you wish (variables):
>
> You might want to try with --skip-name-resolve

It there was a lot of connection error and the client (lasso connector)
will get unable to connect (err. Host 'xxx.xxx.xxx.xxx' is not allowed to
connect) and you will not have a timeout (you need to be connected to have a
timeout).

And MySql has a dns cache. So If the client is connected there is a chance
that the dns is in the cache.

---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster

On 10 May 2006, at 15:05, Olivier Miossec wrote:

>>
>> On 10 May 2006, at 13:59, Olivier Miossec wrote:
>>
>>> We should see if possible Server variables and status.
>>
>> As you wish (variables):
>
> Your wait_timeout and interactive_timeout is set to 300 second !!

And your problem with this is???

The default is 8 hours (28800 seconds) and we ended up with a whole  
bunch of idle (sleeping) connections. I reduced the wait_timeout (and  
increased max_connections) in a hope that this would help fix the  
problem. It just postponed the day of judgement. Subsequently, I  
decreased interactive_timeout to see whether that would help. The  
jury is still out on that one - what with my playing around, the  
server's not been up for long enough to get a good feel for this.

I'll leave things as they are until:

1) The server's been up for one week without problem.
2) The issue crops up again at which point I can look at the logs.

Unfortunately (perhaps fortunately!?), this server is not mine. I'm  
helping to admin it as the workload for the owner has got a little  
too high - so I have to tread carefully.

Also, we're noticing a definite trend to consume all connections on a  
Tuesday and Friday. There may be a badly written perl(?) script doing  
housekeeping routines on one of the tables - but I'll probably need  
to sort out the MySQL user base to really get to the bottom of this.

ATB

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Olivier Miossec
In reply to this post by Simon Forster
>
> On 10 May 2006, at 15:05, Olivier Miossec wrote:
>
>>>
>>> On 10 May 2006, at 13:59, Olivier Miossec wrote:
>>>
>>>> We should see if possible Server variables and status.
>>>
>>> As you wish (variables):
>>
>> Your wait_timeout and interactive_timeout is set to 300 second !!
>
> And your problem with this is???
>
> The default is 8 hours (28800 seconds) and we ended up with a whole
> bunch of idle (sleeping) connections. I reduced the wait_timeout (and
> increased max_connections) in a hope that this would help fix the
> problem. It just postponed the day of judgement. Subsequently, I
> decreased interactive_timeout to see whether that would help. The
> jury is still out on that one - what with my playing around, the
> server's not been up for long enough to get a good feel for this.

For the moment we did not know why there is a timeout (you did not give any
clue about error log and slow query). So decrease only one parameter may
fail because the problem can be at connect or during the connection.


---
Olivier Miossec
AIM/ichat omiossec
--
http://www.lasso-developpeur.net
--
Need a Lasso consultant ?
(Lasso, Java, MySql, Sql Server Consultant ...)
http://www.lasso-developpeur.net/lasso-lab/
--




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: MySQL 4.0.x to 4.1.x

Simon Forster
In reply to this post by Simon Forster

On 10 May 2006, at 16:01, Olivier Miossec wrote:

>>> Your wait_timeout and interactive_timeout is set to 300 second !!
>>
>> And your problem with this is???
>>
>> The default is 8 hours (28800 seconds) and we ended up with a whole
>> bunch of idle (sleeping) connections. I reduced the wait_timeout (and
>> increased max_connections) in a hope that this would help fix the
>> problem. It just postponed the day of judgement. Subsequently, I
>> decreased interactive_timeout to see whether that would help. The
>> jury is still out on that one - what with my playing around, the
>> server's not been up for long enough to get a good feel for this.
>
> For the moment we did not know why there is a timeout (you did not  
> give any
> clue about error log and slow query). So decrease only one  
> parameter may
> fail because the problem can be at connect or during the connection.

OK. Back to my lazy routine! Initial investigations suggested that  
the sensible course of action was to reduce the wait_timeout and  
increase max_connections - which we duly did. This didn't solve the  
problem. Reducing interactive_timeout was suggested, so we did that  
and, accepting that the fix may not be so quick and simple, we turned  
on error logging. Now we wait for the errors!

Thanks for all your assistance so far - and I may be bending the  
list's collective ear in a few days time!

All the best

Simon Forster
_______________________________________________________
  LDML Ltd, 62 Pall Mall, London, SW1Y 5HZ, UK
  Tel: +44 (0)845 257 6086   Fax: +44 (0)70 9230 5247
_______________________________________________________



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
12