If you want your code merged, you need to keep your database queries efficient. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? We have some resources around:
Roan Kattouw's https://www.mediawiki.org/wiki/Manual:Database_layout/MySQL_Optimization/Tut... -- slides at https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
Asher Feldman's https://www.mediawiki.org/wiki/File:MediaWiki_Performance_Profiling.ogv -- slides at https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
More hints: http://lists.wikimedia.org/pipermail/toolserver-l/2012-June/005075.html
When you need to ask for a performance review, you can check out https://www.mediawiki.org/wiki/Developers/Maintainers#Other_Areas_of_Focus which suggests Tim Starling, Asher Feldman, and Ori Livneh. I also BOLDly suggest Nischay Nahata, who worked on Semantic MediaWiki's performance for his GSoC project in 2012.
On Wednesday, March 6, 2013 at 4:36 PM, Sumana Harihareswara wrote:
When you need to ask for a performance review, you can check out https://www.mediawiki.org/wiki/Developers/Maintainers#Other_Areas_of_Focus which suggests Tim Starling, Asher Feldman, and Ori Livneh.
It's bit funny to see my name in that company: I'd wager most of the engineers at the Foundation know more about the topic than me. But happy to help if I can.
-- Ori Livneh
Database query performance isn't the leading performance bottleneck on the WMF cluster. If reading or writing to a database, certainly do take the time to specifically profile your database queries, and make sure to efficiently use caching (and avoid stampede scenarios on cache expiration) whenever possible. Hopefully in the future, caching won't be as up to individual developers to get right on an ad hoc basis. In the last year, we made changes that reduced the query load to mysql masters by nearly 70%. Those queries were well written - there was nothing to tune at the sql layer. The point being, query tuning can't substitute for or even correlate to making efficient design decisions.
If you have profiled sql queries, or if your code doesn't have any to profile, don't stop there. Profiling the code itself is at least as important. The mediawiki profiler ( https://www.mediawiki.org/wiki/Profiler#Profiling) offers an easy place to start and it's good to include profiling hooks as they automatically result in p90/p99, etc. latency graphs in graphite in production. But for individual development environments, setting up xhprof might be more useful. There are plenty of tutorials out there, such as - http://blog.cnizz.com/2012/05/05/enhanced-php-performance-profiling-with-xhp...
Ori - I think this has been discussed but automated xhprof configuration as part of the vagrant dev env setup would be amazing :)
On Wed, Mar 6, 2013 at 4:36 PM, Sumana Harihareswara sumanah@wikimedia.orgwrote:
If you want your code merged, you need to keep your database queries efficient. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? We have some resources around:
Roan Kattouw's
https://www.mediawiki.org/wiki/Manual:Database_layout/MySQL_Optimization/Tut... -- slides at https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
Roan's slides actually at https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
But! If you're a developer and would appreciate guidance around how to best create and efficiently use indexes, I highly recommend this slide deck: http://www.percona.com/files/presentations/WEBINAR-tools-and-techniques-for-...
Asher Feldman's https://www.mediawiki.org/wiki/File:MediaWiki_Performance_Profiling.ogv -- slides at https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
slides actually at https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
More hints: http://lists.wikimedia.org/pipermail/toolserver-l/2012-June/005075.html
Due to the use of views on toolserver, it isn't really possible to use that environment to profile or tune queries as they would actually run in production.
When you need to ask for a performance review, you can check out https://www.mediawiki.org/wiki/Developers/Maintainers#Other_Areas_of_Focus which suggests Tim Starling, Asher Feldman, and Ori Livneh. I also BOLDly suggest Nischay Nahata, who worked on Semantic MediaWiki's performance for his GSoC project in 2012.
-- Sumana Harihareswara Engineering Community Manager Wikimedia Foundation
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On 07/03/13 12:12, Asher Feldman wrote:
Ori - I think this has been discussed but automated xhprof configuration as part of the vagrant dev env setup would be amazing :)
I don't think xhprof is the best technology for PHP profiling. I reported a bug a month ago which causes the times it reports to be incorrect by a random factor, often 4 or so. No response so far. And its web interface is packed full of XSS vulnerabilities. XDebug + KCacheGrind is quite nice.
-- Tim Starling
On Thu, Mar 7, 2013 at 3:57 PM, Tim Starling tstarling@wikimedia.orgwrote:
On 07/03/13 12:12, Asher Feldman wrote:
Ori - I think this has been discussed but automated xhprof configuration
as
part of the vagrant dev env setup would be amazing :)
I don't think xhprof is the best technology for PHP profiling. I reported a bug a month ago which causes the times it reports to be incorrect by a random factor, often 4 or so. No response so far. And its web interface is packed full of XSS vulnerabilities. XDebug + KCacheGrind is quite nice.
That's disappointing, I wonder if xhprof has become abandonware since facebook moved away from zend. Have you looked at Webgrind ( http://code.google.com/p/webgrind/)? If not, I'd love to see it at least get a security review. KCacheGrind is indeed super powerful and nice, and well suited to a dev vm. I'm still interested in this sort of profiling for a very small percentage of production requests though, such as 0.1% of requests hitting a single server. Copying around cachegrind files and using KCacheGrind wouldn't be very practical.
On 03/06/2013 04:36 PM, Sumana Harihareswara wrote:
If you want your code merged, you need to keep your database queries efficient. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? We have some resources around:
Roan Kattouw's https://www.mediawiki.org/wiki/Manual:Database_layout/MySQL_Optimization/Tut... -- slides at https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
Asher Feldman's https://www.mediawiki.org/wiki/File:MediaWiki_Performance_Profiling.ogv -- slides at https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
And https://wikitech.wikimedia.org/wiki/Query_profiling_for_features_developers
Matt Flaschen
The advice on https://wikitech.wikimedia.org/wiki/Query_profiling_for_features_developers sounds good.
Is there more detail somewhere on how to do this part "Test your query against production slaves prior to full deployment"?
Luke
On Wed, Mar 6, 2013 at 8:14 PM, Matthew Flaschen mflaschen@wikimedia.orgwrote:
On 03/06/2013 04:36 PM, Sumana Harihareswara wrote:
If you want your code merged, you need to keep your database queries efficient. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? We have some resources around:
Roan Kattouw's
https://www.mediawiki.org/wiki/Manual:Database_layout/MySQL_Optimization/Tut...
-- slides at
https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
Asher Feldman's https://www.mediawiki.org/wiki/File:MediaWiki_Performance_Profiling.ogv -- slides at
https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
And https://wikitech.wikimedia.org/wiki/Query_profiling_for_features_developers
Matt Flaschen
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
I found EXPLAIN (http://dev.mysql.com/doc/refman/5.0/en/using-explain.html) pretty useful during my project; rather than theories it shows the exact way the query is being resolved and if the indexes are being used rightly.
On Thu, Mar 7, 2013 at 6:06 AM, Sumana Harihareswara sumanah@wikimedia.orgwrote:
If you want your code merged, you need to keep your database queries efficient. How can you tell if a query is inefficient? How do you write efficient queries, and avoid inefficient ones? We have some resources around:
Roan Kattouw's
https://www.mediawiki.org/wiki/Manual:Database_layout/MySQL_Optimization/Tut... -- slides at https://commons.wikimedia.org/wiki/File:MediaWikiPerformanceProfiling.pdf
Asher Feldman's https://www.mediawiki.org/wiki/File:MediaWiki_Performance_Profiling.ogv -- slides at https://www.mediawiki.org/wiki/File:SQL_indexing_Tutorial.pdf
More hints: http://lists.wikimedia.org/pipermail/toolserver-l/2012-June/005075.html
When you need to ask for a performance review, you can check out https://www.mediawiki.org/wiki/Developers/Maintainers#Other_Areas_of_Focus which suggests Tim Starling, Asher Feldman, and Ori Livneh. I also BOLDly suggest Nischay Nahata, who worked on Semantic MediaWiki's performance for his GSoC project in 2012.
-- Sumana Harihareswara Engineering Community Manager Wikimedia Foundation
wikitech-l@lists.wikimedia.org