[debian-mysql] Bug#430684: Calculating the timeout instead of using a configuration setting.

[Paul Veldema]

Hello,

Here some more suggestions and a flexible solution that does not need an 
extra configuration setting.

Since there is a relation between the timeout and the memory to be 
allocated by mysql, a timeout setting is not nescessary.
Simply calculate the the timeout, there are two options:

    * based on the amount of memory to be allocated (info already in the
      my.cnf)
    * based on the amount of memory a system has avalable.


Advantages of calculating the timeout:

    * With memory related configuration changes you do not get 'timeout
      setting' related errors (the timeout becomes machine limits
      related instead of configuration related).
    * Calculating the timeout saves the database maintainer some trial
      and error time trying to get a working timeout for a given
      configuration (possible downtime).
    * Saves time in environments with many different my.cnf configurations.
    * Future proof (256GByte machines anyone).


I have done some additional testing to get some startup stats with 
different memory settings (no mysql clustering):

Amount memory
	minimum needed timeout setting
1
	3
4
	4
8
	6
16
	11
23
	14
32
	18
40
	22
51
	28


Based on these stats you can make a calculation for the timeout (sysmem 
or my.cnf based). In the my.cnf you can specify memory al 1000M or 
1000000 (for Kb) or 1G etc. not something you would want to handle in a 
script. Which is why I did not even try the my.cnf road. The easy way is 
the system memory based solution I think, here is the script addition ( 
based on our startup stats ):

    KB_PER_GB=1024000
    TIMEOUT_OFFSET=4
    MEMORY_ALLOCATION_PER_SEC_IN_KB=`echo $(( 2 * $KB_PER_GB ))`
   
    mysql_startup_timeout() {
        memtotal="`cat /proc/meminfo | grep MemTotal | cut -d ':' -f 2 | 
sed 's/ //g' | cut -d 'k' -f 1`"
        # round memory royally upward for calc in gigabytes.
        memtotal=`expr $memtotal + 700000`
        timeout=`expr $memtotal  / $MEMORY_ALLOCATION_PER_SEC_IN_KB`
        timeout=`expr $timeout + $TIMEOUT_OFFSET`
        if test $timeout -gt 14; then
            echo $timeout
        else
            echo 14
        fi
    }


Combine that with the suggestion from sean finney about using 'seq' and 
you get:

    /usr/bin/mysqld_safe > /dev/null 2>&1 &
        # 6s was reported in #352070 to be too few when using ndbcluster
        STARTUP_TIMEOUT=`mysql_startup_timeout`
        for i in `seq $STARTUP_TIMEOUT`; do
            sleep 1
            if mysqld_status check_alive nowarn ; then break; fi
            log_progress_msg "."


Neat, flexible and scaleable. Memory speed only improves with new 
architectures/memory types so the calculated timeouts should be relaxed 
enough (our Xeon based server does not have the most scalable memory 
architecture).
Should someone submit a related bug in the future concerning a machine 
with slower memory you simply lower the MEMORY_ALLOCATION_PER_SEC_IN_KB 
value and solve the problem for all machines with slower memory.