•  

Announcement

Announcement Module
Collapse
No announcement yet.

Slave tuning

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

  • Slave tuning

    #1
    Hi,

    I'm using a replication slave for backup purposes, application is not slave aware and can't be changed easily. The master runs about 60 queries/s, slave is about 3 queries/s.

    I will usually copy master's my.cnf and cut down a few vars, max_connections/mas_user_connections to 10, key_buffer to 32MB, sort/read/query/innodb buffers 2-3x smaller than master. I keep exactly the same innodb log file sizes )

    It will usually keep at 0s behind master but this particular server is always behind, it just can't catch up.

    It has 2 databases live and staging, 140 InnoDB tables, 2-3 MyISAM (small ones), used for sequence emulation, interprocess communication inside transactions, logs.

    I'm trying to change vars but with no results so far. Any hints at what should I try next? Master has faster disks.

    Also in general what config should I use for a slave that's used only for backup?
    Tags: None

  • #2
    What is the slave doing if you run top or vmstat?

    Check extremely close that the OS isn't swapping due to too large memory usage by the software.

    Disks could be a big problem because the slave is essentially not doing anything else except writing data to disk.

    All additional indexes can be dumped if this server is only intended for backup purpose. Only keep the primary keys that are used for foreign key constraints and since InnoDB uses them in their internal storage format it isn't an overhead anyway.
    Maintaining a lot of indexes that is never going to be used is only going to require unneccessary resources.

    As for the variables, if you never query this server you will not need any sort_buffer, key_buffer, read_buffer or query_buffer.

    But if you are using mainly InnoDB tables you should try to keep the innodb_buffer_pool_size in a relative size.


    But most of all compare the hardware in the two servers and don't expect miracles.

    Comment


    • #3
      I think increasing innodb_buffer_pool_size helped, I haven't got out of sync alerts since increasing it (it's set to notify me at 600s behind master).

      I though that this buffer is used mainly for keeping data and keys and it would make sense to keep it high for more SELECTs.

      FYI I don't disabled sort_buffer completely, some subselect based write queries are forced to use dik files in this case and take a lot longer to execute on the slave than the master. I've seen this being a problem before and at max_connections = 10 it won't use so much RAM.

      We might also manually run reports on the slave from time to time.

      Comment


      • #4
        The thing is that the slave will still check constraints like unique index or foreign keys during inserts/updates.
        And the only way it can do this is to check the indexes and these indexes are stored in the innodb_buffer.
        Writes can also be optimized a lot if the innodb_buffer_pool is big since it doesn't have to flush data so often to disk.
        And it can also use things like http://dev.mysql.com/doc/refman/5.1/en/innodb-insert-bufferi ng.html

        To speed up things.

        So with InnoDB it is always good to keep the innodb_buffer_pool_size at about 80% of the available memory.

        Comment

        Working...
        X