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