MySQL

Using MySQL Workbench with MAMP Pro

3
Your rating: None Average: 3 (1 vote)

I like MySQL and have been using each release of their GUI tools for Mac OS X and have slowly been getting used to the newer MySQL Workbench tool that replaced their previous GUI tools. I also use MAMP on some dev machines, so using both together is a must-do.

There is a way to setup MySQL Workbench with MAMP/MAMP Pro and these are the list of steps I did to get both apps to work together. There could be better ways so I'll update this post over time.

  1. Click "New Server Instance".
  2. Choose "localhost". Click Continue.

Tags: mac os x, mamp, MySQL

MySQL performance optimization and tuning tips

0
Your rating: None

In my work MySQL plays a big part of the dynamic web data chain, especially for many PHP based sites (like Drupal). Here are tips I've collected along the way and of course YMMV (your mileage may vary).

MySQL Tuning notes

  • Disable or remove any extensions (or modules) not being used.
  • Separate the database onto its own server (Master), and try to have a (Slave) copy as a backup or for redundancy.
  • Master-Master configurations are not easy.
  • Use EXPLAIN to dig into queries and find where optimizations can be made for performance.
  • Profile MySQL during development using the SET PROFILING flag. Enable profiling, query information_schema table to review results. Disable profiling when done.
  • Enable and tune the Query cache.
  • Enable Table cache.
  • Enable (or raise) Key buffer.
  • Enable Slow Query Log to find bottleneck queries.
  • Use MyISAM table types for fast reads, but stick with InnoDB for transactional data.

Tools, Utilities, and Resources

mysqltuner - http://blog.mysqltuner.com -

Useful Commands

EXPLAIN <QUERY>

SHOW FULL PROCESS LIST;
SHOW PROCESS LIST;

Tags: MySQL