Find Jobs
Hire Freelancers

Optimize MySQL Query/database

$30-250 USD

Cancelled
Posted almost 11 years ago

$30-250 USD

Paid on delivery
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 [login to view URL] 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 [login to view URL] : /var/lib/mysql # less /etc/[login to view URL] [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 [login to view URL] 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.
Project ID: 4431368

About the project

24 proposals
Remote project
Active 11 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
24 freelancers are bidding on average $110 USD for this job
User Avatar
Hi there, i am very interested to complete this project. please see my past reviews, lets complete this project
$220 USD in 3 days
4.9 (159 reviews)
6.7
6.7
User Avatar
I am expert in both, mysql configuration and query optimization. Check the private message for more.
$70 USD in 3 days
4.9 (170 reviews)
6.2
6.2
User Avatar
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 than 11 year of experience,so we can manage this work and will give you quality solution.
$200 USD in 8 days
4.6 (10 reviews)
5.7
5.7
User Avatar
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 database dump with some sample data, since that would simplify optimization a lot. My bid is above what you said your budget is, but as you can see from my reviews - I deliver.
$110 USD in 2 days
5.0 (67 reviews)
5.5
5.5
User Avatar
Hi I am ready to help you. Please check your pm. Thanks
$75 USD in 3 days
4.9 (36 reviews)
5.1
5.1
User Avatar
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 hope that we can work together. Sincerely Wathek
$176 USD in 3 days
4.9 (40 reviews)
5.0
5.0
User Avatar
I think I can help you .
$75 USD in 3 days
4.9 (35 reviews)
5.0
5.0
User Avatar
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 have considered our request. we thank you for sparing the time with us.
$79 USD in 3 days
5.0 (3 reviews)
4.9
4.9
User Avatar
Right guy here please check my Personal messaage
$70 USD in 3 days
5.0 (17 reviews)
4.5
4.5
User Avatar
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
5.0 (36 reviews)
4.5
4.5
User Avatar
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
5.0 (4 reviews)
3.9
3.9
User Avatar
ready to work with you
$154 USD in 3 days
5.0 (11 reviews)
3.6
3.6
User Avatar
Hello, I have over 7 years experience as a Linux admin. Please read private message. Thank you.
$66 USD in 3 days
4.9 (12 reviews)
3.5
3.5
User Avatar
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
4.4 (6 reviews)
3.6
3.6
User Avatar
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 professional services.
$200 USD in 3 days
5.0 (2 reviews)
2.7
2.7
User Avatar
I can do this Job. I'm a database especialist & Postgres DBA
$140 USD in 3 days
5.0 (1 review)
2.0
2.0
User Avatar
Hi, I'm professional SQL developer, can help you out with this one.
$75 USD in 3 days
5.0 (1 review)
1.4
1.4
User Avatar
I am ready to do this job
$45 USD in 2 days
0.0 (0 reviews)
0.0
0.0
User Avatar
i can do this
$50 USD in 3 days
0.0 (0 reviews)
0.0
0.0
User Avatar
I work as DBA SSr at a developement department
$55 USD in 1 day
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED STATES
New York, United States
5.0
36
Payment method verified
Member since Sep 24, 2004

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.