Announcement

Announcement Module
Collapse
No announcement yet.

5.5.25a server very slow in replication

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

  • 5.5.25a server very slow in replication

    I have setup MySQL replication with Percona 5.5.25a server as a slave for MySQL 5.0.95 master. Slave is very slow in catching up with master. It is lagging behind master for around 2 days now. Both the master and slave runs on VM nodes. Physical configuration of both the nodes are same. MySQL configuration is also same on both.

    When I change the slave to 5.0.95, slave immediately catches up and never lags behind master.

    I am using Innodb as the engine. Innodb status on the slave showed that there were locks on a table during replication. Every time an update runs on this table, the locks were held for around 15 to 20 mins. I have ignored this table in replication. After this, there are no locks observed in the slave. But the QPS on slave is only 4. With this rate, the slave is never to going to catch up with the master.

    Any suggestions on how to resolve this will be much appreciated.

  • #2
    Hi,

    I'm bit confused here as innodb should not lock the tables unless you are running ALTER TABLE or LOCK TABLE. so whatever you are seeing on slave should be something else. Can you provide "SHOW ENGINE INNODB STATUS" and "SHOW PROCESSLIST" of slave? also update statement and table structure if possible.

    I'm assuming that all tables are InnoDB in your master-slave servers. It would be also helpful if you can provide my.cnf configurations and global variables of both the servers. I would also like to suggest you to check/upgrade latest Percona server version(PS 5.5.28-29.2) and try to regenerate above issue.

    Comment


    • #3
      Thanks Niljoshi for your response.

      I stopped the slave and just ran the update query on the slave that was causing issues. This query has been running for more than an hour now. There is no other query running on the DB now. I have attached all the details from the slave node - innodb status, process list, table details, my.cnf, details from innotop.

      I am not sure whether I need to change any mysql configuration.

      Thanks for all your help.



      Regards,
      Vijay

      Comment


      • #4
        Hi Vijay,

        Can you provide EXPLAIN plan of below query, show create table and show table status of sample table for both master and slave servers?

        EXPLAIN select * from sample where creationTime > 1343786787629;

        Comment


        • #5
          Hi,

          I had stopped the replication and just ran the update query on slave node. This update query took 5 hours 25 mins to complete.

          Following is the info from slow query log

          # Time: 121212 11:14:24
          # User@Host: root[root] @ localhost []
          # Thread_id: 4 Schema: myschema Last_errno: 1160 Killed: 0
          # Query_time: 19574.502916 Lock_time: 0.000124 Rows_sent: 0 Rows_examined: 310617 Rows_affected: 0 Rows_read: 0
          # Bytes_sent: 0 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
          # InnoDB_trx_id: 151400
          SET timestamp=1355310864;
          update sample set isValid = 0 where creationTime > 1343786787629;


          Following is the explain plan for the update query on slave

          mysql> EXPLAIN select * from sample where creationTime > 1343786787629\G
          *************************** 1. row ***************************
          id: 1
          select_type: SIMPLE
          table: sample
          type: ALL
          possible_keys: NULL
          key: NULL
          key_len: NULL
          ref: NULL
          rows: 310693
          Extra: Using where
          1 row in set (0.00 sec)


          mysql> EXPLAIN select isvalid from sample where creationTime > 1343786787629\G
          *************************** 1. row ***************************
          id: 1
          select_type: SIMPLE
          table: sample
          type: index
          possible_keys: NULL
          key: sample_Idx
          key_len: 11
          ref: NULL
          rows: 310693
          Extra: Using where; Using index
          1 row in set (0.00 sec)





          5.0 Master
          mysql> show table status like 'sample'\G
          *************************** 1. row ***************************
          Name: sample
          Engine: InnoDB
          Version: 10
          Row_format: Compact
          Rows: 278859
          Avg_row_length: 261
          Data_length: 72810496
          Max_data_length: 0
          Index_length: 42647552
          Data_free: 0
          Auto_increment: NULL
          Create_time: 2012-06-26 11:04:23
          Update_time: NULL
          Check_time: NULL
          Collation: utf8_general_ci
          Checksum: NULL
          Create_options:
          Comment: InnoDB free: 5120 kB; (`id`) REFER `myschema/othertable`(`id')
          1 row in set (0.25 sec)



          5.5 slave
          mysql> show table status like 'sample'\G
          *************************** 1. row ***************************
          Name: sample
          Engine: InnoDB
          Version: 10
          Row_format: Compact
          Rows: 310693
          Avg_row_length: 150
          Data_length: 46792704
          Max_data_length: 0
          Index_length: 49233920
          Data_free: 4194304
          Auto_increment: NULL
          Create_time: 2012-12-03 12:00:31
          Update_time: NULL
          Check_time: NULL
          Collation: utf8_general_ci
          Checksum: NULL
          Create_options:
          Comment:
          1 row in set (0.08 sec)


          SHOW CREATE TABLE is the same on both slave and master.

          Explain plan is the same on master as on slave.

          Comment


          • #6
            Hi Vjay,

            Sorry for late response , I was busy with some other stuff.

            Anyway, the main issue here is, the query is doing full-table scan. So that update would have locked all the rows and then it had to scan all those rows. I would suggest, you should try to add index on creationTime field.

            The other thing could be that the caches of the slave are not primed and hence the query had to do a lot of IO. You mentioned that the query executed quickly on the master, that is because the master must have been running for a long time and so has its caches primed. but the same thing is not happening on the slave.so if the slave has to do IO, and then it has to scan the whole table that could mean reading a lot of data.

            As you are using Percona Server on slave, I would suggest, you should set the following parameters for slow log on slave.

            SET GLOBAL log_slow_verbosity=full;
            SET GLOBAL long_query_time=1;
            SET GLOBAL log_slow_slave_statements=1;

            This will log all the queries that are executed slowly by the slave and will also log additional diagnostic information which will let us see if the query was doing IO.

            You can also see if InnoDB is doing IO to fulfill queries by running the following command

            mysqladmin -r -i 10 extended-status | grep Innodb_buffer_pool_reads

            Regards,
            Niljoshi

            Comment

            Working...
            X