Looking for some help with optimizing some settings...

[travisbell]

Hey guys,

I run a website called TMDb ( http://www.themoviedb.org/ ) and as the db is growing, I am finding there to be a lot of slow queries. Even simple things like a simple search across a table of 300 records will take 5 seconds…

The db is about 2 GB in size.

The box this DB is on is a dual quad core w/ 4GB of RAM, 2GB could easily be allocated to MySQL… everything is indexed, and there isn’t much data that changes (so the indexes don’t generally need to be re-written that often) I really don’t understand what I can do here. Any and all help would be great.

My my.cnf file looks like the following:

[mysqld]
max_allowed_packet=32M
character-set-server=utf8
query_cache_size=32M
table_cache=1024
key_buffer=256M
thread_cache=128
thread_concurrency=12
read_buffer_size=1M
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log

I am running MySQL 5.1.

Thanks for any help you guys can provide!

[danielsan474]

Hi Travis,

i am not sure if you run MySQL on linux distro, but you could find (in debian/ubuntu) some examples of configuration for different uses. Your option seems quite good but is not science. Take a look to your slowest query with the EXPLAIN sql command and you could find some bottlenecks in your code.

Looking a good conf for a data warehouse on MySQL, I’m trying this conf:

[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer_size = 1G #1/4 of RAM
max_allowed_packet = 5M
table_cache = 512
sort_buffer_size = 5M
read_buffer_size = 5M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
thread_concurrency = 64

ps: in the same way, if someone could help me too could be nice ))

[vgatto]

I’ll agree with danielsan, you should start with EXPLAIN and find out if any of your slow queries need to be re-written. All of the config tuning and hardware upgrades aren’t going to buy you much if your queries are terrible. If it takes 5 seconds to get 300 rows, you’ve either got an extremely complex query, an overloaded server, or improper indexing.

If you want to help tuning a particular query, post the results of EXPLAIN, SHOW INDEXES, SHOW CREATE TABLE and SHOW TABLE STATUS. With that level of information, you can usually get a good response.

Regarding config tuning advice, you need to explain more about your work load. Are you using MyISAM or InnoDB? Are you doing lots of large joins? Are you aggregating or pulling single rows?

You can usually figure out how best to set key_buffer_size and innodb_buffer_pool_size by looking at the output of SHOW STATUS (Key_reads vs. Key_read_requests or Innodb_buffer_pool_read vs. Innodb_buffer_pool_read_requests).

If you’re using MyISAM, you’ll want to leave a good chunk of memory to the OS for the filesystem cache, since MyISAM does not cache data pages in memory. It looks like danielsan’s config does a good job of this, but its all really dependent on how much you need indexes in memory vs. how much you need the data in memory. For example, if you make extensive use of covering indexes, you may need a higher key buffer size.

For InnoDB, you’ll want to put much more into the innodb_buffer_pool_size, just leaving enough breathing room for your per-connection memory needs (read buffer, sort buffer, any in-memory temp tables, etc). Again, this is all dependent on your workload.