Advice on my.cnf

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

epagos7Asked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
>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
 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
epagos7Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
UmeshMySQL Principle Technical Support EngineerCommented:
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
 
epagos7Author Commented:
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
 
epagos7Author Commented:
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
 
epagos7Author Commented:
Also i see: 'Connections Aborted: 27%'
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Any updates on this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.