?
Solved

Advice on my.cnf

Posted on 2009-02-10
9
Medium Priority
?
565 Views
Last Modified: 2013-11-15
Hello,

Im tweaking my.cnf for a  HEAVY traffic website, im pasting the code below, any advices on it?

Server:
CPU  E5310  (dual quad core, 8cpu)
8GB RAM
Using CentOS and Mysql5
[mysqld]
skip-locking
skip-innodb
max_connections = 2500
key_buffer = 256M
max_allowed_packet = 8M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
thread_cache_size = 256
query_cache_size = 128M
query_cache_type = 1
query_cache_limit= 1M
thread_concurrency= 16
wait_timeout=20
interactive_timeout=10
connect_timeout=10
join_buffer=1M
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[mysql]
no-auto-rehash
 
[usamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

Open in new window

0
Comment
Question by:epagos7
  • 4
  • 4
8 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 23602594
Is this server dedicated to MySQL?

I see MySQL's minimum required memory for this my.cnf is 10256MB(10GB) and you have 8GB RAM.. don't you think max_connection is too high?


0
 

Author Comment

by:epagos7
ID: 23602798
no, its not mysql dedicated, the server also acts as HTTP server.

Ive increased Max connections to that number because i got a lot of rejected mysql connections in the past, should i lower it?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23602959
If its not dedicated then the way you have assigned the memory to MySQL parameters is dangerous..

I suggest you to run MySQL tuner script and see what it suggest

http://wiki.mysqltuner.com/MySQLTuner
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:epagos7
ID: 23603071
i just executed that perl script, it seems my query efficiency is 0% since its not using any cache... how can i make mysql to read from cache?
0
 

Author Comment

by:epagos7
ID: 23603083
i forgot, a LOT of the Selects that the script is making to mysql have always the same result, so i assume caching wont be a problem
0
 

Author Comment

by:epagos7
ID: 23603099
Also i see: 'Connections Aborted: 27%'
0
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 23608238
>i just executed that perl script, it seems my query efficiency is 0% since its not using any cache... how >can i make mysql to read from cache?

Pls go thur this link for table_cache

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

>>i forgot, a LOT of the Selects that the script is making to mysql have always the same result, so i >>assume caching wont be a problem

If the results are same then cahcing may definately help you... check the manual on how you can pass the SQL CACHE hints to the optimizer.

>>Also i see: 'Connections Aborted: 27%'

Pls check this

http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html

I suggest you to keep an eye on error log.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 23709464
Any updates on this?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question