Difference between revisions of "Mysql notes"

From Simson Garfinkel
Jump to navigationJump to search
m (Created page with 'Notes on MySQL: I generally store my MySQL password in an environment variable (like MYSQL_PASSWORD) and then have all of the mysql commands aliased to use the password, e.g.: …')
 
Line 12: Line 12:
   key_buffer              = 8192M
   key_buffer              = 8192M
   innodb_buffer_pool_size = 1024M
   innodb_buffer_pool_size = 1024M
  sort_buffer_size = 100M
  read_buffer_size = 100M
  read_rnd_buffer_size = 100M
  myisam_sort_buffer_size = 100M
  query_cache_size = 1024M
  # Try number of CPU's*2 for thread_concurrency
  thread_concurrency = 8


Try these commands:
Try these commands:

Revision as of 11:33, 12 December 2009

Notes on MySQL:

I generally store my MySQL password in an environment variable (like MYSQL_PASSWORD) and then have all of the mysql commands aliased to use the password, e.g.:

 alias mysql="mysql -p$MYSQL_PASSWORD"
 alias mysqladmin="mysqladmin -p$MYSQL_PASSWORD"


Tuning MySQL

Look at the system load. If mysqld is not consuming 100% of the system load, then it is disk-bound, not CPU bound. You need to increase the buffers.

In the file my.cnf, try editing these values and making them larger until the system is CPU bound:

 key_buffer              = 8192M
 innodb_buffer_pool_size = 1024M
 sort_buffer_size = 100M
 read_buffer_size = 100M
 read_rnd_buffer_size = 100M
 myisam_sort_buffer_size = 100M
 query_cache_size = 1024M
 # Try number of CPU's*2 for thread_concurrency
 thread_concurrency = 8

Try these commands:

 mysql> SHOW INNODB STATUS\G

See also:

General

Useful admin commands
 mysqladmin shutdown


MacOS Server

MySQL is built in. I find it easier to use Apple's provided server, rather than building my own:

To restart the server (for some reason, launchctl doesn't stop the MySQL server):

 mysqladmin shutdown
 sudo launchctl start org.mysql.mysqld

Linux