Share your my.cnf if you have a 16gb ram server

Hello

Can anyone share with me the my.cnf settings from an optimized server with more than 600 users online at the same time on a 16gb ram server?

Thank you

 

Why don't you post your 16GB RAM server my.cnf to get suggestions from others? Is your server dedicated MySQL server or sharing resources with web server?

 

Ok here it is:

[mysqld]
skip-name-resolve
skip-innodb
skip-locking
skip-networking
thread_concurrency=16
safe-show-database
tmp_table_size=256M
max_heap_table_size=256M
query_cache_limit=3M
query_cache_size=128M
query_cache_type=1
max_user_connections=300
max_connections=300
interactive_timeout=30
wait_timeout=50
connect_timeout=30
low_priority_updates=1
thread_cache_size=512
key_buffer=2048M
join_buffer_size=256M
max_allowed_packet=64M
table_cache=1536
record_buffer=1M
sort_buffer_size=16M
read_buffer_size=16M
max_connect_errors=10
myisam_sort_buffer_size=512M
concurrent_insert=2

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[mysql]
no-auto-rehash



And the problem i think is that every connection takes a lot of ram or cpu and sql is loading a lot

The server is dedicated and web files and sql is on the same server as i think 400 users online are not a problem to handle for sure.

Or maybe every php request is big or something related to that that i am not sure how to check

Thank you

 

1. for big website that use intensive mysq, it shouldn't be on the same server.
2. are you using innodb or myisam? try looking on mysql processlistand see what is hogging your mysql.
if you're using myisam as table, probably if there is lots of select, there will be lots of locking *if* you have consecutive insert too.
3. 600 online user at the sametime? 600 concurrent connection to sql? or what? (i see on your config max 300)

 

2)myisam
3)600 users online on the forum.

How a second derver help if the sql is locking as it will take the same amount of requests i think it will lock again.

 

try looking on mysql processlistand see what is hogging your mysql <-- this is important, and you can adjust from there.

1. by default UPDATE priority are higher than SELECT, by changing SLELECT priority higher than UPDATE maybe could help the things down.
2. use caching, like static file or memcache <-- dp forum also use memcache.
3. for dynamic caching use varnish
4. and start thinking about load blancing your sql with atleast 1 master 2 slave (it could be replication or cluster)
5. use sphinx or solr/lucene to search your data, so it will not lock your myisam table when doing search <-- look at the footer of dp forum, they use sphinx.
6. fix pagination system, instead using LIMIT M,N use LIMIT N and add some condition so mysql will use the available index instead of scanning all table for lookups.

atleast for busy db you need like raid 10 sas hdd for the db itself with minimal 4hdd setup, and use separate hdd for OS and log.
if all still failed, drop myisam, and use something better like innodb.

still failed too, use mysql enterprise server or xtraDB engine from mariadb.

 

What do you think about my.cnf?

Also i think the problem is per thread/connection that every user use.

Maybe php size or a query use a lot of ram as the problem is coming when i have exactly the same amount of users.

And yes i use raid 10 4x sas 15k 16gb ram 2x quad xeon xcache and spinx.

I want to ask also if xcache by default cache also files and not only php as maybe this is my problem....

On a cpanel server i have nginx plugin in front of apache.

Also can i use nginx for caching static files?

Thank you

 

xcache only caching php opcode.
if nginx is in front of apache, so when it request static file it will most probably got it requested from nginx.

have you try optimizing from the forum scripts? if you're using vb http://articles.digitalpoint.com/content.php?r=7-Optimize-vBulletin-4
have you try to use memcache too? (different than xcache)

i rarely use myisam so i cannot comment on your config.
you try this script to check http://mysqltuner.pl/mysqltuner.pl

if all still fail, hire a reputable decent database guy to solve your problem that has experience with your forum software, probably you can find on this forum.

but before that, i recommend you use mariaDB or Perconna, it's waaay better than standard MySQL.
it has Aria and xtraDB engine.

Performance
Aria > myisam
xtraDB > innoDB

i've tried myself, running busy wp side with mariadb replication on xtraDB + sphinx, it's awesome!
but it doesn't work magically by just changing mysql build. i had to optimize alot of wordpress code on the query part and use a caching plugin because it's very important.

 

I don't know first if i have to check when i use Easy apache update the Memcache and memcached too.

And then after installation do you know any easy info to configure it?

Thank you

 

The hardware sounds good. I would seriously consider Innodb or one of percona innodb builds, Xtradb as suggested. The thought of table locking alone with this much usage just wouldn't work for me. Apart from that Innodb is so much more able to be tuned. The exception is if you need full text searching, however there are workarounds for it.

If you stick with myisam, I would start with the key buffer and bump it up to about 6 - 8Gb. Here's a good article from the percona guys about migrating. They estimate you can get 2 - 3 times performance tuning myisam to 10 - 50 for Innodb. This does mean that Innodb isn't as good out of the box, but IMO it is far easier and more predictable to work with.

The other question I have, is how are you connecting to the database on the application level. I would suggest switching to a singleton database connector to prevent opening and closing connections so often. If you have 400 users on the site at the same time, this would definitely be appropriate.

As far as caching, I would start with APC. If you're finding it's not performing the way you want it to, maybe go to a reverse proxy caching mechanism as well.