Hi all Wanted to know how to improve my io performance on innodb , the situation is like this ,32 bit mysql ,virtualized with hyper-v , the db is on a 200gb vhd and space is not an issue, please note nothing can be changed in the environment unless you suggest putting log files to a different partition, but no changing of the sql server. Regards GG
Run `iostat -c10` or something like that and confirm that you're near the IO limit of the disk subsystem. You'll also want to run `free` and see what's going on regarding memory usage. You may not be using all of your memory if your InnoDB settings aren't setup to use it. Anyway, the first thing is to get onto the latest and greatest Ubuntu/Debian/CentOS or whatever OS you're running and make sure you're on the latest and greatest MySQL from your OS vendor. You should definitely be on 5.5 and ideally 5.6. Then, checkout this whole section: http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb.html I wonder if it's an Innodb issue though - more likely it's bad queries and indexes - which you may or may not be able to change. To see the hotspots, install New Relic (http://newrelic.com/). -Adam -- Kili - Cloud for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> More Musings: varud.com About Adam: www.linkedin.com/in/adamcnelson On Mon, Nov 17, 2014 at 10:52 AM, geoffrey gitagia via skunkworks < skunkworks@lists.my.co.ke> wrote:
Hi all Wanted to know how to improve my io performance on innodb , the situation is like this ,32 bit mysql ,virtualized with hyper-v , the db is on a 200gb vhd and space is not an issue, please note nothing can be changed in the environment unless you suggest putting log files to a different partition, but no changing of the sql server.
Regards GG
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
Geoffrey, 1. There shouldn't be any MyISAM tables at all (aside from maybe the information schema), that's a problem right there. 2. Hopefully you're on Windows Server 2012? 3. It seems like you should still be able to move onto MySQL 5.6 which has InnoDB improvements. 4. 'SELECT *' should be rare in code. It would be better if the developers were using an ORM and weren't writing very much SQL but that's probably not under your purview. 5. If you can find the top 5 queries by time, do an 'explain' on each one and you'll see whether indexes will help. Be careful because indexes can slow down INSERTs and UPDATEs. 6. You might be able to reduce what gets logged - although that's a minor improvement usually. 7. MySQL works better under Linux. I know that's a big change but it's worth thinking about over the longer term. -Adam -- Kili - Cloud for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> More Musings: varud.com About Adam: www.linkedin.com/in/adamcnelson On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia <ggitagia@gmail.com> wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
On Mon, Nov 17, 2014 at 2:23 PM, Adam Nelson via skunkworks < skunkworks@lists.my.co.ke> wrote:
Geoffrey,
1. There shouldn't be any MyISAM tables at all (aside from maybe the information schema), that's a problem right there. 2. Hopefully you're on Windows Server 2012? 3. It seems like you should still be able to move onto MySQL 5.6 which has InnoDB improvements.
Hi, 4. 'SELECT *' should be rare in code.
I highly suspect that this is a myth ....
It would be better if the developers were using an ORM and weren't writing very much SQL but that's probably not under your purview.
For some reason I cannot access Youtube at the monent but. One of the keynote talks at 2008's DjangoCon had Cal Henderson (Flickr's creator) dispute the fact that SELECT * FROM YOUR_TABLE is a high overhead query and should not be avoided by the Django ORM developers. Doing queries in the any other way leads to unreadable SQLs in some cases, like Geoffrey having tables with many columns http://image.slidesharecdn.com/hatedjangopart2-1220820560497639-9/95/why-i-h... Perhaps someone needs to bring this argument to rest once and for all .... http://www.slideshare.net/iamcal/why-i-hate-django-part-22-presentation?next... Why I hate Django: www.youtube.com/watch?v=i6Fr65PFqfk Martin.
5. If you can find the top 5 queries by time, do an 'explain' on each one and you'll see whether indexes will help. Be careful because indexes can slow down INSERTs and UPDATEs. 6. You might be able to reduce what gets logged - although that's a minor improvement usually. 7. MySQL works better under Linux. I know that's a big change but it's worth thinking about over the longer term.
-Adam
-- Kili - Cloud for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> More Musings: varud.com About Adam: www.linkedin.com/in/adamcnelson
On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia <ggitagia@gmail.com> wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
On the other hand, a developer should almost always specify column names when doing INSERTs into a table. We all know what happens when a schema change occurs on the database and your code suddenly stops working. Martin. On Tue, Nov 18, 2014 at 5:10 PM, Martin Akolo Chiteri < martin.chiteri@gmail.com> wrote:
On Mon, Nov 17, 2014 at 2:23 PM, Adam Nelson via skunkworks < skunkworks@lists.my.co.ke> wrote:
Geoffrey,
1. There shouldn't be any MyISAM tables at all (aside from maybe the information schema), that's a problem right there. 2. Hopefully you're on Windows Server 2012? 3. It seems like you should still be able to move onto MySQL 5.6 which has InnoDB improvements.
Hi,
4. 'SELECT *' should be rare in code.
I highly suspect that this is a myth ....
It would be better if the developers were using an ORM and weren't writing very much SQL but that's probably not under your purview.
For some reason I cannot access Youtube at the monent but. One of the keynote talks at 2008's DjangoCon had Cal Henderson (Flickr's creator) dispute the fact that SELECT * FROM YOUR_TABLE is a high overhead query and should not be avoided by the Django ORM developers. Doing queries in the any other way leads to unreadable SQLs in some cases, like Geoffrey having tables with many columns http://image.slidesharecdn.com/hatedjangopart2-1220820560497639-9/95/why-i-h...
Perhaps someone needs to bring this argument to rest once and for all ....
http://www.slideshare.net/iamcal/why-i-hate-django-part-22-presentation?next...
Why I hate Django: www.youtube.com/watch?v=i6Fr65PFqfk
Martin.
5. If you can find the top 5 queries by time, do an 'explain' on each one and you'll see whether indexes will help. Be careful because indexes can slow down INSERTs and UPDATEs. 6. You might be able to reduce what gets logged - although that's a minor improvement usually. 7. MySQL works better under Linux. I know that's a big change but it's worth thinking about over the longer term.
-Adam
-- Kili - Cloud for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> More Musings: varud.com About Adam: www.linkedin.com/in/adamcnelson
On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia <ggitagia@gmail.com> wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Excessive creation of temp-tables in mysql is often a sign of missing indexes, or excessive use of ill-conceived queries with subqueries/outer-joins. You might get a "quick fix" but turning on or increasing the query cache in Mysql. But the better solution is to debug the problem here is one approach: 1. enable the slow query log http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html 2. Run the application - or wait for the users to do it over some time. 3. examine the log and identify the problematic select queries. 4. run "explain" manually on those select queries to identify the tables/colums missing indexes. 5. Create missing indexes. 6. Check your stats/performance to see if it fixed the problem "enough". 7. Send an email to the developer telling him to take a course in db design ;-) .. Mike On 11/17/14 12:08 PM, geoffrey gitagia via skunkworks wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Geoffrey, there is this freeware, Spotlight on MySQL, which I have found very useful for continuous visualisation of that MySQL is upto, in real time. http://software.dell.com/products/spotlight-on-mysql/ On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia via skunkworks < skunkworks@lists.my.co.ke> wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
Virtualization in a high io environment like a database is never a good idea.Google I/O blender effect. Date: Tue, 18 Nov 2014 05:29:31 +0300 To: ggitagia@gmail.com; skunkworks@lists.my.co.ke Subject: Re: [Skunkworks] Inno db io optimization From: skunkworks@lists.my.co.ke Geoffrey, there is this freeware, Spotlight on MySQL, which I have found very useful for continuous visualisation of that MySQL is upto, in real time. http://software.dell.com/products/spotlight-on-mysql/ On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia via skunkworks <skunkworks@lists.my.co.ke> wrote: Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache. _______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke -- Regards, Peter Karunyu ------------------- _______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Thanks to all yee skunks, I am now getting a clear picture of what's going on , now as a solution I want to buy an ssd and then attach it to my systems vm such that I avoid the blender effect ,in your opinion will this 'cure' my system? Or will I still face a io bottle neck? Regard Geoffrey On Nov 18, 2014 8:14 AM, "john doe" <kuwasiliana@hotmail.com> wrote:
Virtualization in a high io environment like a database is never a good idea. Google I/O blender effect.
------------------------------ Date: Tue, 18 Nov 2014 05:29:31 +0300 To: ggitagia@gmail.com; skunkworks@lists.my.co.ke Subject: Re: [Skunkworks] Inno db io optimization From: skunkworks@lists.my.co.ke
Geoffrey, there is this freeware, Spotlight on MySQL, which I have found very useful for continuous visualisation of that MySQL is upto, in real time.
http://software.dell.com/products/spotlight-on-mysql/
On Mon, Nov 17, 2014 at 2:08 PM, geoffrey gitagia via skunkworks < skunkworks@lists.my.co.ke> wrote:
Thanks Adam I am running on a windows environment so I have 5.5 on it , I noticed is writes alot of temp files and at times converts to isam tables ,meaning that my io is going to be high ,from me resource monitor I can see lots of disk activities, it even at times freezes the server for a few seconds, another problem is that the tables have many columns of which the dev uses select * statements, impacting on cache.
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
A good read http://www.devside.net/wamp-server/wamp-is-running-very-slow On Mon, Nov 17, 2014 at 10:52 AM, geoffrey gitagia via skunkworks < skunkworks@lists.my.co.ke> wrote:
Hi all Wanted to know how to improve my io performance on innodb , the situation is like this ,32 bit mysql ,virtualized with hyper-v , the db is on a 200gb vhd and space is not an issue, please note nothing can be changed in the environment unless you suggest putting log files to a different partition, but no changing of the sql server.
Regards GG
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (7)
-
Adam Nelson -
geoffrey gitagia -
john doe -
Martin Akolo Chiteri -
Michael Pedersen -
Patrick Kariuki -
Peter Karunyu