MySQL Optimization from a Developer's point of view

MySQL Optimization from a Developer's point of view

• 2.
  MySQL Optimization Tips to increase performance Sachin Khosla Founder, Digimantra.com
• 3.
  What’s next ? MySQL – What’s that ?  Optimize ? Where and What ?  Quick Glance at the famous MySQL Storage Engines  Many ways but we talk about Cache systems  Query Cache  File Cache  MemCached  Solr/Lucene
• 4.
  Let’s Go ! Havea question ? • If it’s a short – Shoot it ? • If it’s a long – Save it till end of the session :)
• 5.
  As many ofyou know ! •MySQL is a relational database •Community Edition is free of cost •Free GUI tools for Server Administration •Support by the community, forums, manual/docs etc.
• 6.
  Why Optimize • Cost • Tuned system = less resources = less investment. • After tuning the system, you get a real usage stat. • Performance • Performance of the system increases the output :) • Happy developers = Happy end users • Stay in the game • Undoubtedly you want to keep pace with the latest technology • Faster systems showcase helps bring in more business
• 7.
  Who Should Optimize? Ecosystem– Who are responsible in developing that very application. 1. Designers – The documenting guys :) 2. Developers – Geeks :D 3. System/DB Administrators – The machines :P
• 8.
  Optimized, Really ? •Benchmarking • Profiling • Difference between the Benchmark & Profiling Ref - http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html
• 9.
  MySQL flavors •MyISAM –The ideal son •InnoDB – Clever but expensive* •Memory (Heap)
• 10.
  MyISAM InnoDB •Of course, there is no •Of course, there is no difference. It’s just you ! difference. It’s just you ! •You need speed and term •You understand, what “transaction” is martian for transactions are and how are you. they going to effect. •You sure about the size of •Sky is the limit – did you the data in your database. smell cloud computing,eh? •Data is stored in insertion •Rows are physically ordered order. (no clustered index) by the primary key
• 11.
  Memory (Heap) • Faster,Performance booster • Intermediary table • Use it when you have more select than insert • Table Structure remains not the data • Fixed length for data types like varchar • No support for text, blob
• 12.
  Finally, ways toboost performance • Mysql Query Cache • File Cache system • Memcached • Solr / Lucene
• 13.
  Mysql Query Cache •Internal cache system of MySQL • Caches the queries being fired • Never returns stale data • Good when you have less or almost no inserts • Be sure to choose an optimum size for cache (query_cache_size)
• 14.
  File Cache system •Traditional way of caching with files. • Saves database server ping. • Still accesses the file structure. • No Big Deal !
• 15.
  Memcached • How isit different than traditional cache ? • Can it improve my site’s application’s performance ? • How ? $mem = new Memcache(); //PECL extn in PHP $mem->connect(‘SERVER_NAME’,’PORT_NO’); //connect $mem->set(‘key’,’data’); //set memcached KV pair $mem->get(‘key’); //get data
• 17.
  Solr / Lucene •Hithighlighting, faceted search, caching, replication, and a web administration interface – Heavy terms ? •In simple terms it’s a server which has indexes of the database and when you send a request to it, it return you data in the form of XML/JSON
• 18.
  Other ways • QueryOptimization • Using Explain • Show table status (InnoDB / MySQL) • Show InnoDB status (indexes and all ) •Table level Optimization • Normalization • DB Server Level • mysqld status • Process monitoring tools
• 19.
  Q&A
• 20.
  Thank you ! www.digimantra.com Twitter - @realin FB – http://fb.me/sachinkhosla