Solved

MySQL RAM Memory Configuration

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP get months from date search query where user 40 61
Mysqli Query 5 48
how do i form the query to get different columns total count 13 43
MySQL Grouping 2 23
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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now