mysql down on hight loads

servertweak

servertweak

Verified User
Joined
Feb 3, 2005
Messages
266
mysql down on high loads

I noticed my mysql every so offen goes down on high loads, i get an error can't connect to database for 5sec's then everything is fine.

anything i should do to prevent this ?
 
Last edited:
Check your MySQL (or server) logs. Possible its some site that is causing the problem with MySQL.
 
my.cnf

max_connections = 800
key_buffer = 8M
myisam_sort_buffer_size = 16M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 8M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
tmp_table_size = 8M
skip-innodb
 
servertweak said:
my.cnf

max_connections = 800
key_buffer = 8M
myisam_sort_buffer_size = 16M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 8M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
tmp_table_size = 8M
skip-innodb
Click to expand...

WoW!
man you have some high entries there!

you can download the file in attachment and extract it in your server then run it
PHP: 
perl mysqltuner.pl

it'll give u some hints about some values that should be modified in your my.cnf to work fine with your server's resources (processor, RAM)
but first you should get the mysql admin user and password.. do this first
PHP: 
cat /usr/local/directadmin/conf/mysql.conf

hope this is helpful
 

Attachments

can someone help me i have this same problem mysql have too many connections right after i restart VPS.

All i have in my.cnf file is this:
Code: 
[mysqld]
local-infile=0
 
Check these forums for a suggested my.cnf file, and create it on your server. Then restart mysqld.

Jeff
 
thanks if found out how to do it and now my server works GOOD!

anyone else here looking for it check this:
Code: 
http://help.directadmin.com/item.php?id=44
 
Code: 
[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

[mysqld]
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer                                      = 512M
max_allowed_packet                              = 16M
table_cache                                     = 2048
sort_buffer_size                                = 16M
read_buffer_size                                = 16M
join_buffer_size                                = 16M
read_rnd_buffer_size                            = 64M
myisam_sort_buffer_size                         = 256M
myisam_repair_threads                           = 1
myisam_recover
thread_cache_size                               = 8
thread_concurrency                             = 8
query-cache-type                               = 1
query_cache_limit                                = 2M
query-cache-size                                = 128M

log-slow-queries=/var/log/mysqlslowqueries.log
local-infile = 0

character-set-server            = utf8
default-character-set           = utf8

#skip-networking
skip-bdb
skip-federated

# InnoDB Engine
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 16M
innodb_thread_concurrency = 8
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#innodb_force_recovery = 1

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqld_safe]
open-files-limit = 8192

MySQL is the second important part in a Hosting environment. User scripts execution and availability are mainly MySQL dependent.
We are running over 60 shared hosting servers with this configuration file. It depends mainly on the hardware and the operating system used.
The configuration can work on servers with 2GB of RAM also, but you will need to divide all the numbers in half.
 
Help with this configuration

Can someone clarify what the 1st line should read? Should:
#password = your_password

Be changed to :
password = my_actual_password_here


Or should this remain hashed out and not set ?
Thanks very much!
 
You must log in or register to reply here.
Back
Top