Solved

MySQL RAM Memory Configuration

Posted on 2011-09-16
4
525 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 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