When using an innodb_buffer_pool_size of 24GByte (or more) I get
a "failed!" message starting mysql with '/etc/init.d/mysql'.
Mysql starts anyway, however /etc/mysql/debian-start is not started.
This happens because of a 14 second timeout in the /etc/init.d/mysql script.

To reproduce:
Use an x86_64 machine with the 64 bit version of etch and 32GB or more.
We use an Dell PE6800, quad quad-core Xeon with 64GByte RAM.

Transcript with our own settings:

 mysqlmachine-etch64:~# /etc/init.d/mysql stop
 mysqlmachine-etch64:~# vim /etc/mysql/my.cnf
     # add <any other innodb settings>
     # Add/Set the innodb_buffer_pool_size to more than 24GB:
     # in this case 80% of the 64GByte of the memory our (Dell PE6800)
server has.
     innodb_buffer_pool_size         = 51000M

 mysqlmachine-etch64:~# /etc/init.d/mysql start
 Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
 mysqlmachine-etch64:~#

Some extra system info:
 mysqlmachine-etch64:~# mysql -u root -p
 Enter password:
 mysql> show variables like '%version%';
+-------------------------+--------------------------+
| Variable_name           | Value                    |
+-------------------------+--------------------------+
| protocol_version        | 10                       |
| version                 | 5.0.32-Debian_7etch1-log |
| version_comment         | Debian etch distribution |
| version_compile_machine | x86_64                   |
| version_compile_os      | pc-linux-gnu             |
+-------------------------+--------------------------+

 mysqlmachine-etch64:~# uname --all
Linux mysqlmachine-etch64 2.6.18-4-amd64 #1 SMP Fri May 4 00:37:33 UTC
2007 x86_64 GNU/Linux


Proposed fix:
 mysqlmachine-etch64:/etc/init.d:~# diff adjusted-mysql mysql
 <           # 14s was reported in #? to be too few when using x86_64
with large innodb_buffer_pool_size
 <           for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
21 22 23 24 25 26 27 28; do
 ---
 >           for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do

 mysqlmachine-etch64:/etc/init.d# /etc/init.d/adjusted-mysql restart
 Stopping MySQL database server: mysqld.
 Starting MySQL database server: mysqld . . . . . . . . . . . . . . . .
. . . . . . . . . . ..
 Checking for corrupt, not cleanly closed and upgrade needing tables..

28 seconds was enough for mysql to allocate a 51 GByte
innodb_buffer_pool, that should be enough for most.
With 14 seconds the maximum innodb_buffer_pool_size where the table
checker would start was about 23GByte.

We've actually been working on this upstream and have put in support to
specify the timeout value in the my.cnf file, as there is really no
"perfect" timeout value here.

I'll see if I can forward/point out some of the work we've done...

Monty

Paul Veldema wrote:

heya,

alternatively (and much easier probably), the init script code in question
could be replaced with something more flexible.  i.e.:

            /usr/bin/mysqld_safe > /dev/null 2>&1 &
            # 6s was reported in #352070 to be too few when using ndbcluster
            for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
                sleep 1
                if mysqld_status check_alive nowarn ; then break; fi
                log_progress_msg "."

could be replaced by

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

and then let STARTUP_TIMEOUT be defined in /etc/default/mysql-server
(or /etc/sysconfig/mysql-server for the redhat folks, etc).

similar there looks t o be similar shutdown timeout too.

	sean

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.

On Wednesday 27 June 2007 15:52:02 Paul Veldema wrote:
<snip>

neat!  two comments:

- /proc/meminfo is linux specific and less general.  perhaps you could write
something with "free" or some other generic utility instead?
- your suggestion isn't mutually exclusive from the previous one i made.  it
could be made optional to specify a timeout value, and if not specified, use
the heuristic you provide, which would be the default.

but in any event, i think it's a great idea.  monty: you think this is
something mysql.com would be interested in?


	sean

sean finney wrote:

So, I like the idea in general, but unfortunately it's not the only
thing that would cause MySQL to start slowly. If we're talking about
after a crash, you've also got to play the Innodb log files into the
data file or check the MyISAM tables. In some systems, like ones we're
managing with Heartbeat/DRBD for example, you _really_ want to be able
to explicitly say "please, under no circumstances timeout, because I
need a failure or success for real" so that heartbeat doesn't go crazy.
You also want to be checking while looping to see if the process is
still around or if it has died so that you don't have to wait until the
end of the timeout, since it is possible to determine that, in fact,
things have failed already.

I like the checking memory idea. I can see if being useful as a way to
get a default timeout in the case where none has been configured.

I promise, I'll send something RSN. :) I've been trying my best to merge
the MySQL init script and the debian init script, which is made
amazingly hard by the debian extensions to the lsb init script stuff.
(grumble grumble)

Screw it. Here's my not-quite-working attempt so far to make a fully LSB
compliant init script that does all of the things we're talking about
here and also does all of the nice Debian things and returns codes
sensibly enough that heartbeat can use its outputs sensible in a cluster
manager situation. PLEASE help me figure out ways to make it better.
(especially as currently it outputs extra linefeeds and dots that I
don't want it to on debian, I think)

From sean:

Just for fun, here the version using 'free':

    mysql_startup_timeout() {
        memtotal="`free -k | head -2 | tail -1| cut -d ':' -f 2 | tr -s '[:space:]' ' ' | cut -d ' ' -f 2`"
        # 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
    }

I did not integrate this into monty's script in favour of the option of looping
indefinitely (read on).

Monty's script does that, but gives a realy large default timeout
instead, that makes me think, is there a case
you would want a timeout at all? Until now I have only seen reasons to
make the timeout larger.
 From Monty's script:

    # Default value, in seconds, afterwhich the script should timeout
    waiting
    # for server start.
    # Value here is overriden by value in my.cnf.
    # 0 means don't wait at all
    # Negative numbers mean to wait indefinitely
    service_startup_timeout=900

If using a timeout is only for specific cases, I would change the
default to -1: wait indefinitely unless a
specific timeout setting is given ('do not wait' or give a tight
timeout). That would solve this issue for any future
machine regardless of memory size or speed.

From monty:

For ease of administration debian also starts a (initially generated but
customizable and preserved during update) background script for table
checks etc. For debian this should be included, because it is existing
and desireable functionality. This would be a good option to add.

I have made a version of Monty's script that includes all the above,
without being Debian specific (see attachments). This version should
seamlessly replace debian's version (/etc/init.d/mysql,
/usr/share/mysql/debian-start.inc.sh
and the generated /etc/mysql/debian-start). I tested this with the
mysqlmachine-etch64 machine.

The mysqlmaintenance.sh is made to contain the check/upgrade
functionality as a separate script to keep the mysql.server.sh script
clean and only for 'start|stop|status". It made for easy use in any
custom startup script.

A simple diff should show all differences with Monty's mysql.server.sh
script. Any distro should be able to use the adjusted version without
any extra startup setting.

To add a /etc/mysql/debian-start like default script for other distro's
than debian, you can either adapt the mysqlmaintenance.sh script to
include the defaults or add two settings to the my.cnf:

   # Custom Maintenance Script settings that runs at startup (debian
example):
   extra-startup-script=/etc/mysql/debian-start
   extra_defaults=/etc/mysql/debian.cnf

The output (when there are no faults) look good to me, no extra line
feeds or dots the debian version
of the start script does not have. Are there specific cases where the
printing goes wrong?

With regards,
Paul

Paul Veldema wrote:

I think it's the fuzzy "is it a desktop, is it a server" question. If
it's a desktop, you probably are ok with your desktop booting quicker
and mysql not quite being done before you start. If you are a server,
you probably do not want this behavior.

I think the original choice of 900 was based on the older, buggier
script which did not detect process failure and therefore would only
stop on failure once the timeout was reached. I think that could be
revisited now that the script is more robust.

I agree. I'll see if I can convince them upstream, too.

Fantastic!

Yes. I like it.

Ok. I haven't walked through it all yet, but on first glance it looks
good. <hat employee="mysql"> I'd love to check this in upstream, or at
least start checking it in to get some feedback. However, there's a good
amount of code here, and they won't let me unless you agree to our
community contribution agreement:
http://forge.mysql.com/contribute/contributor-agreement.php
If you do, I'll start running this by the team as is for feedback. If
not, I'll try my best to do something similar without copying. Totally
your call, of course. </hat>

Looks like it'll just be going in to 5.1, regardless. Yay for dpatch!

Hmm. printing goes wrong on my old version for me in all cases... I'll
give this code a try and see if there is something silly going on.

Thanks!
Monty

I have entered the agreement and recieved the automated 'contribution' reply:

    'Signed Contributor Agreement #54 from Paul Veldema'.

With regards,
Paul Veldema

Fantastic! I saw it come through the contributions list internally. I'll
start working on getting your patch merged this week.

Thanks a lot!
Monty

Paul Veldema wrote:

just to say that the bug report suggests some upstream work may have
been done. However I have not been able to find a match on bugs,mysql.com.

This is still confirmed on debian wheezy.

System:
RAM 64 GB
CPU Dual AMD Opteron(TM) Processor 6212 (16 cores in total)
2x SSD in RAID 1 256GB
2x HDD in RAID 1 1TB

With the innodb_buffer_pool_size on 50G the timeout is way too short.

We've fixed it with the same proposed fix by the original bugposter.