• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

MySQL

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
Chris
Asked:
Chris
  • 3
  • 3
  • 3
  • +2
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
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
 
Seth SimmonsSr. Systems AdministratorCommented:
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
 
bigeven2002Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
arnoldCommented:
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
 
ChrisDirector of Information TechnologyAuthor Commented:
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
 
arnoldCommented:
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
 
bigeven2002Commented:
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
 
ChrisDirector of Information TechnologyAuthor Commented:
@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
 
bigeven2002Commented:
Certainly.  I'll wait to hear back.
0
 
arnoldCommented:
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
 
ChrisDirector of Information TechnologyAuthor Commented:
We are proceeding with the purchase of a proper server with the RAID configuration mentioned here.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now