200 likes | 208 Views
Learn about scaling MySQL in a three-dimensional approach that encompasses storage choices, engine options, and high volume reads and writes. Discover how to optimize performance, choose the right filesystem, and manage large datasets efficiently.
E N D
scaling MySQL in 3d sarah novotny – sarah@bluegecko.net open databases and LAMP services www.BlueGecko.net
things you’ve heard about scale write 1 / read many partitioning / sharding multimaster / rings memcached / nosql www.BlueGecko.net
large datasets • high volume reads • high volume writes http://www.flickr.com/photos/elbragon www.BlueGecko.net
storage choices engine options storage engine filesystem volume manager hardware http://www.flickr.com/photos/shuttercat7 www.BlueGecko.net
large datasets large objects many rows http://www.flickr.com/photos/olivander www.BlueGecko.net
storage flexibility, reliability, clone-ability http://www.flickr.com/photos/wwworks www.BlueGecko.net
http://www.flickr.com/photos/alreadytaken www.BlueGecko.net
high volume reads more memory fast disks more memory http://www.flickr.com/photos/teclasorg www.BlueGecko.net
myisam vs innodb http://www.flickr.com/photos/redjar www.BlueGecko.net
not to be obvious, but -- read less data! compress data (if you can) don’t use limit http://www.flickr.com/photos/rogersmith www.BlueGecko.net
use thoughtful primary keys www.BlueGecko.net
a short diversion to swap or not to swap that is the question www.BlueGecko.net
high volume reads writes more memory fast disks more memory fast disks http://www.flickr.com/photos/teclasorg www.BlueGecko.net
high volume writes choose your filesystem well understand how your filesystem and raid controller work together tune them to work in concert www.BlueGecko.net
facebook game case: highly concurrent writes low risk of -- omg, i lost my most recent score! www.BlueGecko.net
shard data innodb_log_flush_at_trx_commit=0 benchmark i/o schedulers www.BlueGecko.net
free tools innotop maatkit MySQL proxy monitoring/trending cacti templates $monitoring_server – the one you know www.BlueGecko.net
additional resources • irc.freenode.org • #mysql • #maatkit • mysql.com • HPM2e -Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, and Jeremy Zawodny www.BlueGecko.net
credits swap image http://www.vocw.edu.vn/content/m10106/latest/ special thanks to gabriel cain and mike hamrick for suggestions on content and slides www.BlueGecko.net
Blue Gecko and contact info • sarah@bluegecko.net • sarah.novotny@gmail.com • @sarahnovotny • @bluegecko • senk on #mysql Blue Gecko provides Remote DBA services for companies around the world 7x24x365 support including monitoring, performance analysis, proactive maintenance and architectural guidance for small and large datasets. www.BlueGecko.net