Closed

Optimize MySQL Query/database

This project received 24 bids from talented freelancers with an average bid price of $110 USD.

Get free quotes for a project like this
Employer working
Project Budget
N/A
Total Bids
24
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 [url removed, login to view] 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: [url removed, login to view] Lock_time: [url removed, login to view] 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: [url removed, login to view] Lock_time: [url removed, login to view] 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: [url removed, login to view] Lock_time: [url removed, login to view] 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 [url removed, login to view] :

/var/lib/mysql # less /etc/[url removed, login to view]
[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 [url removed, login to view] 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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online