Category Archives: Databases - Page 2

Clean up whisper database

Publicat la 17 August 2015 No comments

Ending up on this page means that you already know that whisper is not that disk space efficient.
Unless you have unlimited disk space, which you don't have, at some point you would want to clean it up.

This is how I am doing it:

1. Delete old metrics of ours that haven't been written to for over 90 days
[codesyntax lang="bash"]

# just to satisfy my curiosity: how much disk space I will gain
find /data/db/whisper/ -name "*wsp" -mtime +90 -exec echo -n -e {}"\0" \; | du -hc --files0-from=-
# delete the files!
find /data/db/whisper/ -type f -mtime +90 -name "*wsp" -exec rm '{}' \;

[/codesyntax]

2. Delete empty directories
[codesyntax lang="bash"]

# how many do we have?
find /data/db/whisper/ -type d -empty | wc -l
# remove them!
find /data/db/whisper/ -type d -empty -delete

[/codesyntax]

Topology refactoring using classic MySQL replication commands - version 2

Publicat la 17 July 2015 No comments

This one in not very different than Topology refactoring using classic MySQL replication commands - version 1, actually it's the other way around.

Context:

  • Initial state: 
    dbfoo1(M) - dbfoo2(S)
          \ dbfoo3(S)
  • Desired state: 
    dbfoo1(M) - dbfoo2(MS) - dbrfoo3(S)

    • Let's get it done:

    1. stop slave on dbfoo3
      2. MariaDB [(none)]> stop slave;
    2. check the "freezing point" on dbfoo2 and note down the output from 'show' commands
      3. MariaDB [(none)]> stop slave; show master status\Gshow slave status\Gstart slave;

*************************** 1. row ***************************
            File: bin.009642
        Position: 91547140
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: dbfoo1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.293567
          Read_Master_Log_Pos: 90061272
               Relay_Log_File: relay.028914
                Relay_Log_Pos: 90061553
        Relay_Master_Log_File: bin.293567
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 90061272
              Relay_Log_Space: 90062173
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 270
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
    3. make sure dbfoo3 has the same data as dbfoo2 and use Relay_Master_Log_File and Exec_Master_Log_Pos from show master status
      4. MariaDB [(none)]> start slave until master_log_file='bin.293567', master_log_pos=90061272;
    4. point dbfoo3 to dbfoo2 and start the slave
      5. MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='foo2', master_log_file='bin.009642', master_log_pos=91547140;
MariaDB [(none)]> start slave;

Topology refactoring using classic MySQL replication commands - version 1

Publicat la 30 June 2015 No comments

A week ago I wrote a post about promoting a mysql slave as master. Today I will write topology refactoring using classic replication commands.

Context:

  • Initial state: 
    dbfoo1(M) - dbfoo2(MS) - dbrfoo3(S)
  • Desired state: 
    dbfoo1(M) - dbfoo2(S)
          \ dbfoo3(S)

Let's get to work:

  1. stop slave on dbfoo3
    2. MariaDB [(none)]> stop slave;
  2. check the "freezing point" on dbfoo2 and note down the output from 'show' commands
    3. MariaDB [(none)]> stop slave; show master status\Gshow slave status\Gstart slave;

*************************** 1. row ***************************
            File: bin.009642
        Position: 91547140
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: dbfoo1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.293567
          Read_Master_Log_Pos: 90061272
               Relay_Log_File: relay.028914
                Relay_Log_Pos: 90061553
        Relay_Master_Log_File: bin.293567
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 90061272
              Relay_Log_Space: 90062173
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 270
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
  3. make sure dbfoo3 has the same data as dbfoo2 and use File and Position from show master status
    4. MariaDB [(none)]> start slave until master_log_file='bin.009642', master_log_pos=91547140;
  4. point dbfoo3 to dbfoo1 and start the slave
    5. MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to master_host='foo1', master_log_file='bin.293567', master_log_pos=90061272;
MariaDB [(none)]> start slave;

mysql: Promoting a slave to master

Publicat la 23 June 2015 5 comentarii

I assume you you have a high availability (master - slave topology) setup. I will be brief and technical on this one :)

Context:

  • dbfoo1 is master and dbfoo2 is slave - dbfoo1(M) - dbfoo2(S)
  • dbfoo2 has to be promoted to master - dbfoo2(M) - dbfoo1(S)
  • note that in some cases the slave requires a innodb buffer pool warm-up before being promoted to master

Let's get to work:

  • remove the VIP from dbfoo1 (/etc/network/interfaces)
  • add the VIP to dbfoo2 (/etc/network/interfaces)
  • take down the VIP on dbfoo1

[codesyntax lang="bash"]

dbfoo1# ip a d dev eth0 x.x.x.x/y

[/codesyntax]

  • dbfoo2 should not be lagging behind dbfoo1
dbfoo2> show slave status\G
Relay_Log_Space: 0
Seconds_Behind_Master: 0
  • check the "freezing point" on dbfoo1 and note down the output from 'show' commands (this is optional but good to have)
dbfoo1> flush logs;
dbfoo1> show master status\G
dbfoo1> show slave status\G
  • check the "freezing point" on dbfoo1 and note down the output from 'show' commands
dbfoo2> slave stop;
dbfoo2> flush logs;
dbfoo2> show master status\G  <--- from here use File: and Position: to attach dbfoo1 (two steps below)
dbfoo2> show slave status\G
MariaDB [(none)]> show master status\Gshow slave status\G
*************************** 1. row ***************************
            File: bin.005662
        Position: 385
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: dbfoo1
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.006034
          Read_Master_Log_Pos: 245
               Relay_Log_File: relay.017538
                Relay_Log_Pos: 523
        Relay_Master_Log_File: bin.006034
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 1128
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 310
1 row in set (0.00 sec)

MariaDB [(none)]>
  • finish the flip - promote dbfoo2 as master by adding the VIP on it

[codesyntax lang="bash"]

dbfoo2# ip a a dev eth0 x.x.x.x/y

[/codesyntax]

  • connect dbfoo2  to the new master
dbfoo1> reset slave all;
dbfoo1> change master to master_host='dbfoo2', master_user='user', master_password='password', master_log_file='bin.005662', master_log_pos=385;
dbfoo1> slave start;
  • done!

Relay log read failure: Could not parse relay log event entry

Publicat la 17 June 2015 No comments

It happened today... why? Well, I was testing parallel replication (slave_parallel_threads) and after setting the number of threads to zero and when started the slave I got this wonderful error message:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com
                  Master_User: user
                  Master_Port: 3306 
                Connect_Retry: 60
              Master_Log_File: bin.077287
          Read_Master_Log_Pos: 12307299
               Relay_Log_File: relay.167660
                Relay_Log_Pos: 52428684
        Relay_Master_Log_File: bin.076470
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1594 
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 49487635
              Relay_Log_Space: 42906299762
              Until_Condition: None 
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL 
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1594 
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1220 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

To recover from this situation, I had to instruct MySQL to clear out all the relay-bin-logs beyond the following point:

Relay_Master_Log_File: bin.076470
Exec_Master_Log_Pos: 49487635

by doing the following:

[codesyntax lang="bash"]

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='master.example.com', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='bin.076470', MASTER_LOG_POS=49487635;
START SLAVE;

[/codesyntax]

mysql: preload InnoDB buffer pool

Publicat la 16 June 2015 No comments

Why?! To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDB buffer pools.

Things to be added to /etc/mysq/my.cnf:

# Shortened warm-up times with a preloaded InnoDB buffer pool
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON

Basically when MySQL receives the TERM signal will dump the InnoDB buffer pool /data/db/mysql/ib_buffer_pool and it will load it automatically when it will start. However, the buffer pool(s) dump can be done at any time when MySQL is running by doing:

SET GLOBAL innodb_buffer_pool_dump_now=ON;

By analogy the buffer pool(s) can be loaded manually by:

SET GLOBAL innodb_buffer_pool_load_now=ON;

Additionally the progress of the buffer pool(s) dump (although very quick) and load (not so quick) can be monitored by:

SHOW STATUS LIKE 'innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

The load operation can be interrupted by:

SET innodb_buffer_pool_load_abort=ON;

How to find top10 largest mysql tables

Publicat la 11 September 2012 No comments

[codesyntax lang="sql"]

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;

[/codesyntax]

Source: http://www.mysqlperformanceblog.com/2008/02/04/finding-out-largest-tables-on-mysql-server/