Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

/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:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #1 ES - George, Oct 9, 2011
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    16,582
    Likes Received:
    439
    Trophy Points:
    583
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 Infopro, Oct 9, 2011
  3. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    I did, but didn't find any overly helpful.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #3 ES - George, Oct 9, 2011
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 cPanelTristan, Oct 9, 2011
  5. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #5 ES - George, Oct 9, 2011
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 cPanelTristan, Oct 9, 2011
  7. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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 [~]#
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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,608
    Likes Received:
    32
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #8 cPanelTristan, Oct 9, 2011
  9. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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).
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #9 ES - George, Oct 9, 2011
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #10 cPanelTristan, Oct 9, 2011
  11. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    ok, shall I change anything else since i now have 2gb ram? or does ram make no difference.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #11 ES - George, Oct 9, 2011
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #12 cPanelTristan, Oct 9, 2011
  13. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    Ok, I'll do that, thanks.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #13 ES - George, Oct 9, 2011
  14. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    The changes you said has caused this after reboot:
    mysql has failed, please contact the sysadmin (result was "mysql is not running").
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #14 ES - George, Oct 9, 2011
  15. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #15 ES - George, Oct 9, 2011
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #16 cPanelTristan, Oct 9, 2011
  17. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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 [~]#
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #17 ES - George, Oct 9, 2011
  18. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    [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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #18 ES - George, Oct 9, 2011
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,608
    Likes Received:
    32
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #19 cPanelTristan, Oct 9, 2011
  20. ES - George

    ES - George Well-Known Member
    PartnerNOC

    Joined:
    Jun 12, 2011
    Messages:
    159
    Likes Received:
    15
    Trophy Points:
    68
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    Twitter:
    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")
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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. leisegang

    MySQL slow and server optimization

    leisegang, Nov 19, 2018, in forum: Workarounds and Optimization
    Replies:
    3
    Views:
    246
    cPanelMichael
    Nov 21, 2018
  2. OCS

    eCommerce Site optimizations?

    OCS, Aug 25, 2018, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    324
    Anupam SG
    Sep 27, 2018
  3. efuzone

    Apache Optimizations

    efuzone, May 7, 2018, in forum: Workarounds and Optimization
    Replies:
    3
    Views:
    305
    cPanelMichael
    May 8, 2018
  4. Noobie83

    Wordpress Optimization on EC2 Instance?

    Noobie83, May 2, 2018, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    222
    cPanelMichael
    May 2, 2018
  5. georget777

    server optimization for wordpress

    georget777, Jan 6, 2018, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    883
    georget777
    Jan 6, 2018

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice