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