23

I have been experiencing dirty pages flushing problem in MySQL server. This occupies lot of CPU resources and my site becomes down because of lack of resources left in server. I got 10 Million records in database and it will grow further. My tables are running in innodb and since its transaction based site, I need innodb. Will migrating to Percona server remove my dirty pages flushing problem. Generally is it better to migrate or stick to MySQL?

Once I move to percona, if I want to revert back to Mysql can i restore the backup from percona to mysql?

devuser29
  • 233
  • 1
  • 2
  • 4

4 Answers4

30

Percona Server has many improvements to flushing. But on such a small database, it is likely that something is wrong, such as the buffer pool and log file size being at their defaults instead of configured for a production workload. I agree with other posters that you need to measure and understand the problem before trying something to see if it will solve it. I disagree that a migration to Percona Server needs to be regarded as a risky and time-intensive move, however. You can simply replace /usr/sbin/mysqld with the one you extract from Percona Server's tarball, and if you don't like it, put the old binary back. Percona Server is just MySQL with some non-disruptive improvements and you can be confident that switching to/from it will Just Work. (I work for Percona, by the way.)

5

Percona Server does offer a lot of performance benefits over "vanilla" mysql. It has some features that are specifically designed to increase flushing performance, especially on SSD servers.

You can roll back to normal mysql if Percona Server doesn't work out for you. There are a few "extra" features that are not enabled by default that will break backwards compatibility. Just don't change those (documented in the Percona Server documentation) and you will be fine.

My company is using Percona Server 5.5 with XtraDB on a High end SSD server and we have nad nothing but great experiences with it.

Jericon
  • 4,992
  • 3
  • 20
  • 22
0

Dirty page flushing also happens with MariaDB or Percona Server. You have to understand why and when you have spikes of dirty page flushing. Typically a too small InnoDB Log File size is the problem. Have you already checked this. A far more advanced topic is the flushing rate of MySQL (which is limited by default 200 pages/s). But for this you need to measure out your system very well...

shinguz
  • 11
  • 1
0

Do the following:

  1. Reproduce the performance problem on non-production hardware, but production-grade hardware in your lab
  2. Upgrade to Percona server, or carry out other changes, and measure performance to find out whether it fixes it.
  3. Assuming your results are good, carry out MASSIVE FUNCTIONAL REGRESSION TESTING on your application, to check that there are no regressions introduced by the move to Percona server.
  4. Carry out Soak and stress testing on your application - on your non-production system with production-sized data (or bigger).
  5. Test the roll-back plan, to verify that you can actually successfully go back to MySQL
  6. If you are TOTALLY happy with the results of ALL OF THE ABOVE, then you can plan the release to production.

But honestly, if you don't want to do those things, consider finding another way to fix it. Maybe there is a way you can fix your application to store fewer data, modify data less often, etc.

Perhaps you can add more powerful hardware?

10 million records is absolutely tiny and even the smallest of servers (say, 16G ram) should be able to keep the entire DB in memory. So unless you're doing massive amounts of updates,

Can you not find a less risky way of doing it?

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • Yes I am trying to do less risky way. But simultaneously I have asked my team to try percona and meanwhile I am working to solve this issue. The server is really. We just got the the server to accommodate for future load. dual hex core and 48 gb ram with SAS RAID 10 – devuser29 Nov 02 '11 at 16:39