fine tuning mysql for shared hosting

nobody · **Joined:** Sun Mar 29, 2009 6:52 pm **Posts:** 348

I surely am not an expert in mysql... So my configuration below doesn't have to be good or right... !
Over the last 2 years I've been trying to push MySQL to work more effectively and consume less RAM (as far this might be posible).
I believe many people are troubled with that issue. I am pasting my "my.cnf". It has worked for me. Any advise would be great !

Code:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

local-infile=0
thread_concurrency=12
max_connections = 150
max_user_connections = 150
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 1024
thread_cache_size = 128
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 16M
max_heap_table_size = 16M

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Highland · **Joined:** Mon Apr 10, 2006 12:55 pm **Posts:** 656

I like using mysqltuner in the atomic repo. Makes tuning much easier!

nobody · **Joined:** Sun Mar 29, 2009 6:52 pm **Posts:** 348

Highland wrote:

I like using mysqltuner in the atomic repo. Makes tuning much easier!

I have mysqltuner and up to a level I have used it but to me its a bit confusing .... As I said I am no MySQL expert ...

paulie · **Joined:** Tue Apr 20, 2010 2:49 am **Posts:** 74

I found this quite interesting but I have not yet had chance to apply / test : http://www.percona.com/webinars/2012-03 ... iguration/

I also haven't had opportunity to do any testing of the percona rebuilds of MySQL server but they look pretty promising!

The my.cnf is highly dependent on server spec, environment (shared,dedicated,dedicated db server) and what is being hosted. For example we have a fairly high innodb_buffer_pool size as the applications we host tend to use innodb heavily.

You may (or may not) find increasing this has an effect, since it looks to be set at the default of 8MB (ours is specified at 1GB on our 8GB shared servers to give you a comparison). However if its not used it makes little difference. That said, the psa database uses it heavily and its the default engine in MySQL 5.5

Also "old_passwords=1" is in the default CentOS my.cnf's and all our configs... which is posing an irritating problem when moving to PHP 5.3 and php-mysqlnd specifically, as its not compatible and requires reapplying of MySQL user passwords to create new hashes.

fine tuning mysql for shared hosting

Who is online