[Last Call] Learn how to a build a cloud-first strategyRegister Now



Posted on 2016-09-23
Medium Priority
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
  • 3
  • 3
  • 3
  • +2
LVL 84

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 36

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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 81

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 81

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 2000 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 81

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

834 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