Which version to use when upgrading from MySQL 4.1?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Which version to use when upgrading from MySQL 4.1?


    I've been following Percona's developments closely for years and am impressed with the products.

    My machine has an AMD dualcore 185 with 2GB of RAM. Linux 2.6.9. It also has PHP and Lighttpd running on it. I'm still using MySQL 4.1.22 with MyISAM tables and the database is about 3.7GB in 67 tables. The tables with text data have been broken up so that the main meta data are in fixed tables and the text is mostly cached so those tables aren't read from much. The DB is heavy on the read side with a lot of composite primary keys. Also, backups are made nightly using mysqlhotbackup (which locks the DB for about 10min).

    My question: Is it worth it to upgrade to Percona's 5.5? I'm concerned that the new version will use up more RAM or increase IO, which would increase the load on the sever with no real benefit.

    Some details from my.cnf:
    thread-cache-size = 10
    query-cache-min-res-unit = 2048

    Any thoughts on the direction to take?


  • #2
    Because you use MyISAM, I assume you do not mind losing data and you do not care about data integrity. A conversion to InnoDB will make MySQL use more memory, especially for secondary indices because your primary key is large and it is stored for every leaf in a secondary index. You may benefit from page compression though.

    Even if you keep running MyISAM, you may or may not benefit from an upgrade, but differences are likely to be small.

    A real reason for an upgrade would be the bugfixes or the better performance metrics.


    • #3
      Right, losing a day's worth of data or getting a few corrupt rows isn't a big deal. It's never happened yet. A few tables have over 3million rows.


      • #4
        What's your reason to look for an upgrade? Why not bump the memory up to 8 GB? And what is the number of queries/sec, and what does the majority of your queries look like?


        • #5
          Performance isn't an issue. My concern is any possible bugs that might exist since that version is so old. I'd also like to decrease the downtime when creating nightly backups.

          I just ran mysqlreport and got:

          __ Key:
          Buffer used 110.49M of 300.00M %Used: 36.83
          Current 99.50M %Usage: 33.17
          Write hit 24.90%
          Read hit 99.81%

          __ Questions
          Total 703.87M 53.4/s

          DMS 479.58M 36.4/s 68.13
          SELECT 471.71M 35.8/s 67.02 98.36
          UPDATE 3.89M 0.3/s 0.55 0.81
          REPLACE 1.59M 0.1/s 0.23 0.33
          DELETE 1.36M 0.1/s 0.19 0.28
          INSERT 1.02M 0.1/s 0.15 0.21


          • #6
            If you move to InnoDB your backups won't cause downtime but you may need more memory. If you stick with MyISAM, you could set up replication with a second mysqld running on the same server and backup the slave. The slave could run with very little resources.


            • #7
              I thought of the idea of running a slave, but isn't replication too flakey/buggy in 4.1? I'd just need to turn on the slave periodically to catch up. Also, wouldn't the master replication log increase the IO a lot?

              Thanks for all the good feedback!


              • #8
                I haven't run replication with 4.1. What's your current io (e.g. give some output of iostat -dx 1 during a busy period)?


                • #9
                  The version you are using does have a lot of bugs, but it also doesn't have a lot of the new features in 5.0 and newer, which added their own bugs. As a result, there is a very high exposure to bugs when you upgrade, so you should do it very carefully IMO. Another way to consider this is whether the application and usage is changing, or static. If it's running great, and nothing's changing, well, you're just fine -- you might have bugs, but they're not affecting you. If you're changing the way you use the database, at some point you'll hit one of the bugs. And because you are on a machine with rather limited resources, this is one of the cases where I might actually try to stay on the version you're on until you need to upgrade the hardware.


                  • #10
                    i see that it is almost no write on your DB,you can do full back once a month ,and turn bin log on (it will small due to little write) ,use it for increase backup,that will not cause you DB downtime