Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
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? –  Nina Sonbolian Jun 21 '10 at 7:48
    
You should examine your's http server log for errors. –  SaveTheRbtz Jun 21 '10 at 17:03

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.

share|improve this answer
    
Right in the point, but CMS usually have tunned indexes by default. –  SaveTheRbtz Jun 21 '10 at 10:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.