How do I increas MySQL Performance by throwing Memory at it?

Question

I have a machine with 1 GB of RAM and have set apache to use STARTSERVER OF 150, and MAXCLIENTS of 150, only half is used so I still have about 500 MB more of spare RAM. I want MySQL to use this up as right now running AB test shows that with a static image I get 6000req/sec and with my CMS I am getting only 180req/sec which is a php script with MySQL backend. I need to increase MySQL performance without touching the SQL statements, just a a net admin rather than as a programmer. Does MySQL have this option? 500MB of spare RAM, I want to use that up.

Answer 1

If you are using MyISAM increase

key_buffer_size=512M

If you are using InnoDB

innodb_buffer_pool_size=512M

Also there is usually my.cnf presets in MySQL packages called my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf you can search for them.

PS. You should really google for some MySQL tuning examples for you storage engine.

Answer 2

Once you're done tuning the memory settings, improving db performance generally comes down to creating indexes or otherwise rearranging the physical structure of your data for better locality, for instance by clustering on an index.

Of course, there's plenty of overhead in maintaining an index as well, don't make any changes unless you know what you're doing. And don't expect a db to use its indexes until there are a few K rows in a given table.