MySQL: Moving Database from Windows to Linux

Question

I have a Windows based dedicated server have below configuration:

Windows edition: Windows Server 2008 R2 Standard Service Pack 1
Processor: Intel(R) Xeon(R) CPU X5460 @3.16GHZ 3.16 GHZ (2 Processors)
Installed Memory(RAM): 8.00 GB
System type: 64-Bit Operating system

Even, I am facing bad performance of my application. DB connections become high>100 and system starts running slower. It become dead when DB connection reached to around 200. While we defined MAX connection=800. I already tried multiple things like - MySQL configuration variables tuning Identified and improved the bad performing queries

Above steps helped a little bit but still facing very bad performance. It is just like I am swimming in the ocean without any expertise and life saving boat.

Now, I am planning to upgrade MySQL from 5.1 to 5.6 as well as switching from Windows to Linux. I guess as Open source - Linux must have multiple tools to measure performance and tuning tools for Windows. But, before this I want to be sure about below things:

• How much will it be useful in performance improvement?
• My application is running in dedicated Windows server. Switching application servers also required?
• Need to tune MySQL configuration variables again?
• What should be the configuration (hardware and software) of LINUX based system?
• What performance measurement and tuning tools are available for LINUX?

Many Thanks :)

• akuzminsky: Don't expect Linux to solve all your problems. Before suggesting a solution find your problem first. I would start with collecting all queries in a slow log with long_query_time=0 and review it with pt-query-digest. MySQL dies when number of connections high - by any chance, are the tables MyISAM? It might be table level locks problem

Answer 1

You can try Percona wizard tool to configure MySQL server parameter based on Hardware and application in use using https://tools.percona.com/wizard also there are other online tools available at https://cloud.percona.com/ all percona tools are free.

Some similar answsers to the questions can be found here: https://stackoverflow.com/questions/11574401/mysql-monitor-queries

https://stackoverflow.com/questions/12026488/mysql-query-monitor-tool

• ursitesion: thanks Glenn. Links provided by you are helpful. +1 for the provided links.

Answer 2

Take a look into this Neor Profile SQL; this free tool can help you monitor and identify your bottleneck points. It did a lot of good things for me!