Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL RAM Memory Configuration

Posted on 2011-09-16
4
528 Views
Last Modified: 2012-05-12
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
Comment
Question by:cyber-33
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36552632
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
 

Author Comment

by:cyber-33
ID: 36576960
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36577437
What is your new my.ini file look like? Please upload as an attachment.
0
 

Author Comment

by:cyber-33
ID: 36713738
I have resolved the issue. Your original suggestion was very helpful. Thank you!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question