# Looking for some help with optimizing some settings... **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 13, 2009, 1:29am UTC](https://forums.percona.com/t/looking-for-some-help-with-optimizing-some-settings/1170 "2009-05-13T01:29:43Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![travisbell](https://avatars.discourse-cdn.com/v4/letter/t/96bed5/32.png) [@travisbell](https://forums.percona.com/u/travisbell)\ **Post date:** [May 13, 2009, 1:29am UTC](https://forums.percona.com/t/looking-for-some-help-with-optimizing-some-settings/1170/1 "2009-05-13T01:29:43Z")
Hey guys, I run a website called TMDb ( [URL][http://www.themoviedb.org/[/URL]](http://www.themoviedb.org/%5B/URL%5D) ) 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! ---
**Author:** ![danielsan474](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@danielsan474](https://forums.percona.com/u/danielsan474)\ **Post date:** [May 13, 2009, 3:07am UTC](https://forums.percona.com/t/looking-for-some-help-with-optimizing-some-settings/1170/2 "2009-05-13T03:07:29Z")
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 )) ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [May 14, 2009, 2:31pm UTC](https://forums.percona.com/t/looking-for-some-help-with-optimizing-some-settings/1170/3 "2009-05-14T14:31:24Z")
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.