Posted on 2016-09-23
Last Modified: 2016-10-12
I recently started a new job that uses a MySQL server (ver 5.5) running on Windows Server 2012 R2 Standard to host the database used by the entire company (100+ workstations). The database is hosted on the C: drive on a server with 2 hard drives in RAID 1. We are experiencing sever performance issues and from looking at server performance, the storage is the bottleneck. I want to move the database to a NAS connected to the server via iSCSI and have a couple of questions:

1. Is running the database on a NAS via iSCSI a recommended configuration? In my mind, it cannot be worse than what we already have.

2. Is moving the database as simple as creating a directory on the NAS, copying the data folder over, and changing the "datadir" value in the my.ini file?

Any help would be appreciated.

Question by:Chris
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
  • 3
  • 3
  • 3
  • +2
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41813307
No, it is never that simple.  If you move the data to the NAS and leave the program on the server, you are incurring two network round trips minimum between the program and data for every query.  I can't see how that would speed it up.
LVL 35

Expert Comment

by:Seth Simmons
ID: 41813312
i would change storage
you would get better performance using raid 10
at my last place, we had several systems with client data using mysql 5.1 (was a few years ago) on linux and saw a performance improvement using raid 10 compared to raid 5
agree with dave that although the storage might be faster itself, you are incurring more network activity so there is no benefit there
ssd could be another option depending on your environment and if you have money to throw at this
LVL 17

Expert Comment

ID: 41813329
Appending to what the others said, a RAID10 SSD added to the server and configured as a D drive would be best.  That way you get a major performance boost and the I/O stays local to the machine.  Raid 5 is not ideal for SQL as it will incur a performance hit with write operations.

As for moving MySQL, you are on the right track, these should be the correct steps below as referenced from this site.

1. Shutdown mysql.
2. Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
3. Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
4. Verify and change the permissions at the destination if applicable.
5. Start mysql.
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 78

Expert Comment

ID: 41813407
Any option, consideration to move the server to Linux. OS overhead on a Windows platform is significantly higher. Adding more memory to the system sounds what will make an immediate impact on the pergormance in the existing setup.
The db gas grown such that data needs to be swapped in and out of memory, (page to disk)

Current server spec, memory, CPU, database size.
If you have iis, see if you have phpmyadmin installed. If not, install and it may help you identify performance related issues if an index, or a change in allocation of temporary table space for queries, or identify a slow query that causes this impact.

Relocating the db to another storage area local on the server or remote using a completely separate dedicated network connection, might have no improvement of performance.

Author Comment

ID: 41813647
Good morning everyone,

Thank you for all the responses. Some additional info. The server has a 4-core Xeon with 16GB of memory. Current memory utilization is <40% and the CPU is averaging less than 20% utilization at our busiest time.

The application we are using (OpenDental) is a client-server application with each workstation having the app installed and simply accessing the database on the server. From looking at performance statistics, there are significantly more reads than writes going to the drive.

Not sure if that makes a difference to your thoughts on moving to the NAS. I am looking at options now that include a new server and will take the RAID 10 recommendation into consideration as that seems to be the consensus on how the storage should be configured.

Would an SSD array with six or eight drives in RAID 5 be an option or is RAID 10 the only solution to consider?
LVL 78

Expert Comment

ID: 41813717
SSD would help if io from disk is the issue.
Using tuning or phpmyadmin, the issue might be that your configuration settings dealing how much memory/cache MySQL can use can be increased to improve performance
Under allocation of resources (temp settings) might require MySQL to flush pages in and out of memory (write out to disk )

Phpmyadmin is a php web based interface to MySQL that includes suggestions based on MySQL statistics to improve performance.
LVL 17

Accepted Solution

bigeven2002 earned 500 total points
ID: 41813787
I have been reading online and others have said NAS isn't recommended for SQL due to the possible instability with the network access.  A SAN may be more appropriate as it would use its own isolated channel.  But local storage trumps both.

If wanting to use RAID 5, then personally I wouldn't use more than 4 or 5 disks, any more than that and you are increasing the chance of failure.  Regardless of the number of disks, you can still only lose 1 drive with the tolerance.  For 6+ drives, use RAID 6.  That has slightly lower performance from RAID 5 but it can tolerate the loss of 2 disks.

Also, another thing to note is RAID controllers typically don't support the SSD TRIM command, so it doesn't do the garbage collection to preserve performance.  However, I think the most modern SSDs are overcoming this obstacle so when you are ready to move forward, be sure you are getting at least the SAS SSD Enterprise grade drives.

The above is assuming that the write operations will continue to be minimal compared to read operations.    If the write operations have the possibility of becoming much more frequent, then still take RAID 10 into consideration.

Author Comment

ID: 41813896

Thank you for your response. I initially thought this was well and when I checked the my.ini file, there was not much in there other than the path to the data directory, default storage engine, and max allowed packets. I made some changes to it in the hopes of increasing performance and stability and while we're not crashing hourly anymore, performance is still sluggish. I've put the current config below. If you have any suggestions for changing whats there or adding additional values, it would be appreciated

basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"


Thank you for your reply. I'm looking into RAID 10 seriously now. I just need to convince management to spend the $$$ on the server.
LVL 17

Expert Comment

ID: 41813923
Certainly.  I'll wait to hear back.
LVL 78

Expert Comment

ID: 41813984
there are many parameters, dealing with cache's, query and tmp table allocation, etc.
as well as recording slow query......

PHPmyadmin is a self contained PHP directory that can be added as a virtual Site including on a workstation where IIS express can be installed with PHP as long as this system can connect to the mysql to pull the statistics.

status, advisor will retrieve the statistics from the mysql server and will provide suggestions on what adjustments might improve the performance.

show variables where Variable_name like '%tmp%' or Variable_name like '%query%' or Variable_name like '%heap%';

Author Closing Comment

ID: 41840542
We are proceeding with the purchase of a proper server with the RAID configuration mentioned here.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySqli Real Escape String and SQL Injection 1 50
Find unused columns in a table 12 76
MySQL-Design Help 12 44
Access control a form field in Lotus Notes 3 29
Each year, investment in cloud platforms grows more than 20% ( as an increasing number of companies begin to…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This Micro Tutorial will teach you how to reformat your flash drive. Sometimes your flash drive may have issues carrying files so this will completely restore it to manufacturing settings. Make sure to backup all files before reformatting. This w…

730 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