- By robellis
- In SEO
- Tags cpanel, Database Error, MySQL, MySQL Optimization, Wordpress Optimization
Error Establishing A Database Connection In WordPress Fix
Well the Error Establishing A Database Connection for my WordPress sites was a nightmare to fix and to figure out exactly what was wrong? But finally after a few days of researching and testing, the error is now fixed.
I run a VPS server, and I decided to update my database version from MySQL 5.5 to 5.6. Hence this is where the problem began. For 3 years I had no issues with my site & then all of a sudden my website is getting the dreaded Error Establishing A Database Connection 2-4 times a day. First thing I did was contact my hosting provider to see if they would help, they said I would have to contact their professional services, which I did and they said I would need MySQL tuning for a $90 charge because I was running out of memory but would not guarantee it would solve the issue.
After some research I find some great sites for optimizing MySQL to solve the memory issue where you have to edit your my.cnf file. My VPS has 1.5 GB of memory so these are the settings I am using.
MySQL Optimization:
[mysqld]
#skip-name-resolve
# safe-show-database
#old_passwords
back_log = 50
# skip-innodb
max_connections = 650
key_buffer_size = 144M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_open_cache=4000
thread_cache_size = 512
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet=268435456
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 8
concurrent_insert = 2
# table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 4
open_files_limit=10000
innodb_file_per_table=1
innodb_buffer_pool_size=134217728
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
After applying these settings, my websites would run longer but still crash after 12 hours, so after MySQL optimization, this was not the issue.
The next thing I did was run a ‘check the database’ in the MySQL Databases section in cpanel. Here is where I found an issue in the wp_options table. I had this error on multiple websites in my vps. warning: 2 clients are using or haven’t closed the table properly Just select repair the database option to fix the issue and you will have to restart MySQL for the changes to take affect.
The last thing I did was have to create a new database user for each database I have. After these changes were made, I have had no issues with MySQL crashing on me.
Hopefully these helpful hints will help you out? If anyone has any other fixes or comments please leave them in the comment section below.
Good Luck Guys!
Here is a great wordpress optimization for tips: http://www.prelovac.com/vladimir/wordpress-optimization-guide/
