Solved

MySQL

Posted on 2016-09-23
11
69 Views
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.

Thanks!
0
Comment
Question by:Chris
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 82

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.
0
 
LVL 34

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
0
 
LVL 17

Expert Comment

by:bigeven2002
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.
0
 
LVL 76

Expert Comment

by:arnold
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.
0
 

Author Comment

by:Chris
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?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:arnold
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.
.
1
 
LVL 17

Accepted Solution

by:
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.
1
 

Author Comment

by:Chris
ID: 41813896
@Arnold,

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

[mysqld]
basedir="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"
datadir="C:/mysql/data/"
default-storage-engine=MyISAM
max_allowed_packet=40M
max-connections=350
server-id=4
query_cache_size=128M
key_buffer_size=2048M
max_connect_errors=1000

@bigeven2002

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.
0
 
LVL 17

Expert Comment

by:bigeven2002
ID: 41813923
Certainly.  I'll wait to hear back.
0
 
LVL 76

Expert Comment

by:arnold
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.
www.phpmyadmin.net

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%';
0
 

Author Closing Comment

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
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…

746 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

10 Experts available now in Live!

Get 1:1 Help Now