This may be the wrong section but I tried to find the most related place. So anyway, one of my servers is becoming very slow at times and has high load on average. Sometimes we can see a load of 10 when usually it is around 3.0-4.0. Also the mysqld process is taking about 200% or more CPU at any one time. Occasionally it drops to 50% for a minute then goes back up. This is most likely due to a huge increase in traffic on various sites which the server hosts. So the question is, does anyone have any tips for me or know any good server optimization services? Basically, mysql needs optimizing and other things in general which affect the performance. It is painfully slow at times and is causing huge losses of traffic.
If i knew any, I would. Also after messing around mysqld is 90% CPU most the time (4 cpus). The slow log shows a rand query i cant track down, but ill work on optimizing some queries.
Rand queries are highly useless in large tables. Benchmarks were done on tables with 100K rows, you'd be suprised how long it took to return a single row. Generate random numbers in the scripting language, and send them to SQL.
Its ok a sorted it now. Turned out it was a huge table (300-400k rows) which had a missing index and was being queried every second or two. So i cleared 200,000 rows out of it and added an index, seems to be fine now.