MySQL RAM Memory Configuration

I have installed the latest version of the MySQL Server on a 64bit windows 7 pc with 8GB of RAM. I have subsequently updated the RAM to 16GB.

I am currently testing a significant dataset stored in about 10 tables, with about 100GB worth of data. The tables are configured to use InnoDB.

The queries involve many nested SELECT, JOIN and GROUP BY clauses. No inserts. No updates.

Questions:
1. how can I utilize all 16GB of RAM to improve the query performance?
2. will the performance increase if I switch to another MySQL Engine (other than InnoDB)?

Thank you, experts!
cyber-33Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
HI.

Have you taken a look at the my.ini? i.e., did you tune MySQL after installation?
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
(check out the InnoDB configuration settings in particular focusing on innodb_buffer_pool_size)

If you do switch to MyISAM, then key_buffer_size of 4GB may be helpful.

The other side of this is looking at the query. Using EXPLAIN, you can see where some of the issues are and tune accordingly. Here is another good reference:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

Regards,

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyber-33Author Commented:
mwvisa1,
Sorry for the delayed response.
 
I have followed the guidelines in the links that you provided, but my server still never reports higher than 20% usage of available memory. I wonder what parameter should I modify to increase this number.

Thank you for your help!
Kevin CrossChief Technology OfficerCommented:
What is your new my.ini file look like? Please upload as an attachment.
cyber-33Author Commented:
I have resolved the issue. Your original suggestion was very helpful. Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.