İptal Edildi

Optimize MySQL Query/database

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: 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 [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.

Beceriler: Veri Tabanı Yönetimi, Linux, MySQL, PHP, SQL

Daha fazlasını görün: 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

İşveren Hakkında:
( 36 değerlendirme ) New York, United States

Proje NO: #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

in 3 gün içinde220$ USD
(159 Değerlendirme)
6.7
jthoma

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

in 3 gün içinde70$ USD
(170 Değerlendirme)
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 Daha fazlası

in 8 gün içinde200$ USD
(10 Değerlendirme)
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 Daha fazlası

in 2 gün içinde110$ USD
(67 Değerlendirme)
5.5
servicebenq

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

in 3 gün içinde75$ USD
(36 Değerlendirme)
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 Daha fazlası

in 3 gün içinde176$ USD
(40 Değerlendirme)
5.0
tamrakar81

I think I can help you .

in 3 gün içinde75$ USD
(35 Değerlendirme)
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 Daha fazlası

in 3 gün içinde79$ USD
(3 Değerlendirme)
4.9
jenzeerbasheer

Right guy here please check my Personal messaage

in 3 gün içinde70$ USD
(17 Değerlendirme)
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

in 3 gün içinde54$ USD
(36 Değerlendirme)
4.5
tcrnicki

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

in 3 gün içinde100$ USD
(4 Değerlendirme)
3.9
garhwalsatyapal

ready to work with you

in 3 gün içinde154$ USD
(11 Değerlendirme)
3.6
MikeRRR

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

in 3 gün içinde66$ USD
(12 Değerlendirme)
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

in 3 gün içinde82$ USD
(6 Değerlendirme)
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 Daha fazlası

in 3 gün içinde200$ USD
(2 Değerlendirme)
2.7
d0tnet12

Hi, i can develop this for [url removed, login to view] will find me on skyp,e : bujhbe

in 3 gün içinde100$ USD
(2 Değerlendirme)
2.4
IgnacioB

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

in 3 gün içinde140$ USD
(1 Değerlendirme)
2.0
jaromirdolecek

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

in 3 gün içinde75$ USD
(1 Değerlendirme)
1.4
DanielArg

I work as DBA SSr at a developement department

1 gün içinde 55$ USD
(0 Değerlendirme)
0.0
mehmetcelik

i can do this

in 3 gün içinde50$ USD
(0 Değerlendirme)
0.0