Ok I'm not very good with MySQL let alone myself performance tuning, I've always been a Sun admin in an Oracle environment Anyway I run a VoIP auto-dialer for a client of mine, the auto-dialer makes 200 concurrent calls, basically the auto-dialer reaches a threshold of about 100 available lines queries the database finds the next available set of 100 numbers to call and dumps them into Asterisk (which is an open source pbx) making sure that there are about 200 lines in use at all times. Anyway, when the dialer is just running (in between the SQL queries) it runs at 98% CPU IDLE, then MySQL spikes the box and I'm at about 65% CPU IDLE, I want to be able to up the amount of concurrent calls my dialer makes but I don't want these SQL spikes to hinder voice quality. Mem usage is low, and there is no problem with IO since the MySQL database is only about 25M since its just about 300,000 phone numbers. Anyway I can decrease the CPU spikes of MySQL? The Box is a simple Intel(R) Pentium(R) D CPU 3.00GHz w/ 1G ram. Any suggestions would be good, any info you need just ask.
With MySQL you should have a phpmyadmin interface to the database. Using phpmyadmin, you can run the query and see the actual time taken by the query. You can try and adjust the query to see if you gain any performance improvement. You could also post the actual query and the approx table sizes here so that someone who has an idea can give his suggestions. You could also try to reduce the figure from 100 to 50 and make it more frequent.