0

I'm configuring mySQL buffer sizes. I find it's really slow. I'm running mySQL on a i5 mac mini with 16GBRAM OS X Mountain Lion. My database size is about 10GB. This is the copy of my.cnf file and I hoped someone could offer some suggestions about getting better performance.

[client]
socket = /var/mysql/mysql.sock

[mysqld]
socket = /var/mysql/mysql.sock

key_buffer_size = 2536M
read_buffer_size = 16M 
performance_schema = ON
query_cache_size = 128M
query_cache_type=1
query_cache_limit=16M
bulk_insert_buffer_size = 1000M
raw-bin hood
  • 5,839
  • 6
  • 31
  • 45
  • what engine(s) do you use? How much tables per engine do you have? MySQL version? Read/write? – ravnur Apr 05 '13 at 12:50
  • Thanks. I'll try to answer your questions, but I'm not sure if my answer is correct. The engines I use are InnoDB. I have about 30 tables in the db. Some are very large tables (>4GB) and most contain over 4 million rows. One table contains 200 Million rows. I use 5.5.29. Should I change the version considering I am using such large data? The tables are mostly for reading. Sometimes I will try to write new tables from the data, but I'm getting timeout errors as well. – raw-bin hood Apr 05 '13 at 13:23
  • `innodb_buffer_pool_size = 12G` and you should experience less available ram and faster DB. – N.B. Apr 05 '13 at 14:13

1 Answers1

1

The variables you're tuning are mainly MyISAM related. For InnoDB have a look at: what-to-tune-in-mysql-server-after-installation, innodb-performance-optimization-basics and choosing-innodb_buffer_pool_size

tersmitten
  • 1,310
  • 1
  • 9
  • 23