Announcement

Announcement Module
Collapse
No announcement yet.

MySQL 5.1 performance issues

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL 5.1 performance issues

    Hello,

    We have replication setup having MySQL 4.1 acting as Master Database and MySQL 5.1.32 on replication slaves, We have five (5) replication nodes for read operations. We are in the process of upgrading our database servers to MySQL 5.1 from MySQL 4.1. Initially, we upgraded slaves nodes and upgraded three (3) slaves to MySQL 5.1 while 2 slave nodes are still running MySQL 4.1 (Master database is still on MySQL 4.1).

    From the day we upgraded our database server to MySQL 5.1. We are facing serious performance issues. We are facing locking issues on MySQL 5.1 slaves queries are stuck quite regularly in queue and state is locked because of this server ran out max-connections and we are getting error "Too many connections". Interesting thing is that whenever MySQL 5.1 slaves ran out to max-connections server load is zero.

    To prove my point, MySQL 4.1 slaves are still running fine enough while we are facing issues only with MySQL 5.1. I taught it might be because of master database
    version is MySQL 4.1 and we are replicating it to MySQL 5.1 slaves and MySQL 4.1 slaves. We are using old replication format i.e. STATEMENT BASED REPLICATION.
    Beyond of this we are using ldirectord as loadbalancer on MySQL slaves to distribute read queries.

    Is there any compatibility issues b/w MySQL 4.1 as master db and MySQL 5.1 as slave db. Anyone else faced this sort of issues ?

    server : CentOS 4.7 (Final)
    MySQL Community Server (4.1.22) --> Master DB
    MySQL Community Server (5.1.32) --> 3 Slaves
    MySQL Community Server (4.1.22) --> 2 Slaves
    max_connections = 200 (slave nodes)

    Looking forward to hear from you.
    Thankyou.

  • #2
    It's hard to answer exactly, but I don't think it's a problem with replication. Rather, I think it's likely to be a problem with queries not running the same way on 5.1 as on 4.x. This is not uncommon, and if it's the case then it's a mysqld bug.

    We wrote mk-upgrade to help mitigate the risk of upgrades for exactly these types of situations.

    Comment


    • #3
      Hello,

      I thought it might because we are replicating from MySQL 4.1 (master server) to mysql 5.1 (slaves) because MySQL manual stats that:

      MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on.

      Or it might be i didn't follow proper upgrade procedure. I upgraded MySQL 4.1 servers to 5.1 by uninstalling 4.1 rpm's than i installed mysql 5.1 rpm's. Finally i didn't backup/reload databases for upgrade instead i ran mysql_upgrade script.

      What is mk-upgrade script. How it works and how to get it ?

      Regards,
      Aspen

      Comment


      • #4
        mk-upgrade is part of Maatkit (http://www.maatkit.org/) and there is documentation there.

        Comment


        • #5
          Hi,

          So now can i run mk-upgrade script on MySQL 4.1 and MySQL 5.1 having same hardware and data and check to see what sort of differences i get and compare ? And please let me know what sort of queries mk_upgrade script runs means what will be executed on backgroup.

          Secondly do you replication causing the issue as i mentioned in my previous post that replication from MySQL 4.1 master to MySQL 5.1 slaves is not recommended.

          Best Regrads,
          Aspen

          Comment


          • #6
            I think that unless replication dies with an error, it's probably fine. But I'd use mk-table-checksum to verify that you're getting good data integrity across the mysql versions. If queries produce different results in 5.1, the slave could end up with different data.

            I'm not sure I understand what your question about mk-upgrade is. You give it the queries to execute, and it runs them on both servers and tells you if there are differences.

            Comment


            • #7
              I noticed one another thing total indexes size on MySQL 5.1.32 is 9.1GB and total database size is around 50GB while on MySQL 4.1.22 servers we same data and indexes total indexes size is 7GB (2GB less than 5.1) and total database size is around 50GB.

              We are getting different total indexes sizes on both versions. Can you please little explain why that's happening and how about if i can use more key_buffer_size on MySQL 5.1 that's improve performance ?

              We have our mysql database servers contains 16GB RAM on all and we have 4GB key buffer size on both versions i.e. MySQL 4.1 and MySQL 5.1

              Thank you
              Aspen

              Comment


              • #8
                I don't know why the tables are larger, I'd have to inspect the server to find that out.

                Comment


                • #9
                  Hello,

                  One more thing i want to bring in your notice.

                  I tried to increase key_buffer_size bigger than 4 gigs on MySQL 5.1 but it's truncated back to 4GB.

                  mysql> show variables like '%key_buffer_size%';
                  +-----------------+------------+
                  | Variable_name | Value |
                  +-----------------+------------+
                  | key_buffer_size | 4294963200 |
                  +-----------------+------------+
                  1 row in set (0.00 sec)

                  mysql> SET @@global.key_buffer_size = 6442450944;
                  Query OK, 0 rows affected, 1 warning (0.00 sec)

                  mysql> show warnings;
                  +---------+------+------------------------------------------ ---------------+
                  | Level | Code | Message |
                  +---------+------+------------------------------------------ ---------------+
                  | Warning | 1292 | Truncated incorrect key_buffer_size value: '6442450944' |
                  +---------+------+------------------------------------------ ---------------+
                  1 row in set (0.00 sec)

                  When i tried to change it from /etc/my.cnf it's restarted successfully with key_buffer_size 6144MB (6GB). After that i checked back from mysql variables it shows me 4GB.

                  mysql> show variables like '%key_buffer_size%';
                  +-----------------+------------+
                  | Variable_name | Value |
                  +-----------------+------------+
                  | key_buffer_size | 4294963200 |
                  +-----------------+------------+
                  1 row in set (0.00 sec)


                  Can someone please help me out how to increase key_buffer_size on MySQL 5.1 while i also tried to increase key_buffer in MySQL 5.0 from 4GB and it's done without any problem.

                  Thank you.

                  Comment

                  Working...
                  X