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.

Page 1 of 2
  1. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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.
     
    #1 ES - George, Oct 9, 2011
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    15,433
    Likes Received:
    285
    Trophy Points:
    433
    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.
     
    #2 Infopro, Oct 9, 2011
  3. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    I did, but didn't find any overly helpful.
     
    #3 ES - George, Oct 9, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    What have you tried thus far that was suggested in those threads?
     
    #4 cPanelTristan, Oct 9, 2011
  5. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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?
     
    #5 ES - George, Oct 9, 2011
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    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.
     
    #6 cPanelTristan, Oct 9, 2011
  7. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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,622
    Likes Received:
    24
    Trophy Points:
    238
    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.
     
    #8 cPanelTristan, Oct 9, 2011
  9. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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).
     
    #9 ES - George, Oct 9, 2011
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    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.
     
    #10 cPanelTristan, Oct 9, 2011
  11. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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.
     
    #11 ES - George, Oct 9, 2011
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    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.
     
    #12 cPanelTristan, Oct 9, 2011
  13. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Ok, I'll do that, thanks.
     
    #13 ES - George, Oct 9, 2011
  14. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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").
     
    #14 ES - George, Oct 9, 2011
  15. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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.
     
    #15 ES - George, Oct 9, 2011
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    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.
     
    #16 cPanelTristan, Oct 9, 2011
  17. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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 [~]#
     
    #17 ES - George, Oct 9, 2011
  18. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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
     
    #18 ES - George, Oct 9, 2011
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,622
    Likes Received:
    24
    Trophy Points:
    238
    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
     
    #19 cPanelTristan, Oct 9, 2011
  20. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    142
    Likes Received:
    2
    Trophy Points:
    68
    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")
     
    #20 ES - George, Oct 9, 2011
(You must log in or sign up to post here.)
Page 1 of 2
Loading...
Similar Threads - optimization
  1. RadWebHosting

    Apache Optimizations for Single Domain on Server

    RadWebHosting, Jun 15, 2017, in forum: Workarounds and Optimization
    Replies:
    2
    Views:
    61
    cPanelMichael
    Jun 16, 2017
  2. Rakaris Bakaris

    Question regarding my.cnf optimization

    Rakaris Bakaris, Mar 17, 2017, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    248
    cPanelMichael
    Mar 17, 2017
  3. Neso

    opCache optimization

    Neso, Jan 17, 2017, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    323
    cPanelMichael
    Jan 19, 2017
  4. whothat

    Apache 4 Optimizations Possible?

    whothat, Nov 12, 2016, in forum: EasyApache
    Replies:
    8
    Views:
    386
    cPanelMichael
    Nov 15, 2016
  5. SpaceCowboy

    MysqlTunner optimization help

    SpaceCowboy, Nov 12, 2016, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    496
    cPanelMichael
    Nov 14, 2016

Share This Page