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.

/etc/my.cnf optimization

Discussion in 'Workarounds and Optimization' started by ES - George, Oct 9, 2011.

  1. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Here's my my.cnf configuration:
    Code:
    [mysqld]
    set-variable = max_connections=250
    
    max_user_connections = 30
    thread_concurrency = 8
    
    key_buffer = 4M
    key_buffer_size = 4M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 8M
    read_buffer_size = 8M
    sort_buffer_size = 8M
    record_buffer = 8M
    
    max_allowed_packet = 300M
    
    table_cache = 200
    thread_cache_size = 128
    
    wait_timeout = 15
    connect_timeout = 20
    interactive_timeout = 25
    max_connect_errors = 20
    
    query_cache_type = 1
    query_cache_limit = 32
    query_cache_size = 32M
    skip-locking
    log-slow-queries
    safe-show-database
    [mysqld_safe]
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 8M
    
    [myisamchk]
    key_buffer = 32M
    sort_buffer = 16M
    read_buffer = 20M
    write_buffer = 20M
    
    local-infile=0
    Here's my server info:
    Code:
    root@server [~]# cat /proc/cpuinfo
    processor       : 0
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 0
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5320.09
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 1
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 2
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.04
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 2
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 4
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.26
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 3
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 6
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.19
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 4
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 0
    cpu cores       : 4
    apicid          : 1
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.12
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 5
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 1
    cpu cores       : 4
    apicid          : 3
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.37
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 6
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 2
    cpu cores       : 4
    apicid          : 5
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.08
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    processor       : 7
    vendor_id       : GenuineIntel
    cpu family      : 6
    model           : 26
    model name      : Intel(R) Core(TM) i7 CPU         920  @ 2.67GHz
    stepping        : 5
    cpu MHz         : 2660.048
    cache size      : 8192 KB
    physical id     : 0
    siblings        : 8
    core id         : 3
    cpu cores       : 4
    apicid          : 7
    fpu             : yes
    fpu_exception   : yes
    cpuid level     : 11
    wp              : yes
    flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov                                                                                         pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm const                                                                                        ant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2                                                                                         popcnt lahf_lm
    bogomips        : 5319.22
    clflush size    : 64
    cache_alignment : 64
    address sizes   : 36 bits physical, 48 bits virtual
    power management: [8]
    
    Is there anything I can do to optimize mysql?

    Thanks.
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Have you read thru any of the other threads on this specific topic? I think you'll find lots of suggestions already posted to many of the longer threads in the Optimization forums to get you going in the right direction.
     
  3. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    I did, but didn't find any overly helpful.
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    What have you tried thus far that was suggested in those threads?
     
  5. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Nothing, because none of these threads looked particuarly relevant to me..I want to fine tune the server, is there some kind of guide into tuning my.cnf for your server?
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Yes, several threads mention this video tutorial on doing so:

    MySQL Optimization | cPanel Video Site

    You can run mysqltuner.pl located at /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl location and see the results on suggestions it provides. If you have questions upon attempting to utilize the suggestions provided in the presentation and running the script, please let us know then.
     
  7. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Here's what I got, any suggestions to what I should do?

    Code:
    root@server [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
    
     >>  MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 155M (Tables: 2004)
    [--] Data in InnoDB tables: 144K (Tables: 9)
    [!!] Total fragmented tables: 9
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 38m 29s (12K q [5.274 qps], 610 conn, TX: 38M, RX: 1M)
    [--] Reads / Writes: 47% / 53%
    [--] Total buffers: 482.0M global + 5.4M per thread (500 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 3.1G (156% of installed RAM)
    [OK] Slow queries: 0% (0/12K)
    [OK] Highest usage of available connections: 0% (3/500)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/34.9M
    [!!] Key buffer hit rate: 23.0% (72K cached / 55K reads)
    [OK] Query cache efficiency: 70.0% (4K cached / 6K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 339 sorts)
    [OK] Temporary tables created on disk: 3% (7 on disk / 181 total)
    [OK] Thread cache hit rate: 99% (3 created / 610 connections)
    [!!] Table cache hit rate: 1% (32 open / 2K opened)
    [OK] Open file limit used: 0% (45/8K)
    [OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
    [OK] InnoDB data size / buffer pool: 144.0K/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        table_cache (> 2048)
    
    root@server [~]#
    
    
     
    #7 ES - George, Oct 9, 2011
    Last edited: Oct 9, 2011
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    In your /etc/my.cnf file:

    Code:
    [mysqld]
    max_connections=250
    max_user_connections = 30
    key_buffer_size = 4M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 8M
    read_buffer_size = 8M
    sort_buffer_size = 8M
    record_buffer_size = 8M
    max_allowed_packet = 300M
    table_open_cache = 200
    thread_cache_size = 128
    wait_timeout = 15
    connect_timeout = 20
    interactive_timeout = 25
    max_connect_errors = 20
    query_cache_type = 1
    query_cache_limit = 32
    query_cache_size = 32M
    log-slow-queries
    
    [mysqld_safe]
    open_files_limit = 8192
    
    [mysqldump]
    max_allowed_packet = 8M
    
    [myisamchk]
    key_buffer_size = 32M
    sort_buffer_size = 16M
    read_buffer_size = 20M
    write_buffer_size = 20M
    local-infile=0
    Here is your /etc/my.cnf cleaned up to remove non-existing variables on Linux (thread_concurrency) or default variables (safe-show-databases, skip-locking), all of which I discuss in my video presentation. I've changed the variable names to the proper ones for MySQL 5.1 (table_open_cache, key_buffer_size), and removed set-variable as that is deprecated. I haven't changed any of the actual values at this point. I suggest reducing the max_connections and reducing the various buffer sizes you've set to get the memory usage lower.
     
  9. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    What would you suggest I change them to? P.s. My VPS now has 2GB RAM, not 1GB since I purchased more as yesterday we got to 1.1GB RAM usage (went to burst).
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I have no specific suggestion. I suggest reducing them to the default values by commenting them out entirely with # at the beginning of the buffer lines, then slowly increasing them if you find that isn't sufficient what the default value happens to be. You can check the default value after commenting them out and restarting MySQL by doing the following command:

    Code:
    mysqladmin var | grep size
    This will show the various defaults for anything with size in it such as sort_buffer_size, key_buffer_size, etc.
     
  11. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    ok, shall I change anything else since i now have 2gb ram? or does ram make no difference.
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    RAM does make a difference, but on 32-bit machines, you can only utilize up to 2GB of RAM.

    I wouldn't change anything else besides what has been noted until you've revised what you have so far, restarted MySQL and given it 24 hours to retest for mysqltuner.pl at that point. I would suggest in that 24 hours to view the video tutorial, since several things I've mentioned were already discussed in that tutorial.
     
  13. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
  14. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    The changes you said has caused this after reboot:
    mysql has failed, please contact the sysadmin (result was "mysql is not running").
     
  15. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    mysql failed @ Sun Oct 9 20:00:00 2011. A restart was attempted automagically.

    Service Check Method: [check command]

    Number of Restart Attempts: 2

    Cmd Service Check Raw Output: mysql is not running


    Startup Log: Starting MySQL....... ERROR! Manager of pid-file quit without updating file.
     
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Tail the error log to see what it is showing as those variables shouldn't cause MySQL to not restart:

    Code:
    tail -500 /var/lib/mysql/hostname.err
    Please replace hostname.err with the fully qualified hostname on the machine with .err on the end.

    Also, to clarify, you mean MySQL restart not reboot right? You wouldn't reboot the whole machine to restart MySQL.

    Finally, post the whole /etc/my.cnf file that you are now using as I don't know which lines you've commented out or what it looks like at this point.
     
  17. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Code:
    111009 20:19:10  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:19:10 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:19:10 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.pid ended
    111009 20:19:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:19:10 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:19:10 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:19:10 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:19:10 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:19:10 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:19:10 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:19:10  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:19:10  InnoDB: Completed initialization of buffer pool
    111009 20:19:10  InnoDB: Started; log sequence number 0 9113420
    111009 20:19:10 [Note] Event Scheduler: Loaded 0 events
    111009 20:19:10 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:22:28 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:22:28 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:22:28  InnoDB: Starting shutdown...
    111009 20:22:30  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:22:30 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:22:30 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:22:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:22:30 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:22:30 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:22:30 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:22:30 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:22:30 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:22:30 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:22:30  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:22:30  InnoDB: Completed initialization of buffer pool
    111009 20:22:30  InnoDB: Started; log sequence number 0 9113420
    111009 20:22:30 [Note] Event Scheduler: Loaded 0 events
    111009 20:22:30 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:22:53 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:22:53 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:22:53  InnoDB: Starting shutdown...
    111009 20:22:56  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:22:56 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:22:56 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:23:09 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:23:09 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:23:09 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:23:09 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:23:09 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:23:09 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:23:09 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:23:09  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:23:09  InnoDB: Completed initialization of buffer pool
    111009 20:23:09  InnoDB: Started; log sequence number 0 9113420
    111009 20:23:09 [Note] Event Scheduler: Loaded 0 events
    111009 20:23:09 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:23:23 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:23:23 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:23:24  InnoDB: Starting shutdown...
    111009 20:23:29  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:23:29 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:23:29 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:23:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:23:30 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:23:30 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:23:30 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:23:30 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:23:30 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:23:30 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:23:30  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:23:30  InnoDB: Completed initialization of buffer pool
    111009 20:23:30  InnoDB: Started; log sequence number 0 9113420
    111009 20:23:30 [Note] Event Scheduler: Loaded 0 events
    111009 20:23:30 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:30:02 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:30:02 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:30:04  InnoDB: Starting shutdown...
    111009 20:30:05  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:30:05 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:30:05 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:30:06 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:30:06 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:30:06 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:30:06 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:30:06 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:30:06 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:30:06 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:30:06  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:30:06  InnoDB: Completed initialization of buffer pool
    111009 20:30:06  InnoDB: Started; log sequence number 0 9113420
    111009 20:30:06 [Note] Event Scheduler: Loaded 0 events
    111009 20:30:06 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:38:16 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:38:16 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:38:16  InnoDB: Starting shutdown...
    111009 20:38:21  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:38:21 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:38:21 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:38:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:38:22 [Warning] /usr/sbin/mysqld: Option '--set-variable' is deprecated. Use --variable-name=value instead.
    111009 20:38:22 [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead.
    111009 20:38:22 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
    111009 20:38:22 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:38:22 [Warning] '--safe-show-database' is deprecated and will be removed in a future release. Please use 'GRANT SHOW DATABASES' instead.
    111009 20:38:22 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:38:22  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:38:22  InnoDB: Completed initialization of buffer pool
    111009 20:38:22  InnoDB: Started; log sequence number 0 9113420
    111009 20:38:22 [Note] Event Scheduler: Loaded 0 events
    111009 20:38:22 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.1.56-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
    111009 20:43:06 [Note] /usr/sbin/mysqld: Normal shutdown
    
    111009 20:43:06 [Note] Event Scheduler: Purging the queue. 0 events
    111009 20:43:06  InnoDB: Starting shutdown...
    111009 20:43:08  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:43:08 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:43:08 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:43:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:43:08 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:43:08 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:43:08  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:43:08  InnoDB: Completed initialization of buffer pool
    111009 20:43:08  InnoDB: Started; log sequence number 0 9113420
    111009 20:43:08 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
    111009 20:43:08 [ERROR] Aborting
    
    111009 20:43:08  InnoDB: Starting shutdown...
    111009 20:43:14  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:43:14 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:43:14 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    111009 20:44:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    111009 20:44:57 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
    111009 20:44:57 [Note] Plugin 'FEDERATED' is disabled.
    111009 20:44:57  InnoDB: Initializing buffer pool, size = 8.0M
    111009 20:44:57  InnoDB: Completed initialization of buffer pool
    111009 20:44:57  InnoDB: Started; log sequence number 0 9113420
    111009 20:44:57 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
    111009 20:44:57 [ERROR] Aborting
    
    111009 20:44:57  InnoDB: Starting shutdown...
    111009 20:45:02  InnoDB: Shutdown completed; log sequence number 0 9113420
    111009 20:45:02 [Note] /usr/sbin/mysqld: Shutdown complete
    
    111009 20:45:02 mysqld_safe mysqld from pid file /var/lib/mysql/server.host.com.pid ended
    root@server [~]#
    
     
  18. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    [mysqld]
    max_connections=250
    max_user_connections = 30
    key_buffer_size = 4M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 8M
    read_buffer_size = 8M
    sort_buffer_size = 8M
    record_buffer_size = 8M
    max_allowed_packet = 300M
    table_open_cache = 200
    thread_cache_size = 128
    wait_timeout = 15
    connect_timeout = 20
    interactive_timeout = 25
    max_connect_errors = 20
    query_cache_type = 1
    query_cache_limit = 32
    query_cache_size = 32M
    log-slow-queries

    [mysqld_safe]
    open_files_limit = 8192

    [mysqldump]
    max_allowed_packet = 8M

    [myisamchk]
    key_buffer_size = 32M
    sort_buffer_size = 16M
    read_buffer_size = 20M
    write_buffer_size = 20M
    local-infile=0
     
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Previously, you'd had a variable about record_buffer, which both that and record_buffer_size don't appear to exist:

    Code:
    111009 20:44:57 [ERROR] /usr/sbin/mysqld: unknown variable 'record_buffer_size=8M'
    Since I'd mentioned to comment out all the buffers with # and that wasn't done, you'd want to remove or comment out that line:

    Code:
    #record_buffer_size = 8M
     
  20. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Done that.

    Waiting for mysql to restart...............finished.

    mysqld_safe (/bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/server.host.com.pid) running as root with PID 10185
    mysqld (/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.host.com.err --open-files-limit=8192 --pid-file=/var/lib/mysql/server.host.com.pid) running as mysql with PID 11324

    mysql has failed, please contact the sysadmin (result was "mysql is not running")
     
Loading...

Share This Page