My website has 50.000 unique user day on a Xeon Dual with 2GB RAM
The problem is that the MySql is often over 55% on my "top" and slowing my website.
I've optimized more times my.cnf but with bad result and i don't understand the cause.
I have to agree with the above... you really want to look into which queries are taking a long time by viewing the MySQL processlist.
I think you might be looking for a "magic" configuration change, where you change one configuration point and the whole thing flies... but in reality, you're better off actually diagnosing what is consuming your resources.
Once you figure out what it is, you can then rectify it. I would be willing to bet that optimizing some of your SQL statements would easily reduce your processor usage for MySQL from a steady 55% down to a more reasonable 20% or 25%.
Things to look for? While they teach tons of database courses in optimization, here are a couple easy ones:
JOINS: Don't join your datasets too early! If you want to join two large tables, first select the relevant records from each, then JOIN on that subset. Don't do a big join and then select your records (lazy way because the SQL statement is more intuitive). Why? Joining is expensive. Lots of memory malloc'ed/free'ed/requested from the pool, and lots of processing.
CACHING: Got a big select operation done on every single page of your site? Why don't you use your web app framework to cache some of the load... many have this built-in, but you could have a simple piece of code like "on every Nth request, grab fresh data from the database; else, use this cached copy I grabbed a moment ago". That will reduce the number of operations by a factor of N.
Cory von Wallenstein, Dynamic Network Services Inc.
My team and I spent the last few months putting together the DynDNS Spring Server VPS platform for folks needing IPv4 and IPv6 Linux servers on Xen. Would love feedback, both good and bad!
You can try to turn off/on persistent connections on php scripts. There is not a magic trick.
Max connection should match max clients from apache. I'm not sure about this.
Find your most used scripts, and use explain on each queryes. Make sure those queries are using indexes.
Check each table, make sure valid data is less or egual with indexes data. Scanning 100 lines sometime could be better then adding a new index.
Compund indexes made from 2 columns ID1, ID2 in this order works for queries using only ID1 in WHERE or for ID1 and ID2 if ID2 is always require ID1 use this version.