* Disk I/O issues - InnoDB log file size - Hard limit of disk I/O capacity ??? * CPU cores are limited to what the version of MySQL supports * Log file size mostly addresses peak usage and not sustained higher usage --- ## Common issues - Furious flushing ??? * Yes, 600 IOPS is non-RAID spinning rust EBS --- ## Common issues - Query Cache ??? * Drupal is supposed to be mostly reads, most customers have 99% reads --- ## Common issues - Query Cache --- ## Common issues: Traffic * deploy with cache clear (drush cc all) * crawlers * lots of admin edits * user registrations / auth traffic * query cache ??? * due to the nature of drupal, certain types of traffic tend to bypass caches and go straight to the db --- ## Common issues: Traffic ![deflect](bounce.gif) --- ## Common issues: Traffic * deploy with cache clear (drush cc all) - maint mode * crawlers - deflect upstream * lots of admin edits -> semaphore, *revision - perhaps make an edit web node, reserved * user registrations / auth traffic - fix everything and scale * query cache (watch throughput and thread status) - increase - yes, really, sometimes. - zero + mutex --- ## Your role ![choice](anteater.jpg) --- ## Your role * not often fixing queries or adding indexes - it is code, we can't easily change code * monitor workload - statsd - new relic - whatever * provide useful feedback to developers - more than just slow queries * where are they being generated? --- class: left, bottom, chapter-header ### I am sure there are none. ## questions? --- class: left, bottom, chapter-header ### For not falling asleep ## Thank you! --- ## extra1 - cache_form cron fail ![cron](cacheformcron.png) --- ## extra2 - cache_form crawler fail ![crawler](crawlerhittingforms.png) --- ## extra3 - Long Transactions