fine tuning mysql for shared hosting

Support/Development for MySQL, MariaDB, and other database systems
nobody
Forum Regular
Forum Regular
Posts: 349
Joined: Sun Mar 29, 2009 6:52 pm

fine tuning mysql for shared hosting

Unread post by nobody »

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: Select all

[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
Hello IT.
Phone : Blah Blah ....
Have you tried turning it on and off again ?
Phone : Blah Blah ....
....
I'm sorry, are you from the Past ?!
http://www.youtube.com/watch?v=-E4fm4Wqego
Highland
Forum Regular
Forum Regular
Posts: 674
Joined: Mon Apr 10, 2006 12:55 pm

Re: fine tuning mysql for shared hosting

Unread post by Highland »

I like using mysqltuner in the atomic repo. Makes tuning much easier!
"Its not a mac. I run linux... I'm actually cool." - scott
nobody
Forum Regular
Forum Regular
Posts: 349
Joined: Sun Mar 29, 2009 6:52 pm

Re: fine tuning mysql for shared hosting

Unread post by nobody »

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 ...
Hello IT.
Phone : Blah Blah ....
Have you tried turning it on and off again ?
Phone : Blah Blah ....
....
I'm sorry, are you from the Past ?!
http://www.youtube.com/watch?v=-E4fm4Wqego
paulie
Forum User
Forum User
Posts: 76
Joined: Tue Apr 20, 2010 2:49 am

Re: fine tuning mysql for shared hosting

Unread post by paulie »

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.
Post Reply