Optimize MySQL Query/database

Cancelled

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.

Skills: Database Administration, Linux, MySQL, PHP, SQL

See more: mysql slow queries, try freelancer local, the freelancer core, problem stats, php mysql developer near me, php freelancer id, not awarded, near by freelancer, mysql developer freelancer, i am a freelancer where to live, hd freelancer, freelancer version 1.1, freelancer user id, freelancer stats, freelancer s community, freelancer on linux, freelancer online version, freelancer online hd, freelancer near you, freelancer log up, freelancer local developer, freelancer linux project, freelancer linux developer, freelancer for unix, freelancer desktop version

Project ID: #4431368

24 freelancers are bidding on average $110 for this job

linuxfreak1985

Hi there, i am very interested to complete this project. please see my past reviews, lets complete this project

$220 USD in 3 days
(159 Reviews)
6.7
jthoma

I am expert in both, mysql configuration and query optimization. Check the private message for more.

$70 USD in 3 days
(170 Reviews)
6.2
bistanil98

I have check this requirement,have some query,so need to discuss this,please tell me how we can start the discussion. to know more about us please check Private Message. We have a team of professionals,they have more More

$200 USD in 8 days
(10 Reviews)
5.7
damirmarkovic

I have experience with optimization of similar databases/queries. In your query, I see 2-3 possible issues that can be resolved with different MySQL setup and altered query. It would be great if you could provide dat More

$110 USD in 2 days
(67 Reviews)
5.5
servicebenq

Hi I am ready to help you. Please check your pm. Thanks

$75 USD in 3 days
(36 Reviews)
5.1
wathek

Dear, I'm very interested in this. I have had something very close to this. I had a big database with one table that contains more than 50 000 000 records and one query doesn't take more than 0.5 second to run. I h More

$176 USD in 3 days
(40 Reviews)
5.0
tamrakar81

I think I can help you .

$75 USD in 3 days
(35 Reviews)
5.0
acutegroups

Here we like to specify our slogan "A Right Place For Pace". we have ability and tech to close your bid within that target time. we request you to have a glance at our Private Message. we would be glad to know that you More

$79 USD in 3 days
(3 Reviews)
4.9
jenzeerbasheer

Right guy here please check my Personal messaage

$70 USD in 3 days
(17 Reviews)
4.5
farhaoui

Hello, I have over 8 years experience in web developing. I can do this job. Please feel free to ask if you have any questions. Thank You

$54 USD in 3 days
(36 Reviews)
4.5
tcrnicki

I am into advanced Linux and MySQL 15+ years and I will do my best to resolve the CPU peaks.

$100 USD in 3 days
(4 Reviews)
3.9
garhwalsatyapal

ready to work with you

$154 USD in 3 days
(11 Reviews)
3.6
MikeRRR

Hello, I have over 7 years experience as a Linux admin. Please read private message. Thank you.

$66 USD in 3 days
(12 Reviews)
3.5
tgshakthi

Hi, You need to create index and change the query to keep the CPU usage minimal, Please check pm when possible where have elaborated on the process Best regards, TGS

$82 USD in 3 days
(6 Reviews)
3.6
costansin

I am a worldwide MySQL dba expert. I will optimize your system in the next 3 days. Please read PM for more details. I am currently building my portfolio so I am bidding low - don't let this fool you - I provide profess More

$200 USD in 3 days
(2 Reviews)
2.7
d0tnet12

Hi, i can develop this for you.You will find me on skyp,e : bujhbe

$100 USD in 3 days
(2 Reviews)
2.4
IgnacioB

I can do this Job. I'm a database especialist & Postgres DBA

$140 USD in 3 days
(1 Review)
2.0
jaromirdolecek

Hi, I'm professional SQL developer, can help you out with this one.

$75 USD in 3 days
(1 Review)
1.4
mehmetcelik

i can do this

$50 USD in 3 days
(0 Reviews)
0.0
DanielArg

I work as DBA SSr at a developement department

$55 USD in 1 day
(0 Reviews)
0.0