/etc/my.cnf optimization

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#1

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:

[email protected] [~]# 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.

I

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#3

Infopro said:
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.

I did, but didn't find any overly helpful.

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#4

What have you tried thus far that was suggested in those threads?

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#5

cPanelTristan said:
What have you tried thus far that was suggested in those threads?

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?

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#6

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#7

Here's what I got, any suggestions to what I should do?

Code:

[email protected] [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >>  MySQLTuner 1.1.2 - Major Hayden <[email protected]>
 >>  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)

[email protected] [~]#

Last edited: Oct 9, 2011

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#8

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#9

cPanelTristan said:
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.

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).

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#10

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#11

ok, shall I change anything else since i now have 2gb ram? or does ram make no difference.

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#12

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#13

Ok, I'll do that, thanks.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#14

The changes you said has caused this after reboot:
mysql has failed, please contact the sysadmin (result was "mysql is not running").

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#15

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.

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#16

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.

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#17

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
[email protected] [~]#

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#18

[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

cPanelTristan

Quality Assurance Analyst

Staff member

Oct 9, 2011

#19

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

ES - George

Well-Known Member

PartnerNOC

Oct 9, 2011

#20

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")

Thread starter	Similar threads	Forum	Replies	Date
K	/etc/my.cnf optimization	Workarounds and Optimization	4	Dec 11, 2012
W	MySQL /etc/my.cnf Help	Workarounds and Optimization	5	Nov 14, 2012
S	/etc/my.cnf optimisation	Workarounds and Optimization	0	Aug 28, 2012
B	Is /etc/my.cnf is OK?	Workarounds and Optimization	1	Oct 15, 2011
	/etc/my.cnf almost empty on new server	Workarounds and Optimization	4	Oct 11, 2011