Optimize MySQL Query/database

CANCELLED
Bids
25
Avg Bid (USD)
$107
Project Budget (USD)
$30 - $250

Project Description:
Hi All,

This is a Linux - MySQL optimization - MyISAM
I have a database that tracks online users and is updated with live stats. This is a single website on a dedicated server. The HD is SSD (extremely fast) , 4GB of RAM,. and a single Quad Core (desktop CPU,. not Xeon).
No problem with RAM,. it is not utilizing any swap.
However, the CPU is jumping up the roof at peek time. > 140.0 which brings the server to a near halt.

I'm not the website developer,. I'm the sys admin.
My client has a developer on his end that built the website for him.
I am looking to improve the performance on this server - both by optimizing the queries (which I'm almost certain are the cause of the high loads) AND also with optimizing the my.cnf values.

I tracked the slow-queries, and here is the sample of it.

/usr/sbin/mysqld, Version: 5.1.65-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 130408 9:14:50
# User@Host: musicL_musicL[musicL_musicL] @ localhost []
# Query_time: 15.612654 Lock_time: 5.597070 Rows_sent: 25 Rows_examined: 572304
use musicL_musicL;
SET timestamp=1365401690;
select `tstations`.*,`UniqueEntries` from `tstations`,(select `idStationStat`,count(1) as `UniqueEntries` from (select `idStationStat` from `tstatistics` where `dateStat` > '2013-03-09' group by `ipUserStat`) `UniqueEntriesThisMonth` group by `idStationStat` order by `UniqueEntries` desc) `TopStationsThisMonth` where `tstations`.`id` = `TopStationsThisMonth`.`idStationStat` and `tstations`.`stationStatus` = '0' and `tstations`.`stationActiveOut` = '0' and `tstations`.`stationCat` 9 order by `UniqueEntries` desc limit 25;
# User@Host: musicL_musicL[musicL_musicL] @ localhost []
# Query_time: 11.506982 Lock_time: 1.431962 Rows_sent: 25 Rows_examined: 572304
SET timestamp=1365401690;
select `tstations`.*,`UniqueEntries` from `tstations`,(select `idStationStat`,count(1) as `UniqueEntries` from (select `idStationStat` from `tstatistics` where `dateStat` > '2013-03-09' group by `ipUserStat`) `UniqueEntriesThisMonth` group by `idStationStat` order by `UniqueEntries` desc) `TopStationsThisMonth` where `tstations`.`id` = `TopStationsThisMonth`.`idStationStat` and `tstations`.`stationStatus` = '0' and `tstations`.`stationActiveOut` = '0' and `tstations`.`stationCat` 9 order by `UniqueEntries` desc limit 25;
# User@Host: musicL_musicL[musicL_musicL] @ localhost []
# Query_time: 14.062953 Lock_time: 3.844835 Rows_sent: 25 Rows_examined: 572304
SET timestamp=1365401690;
select `tstations`.*,`UniqueEntries` from `tstations`,(select `idStationStat`,count(1) as `UniqueEntries` from (select `idStationStat` from `tstatistics` where `dateStat` > '2013-03-09' group by `ipUserStat`) `UniqueEntriesThisMonth` group by `idStationStat` order by `UniqueEntries` desc) `TopStationsThisMonth` where `tstations`.`id` = `TopStationsThisMonth`.`idStationStat` and `tstations`.`stationStatus` = '0' and `tstations`.`stationActiveOut` = '0' and `tstations`.`stationCat` 9 order by `UniqueEntries` desc limit 25;

Here is the output of my.cnf :

/var/lib/mysql # less /etc/my.cnf
[mysqld]
local-infile=0
set-variable = max_connections=600
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 4
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

Do you think you can optimize both the queries and my.cnf to improve the usage of the server?

If you can,. please bid.

The budget for this project is $75
Estimated delivery is 3 days.
Please post any feedback/questions on the PMs only.

==>> DO NOT contact me outside of Freelancer if you were not awarded the project

Good luck and happy bidding.

Skills required:
Database Administration, Linux, MySQL, PHP, SQL
Hire asolell
Project posted by:
asolell Israel
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


$ 220
in 3 days
$ 70
in 3 days
$ 200
in 8 days
Hire damirmarkovic
$ 110
in 2 days
Hire servicebenq
$ 75
in 3 days
$ 176
in 3 days
$ 75
in 3 days
$ 79
in 3 days
Hire jenzeerbasheer
$ 70
in 3 days
Hire farhaoui
$ 54
in 3 days