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.
Add a comment
|
2 Answers
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.
answered Jun 21, 2010 at 7:41
-
Thank you. I get 6000req/sec on a static file, and about 160req/sec on mysql backed dynamic content. But the thing is about 40% results in failed request, while the same thing running on a shared host (DreamHost), the req/sec is much lower but there are not failed request at all. I think I will need to increase que time as well? Jun 21, 2010 at 7:48
-
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.
-
Right in the point, but CMS usually have tunned indexes by default. Jun 21, 2010 at 10:01