The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL?

Discussion in 'General Discussion' started by dazerrackers, Jun 26, 2004.

  1. dazerrackers

    dazerrackers Well-Known Member

    Joined:
    Jan 23, 2004
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    England
    I get an error sometimes saying warning to many connections. Or something like this. It's a MySQL error. So i have to re-boot the server for MySQL to start working again.
    Can anyone tell me why this is happening?
     
  2. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    what does your variable say for max_connections, which usually set in your MySQL my.cnf file ?

    Mickalo
     
  3. dazerrackers

    dazerrackers Well-Known Member

    Joined:
    Jan 23, 2004
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    England
    How do i find this file and read it? Sorry if i sound stupid.
     
  4. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    check the setting at the shell prompt mysqladmin variables and check the setting for max_connections, if it's too low, then there should be a file in the /etc folder called my.cnf, under the [mysqld] group, set the max_connections as needed, then restart MySQL and see if this helps.

    Mickalo
     
  5. zentity

    zentity Well-Known Member

    Joined:
    Jun 16, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    You probably don't want to set the max_connections higher than 500 - You don't need to reboot the server in order to correct the issue when it occurs -

    killall -9 mysqld
    /etc/init.d/mysql stop
    /etc/init.d/mysql start

    Should do it.

    You might want to check for database corruption or a bad users code.

    You can set a per user limit within /etc/my.cnf

    add the following BEFORE the max_connections limit

    max_user_connections = 20 (or a number that you set as being the max allowed per user)

    There are also several more parameters for my.cnf that you could be using to optimise your mysql server.

    Edit: You need to restart the MySQL server after making changes to my.cnf in order for them to take effect.

    Also turning off persistent mysql connections within /usr/local/lib/php.ini and restarting apache may help too, with stale open connections.

    Hope this helps.
     
    #5 zentity, Jun 26, 2004
    Last edited: Jun 27, 2004
  6. Sheldon

    Sheldon Well-Known Member

    Joined:
    Jun 7, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Canada
    any how to on optimizing mysql would be extremely helpful

    Sheldon
     
  7. dazerrackers

    dazerrackers Well-Known Member

    Joined:
    Jan 23, 2004
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    England
    Thanks guys! got it.
     
    #7 dazerrackers, Jun 27, 2004
    Last edited: Jun 27, 2004
  8. dazerrackers

    dazerrackers Well-Known Member

    Joined:
    Jan 23, 2004
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    England
    Hey, if i limit the max_user_connections to 15 will that make it so only 15 people that visit the site can connect to the DB?
     
  9. zentity

    zentity Well-Known Member

    Joined:
    Jun 16, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    It will allow up to 15 simultaneous Mysql connections, per database user. You should have this set to a reasonable number in order to prevent a user abusing your MySQL server.

    If you have a problem database, that could cause issues, maybe run something like this via cron or manually once per week too

    #!/bin/bash
    mysqlcheck --all-databases --medium-check
    mysqlcheck --all-databases -r
    mysqlcheck --all-databases -a
    mysqlcheck --all-databases -o

    To see what this all means, type

    mysqlcheck --help

    In a terminal session :)
     
  10. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    No what this means if user account someuser, can only simultaneous connect to MySQL 15 times. But this does affect all MySQL user accounts. Each is allowed a simultaneous connect of 15 or what ever this variable is set to.

    by the way, if this variable is not set in the my.cnf file, the default is 0, which means unlimited.

    Mickalo
     
  11. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    Just a quick side note. the -r option does not work with InnoDB table types :)

    Mickalo
     
  12. zentity

    zentity Well-Known Member

    Joined:
    Jun 16, 2004
    Messages:
    78
    Likes Received:
    0
    Trophy Points:
    6
    No it doesn't and it will tell you so :)

    You can also disable innodb tables within /etc/my.cnf

    [mysqld]
    skip-innodb

    If you don't need to support innodb tables (I would disable them until you get asked to support them ;-) )

    If you do run innodb you should also set the parameters within /etc/my.cnf for the files etc as they can grow quite large

    Something like (under [mysqld] )

    innodb_data_file_path = ibdata1:10M:autoextend:max:1900M
    innodb_flush_log_at_trx_commit=1
    innodb_lock_wait_timeout=50
    innodb_buffer_pool_size=200M
    innodb_additional_mem_pool_size=50M

    Could be added if you use innodb tables too :)

    You need to really specify the file limit sizes (as above) to prevent files extending past 2.1 GB which is the max file size limit on most systems.
     
  13. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    if you require transactional table support(ACID), then you will need to utilize, enable,.. the InnoDB table types :) Highly recommend now adays :)

    Mickalo
     
  14. ctbhost

    ctbhost Well-Known Member

    Joined:
    May 31, 2002
    Messages:
    139
    Likes Received:
    0
    Trophy Points:
    16
    i have created a file called checkdb with the above in it and saved it to the server but when the cron runs i get the error

    mysqlcheck: Got error: 1045: Access denied for user: 'USER@localhost' (Using password: NO) when trying to connect

    but when i run it from the shell it runs fine, any idea what im doing wrong

    BTW i have set up the cron in cpanel for the job

    0 0 1 * * /home/username/checkdb
     
    #14 ctbhost, Oct 3, 2004
    Last edited: Oct 3, 2004
Loading...

Share This Page