Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

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!
0
cyber-33
Asked:
cyber-33
  • 2
  • 2
1 Solution
 
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
0
 
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!
0
 
Kevin CrossChief Technology OfficerCommented:
What is your new my.ini file look like? Please upload as an attachment.
0
 
cyber-33Author Commented:
I have resolved the issue. Your original suggestion was very helpful. Thank you!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now