Skip to content

Instantly share code, notes, and snippets.

@qix

qix/gist:035556db5ffbf9415550

Created April 29, 2015 19:45
Show Gist options
  • Save qix/035556db5ffbf9415550 to your computer and use it in GitHub Desktop.
Save qix/035556db5ffbf9415550 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
```
mysql> SHOW AGGREGATORS;
+----------------+------+--------+--------------------+------------------------------+-------------------+
| Host | Port | State | Opened_Connections | Average_Roundtrip_Latency_ms | Master_Aggregator |
+----------------+------+--------+--------------------+------------------------------+-------------------+
| 127.0.0.1 | 3306 | online | 0 | NULL | 1 |
| 10.240.76.251 | 3306 | online | 2 | 1.184 | 0 |
| 10.240.194.230 | 3306 | online | 2 | 1.175 | 0 |
+----------------+------+--------+--------------------+------------------------------+-------------------+
mysql> SHOW LEAVES;
+--------------+------+--------------------+--------------+-----------+--------+--------------------+------------------------------+
| Host | Port | Availability_Group | Pair_Host | Pair_Port | State | Opened_Connections | Average_Roundtrip_Latency_ms |
+--------------+------+--------------------+--------------+-----------+--------+--------------------+------------------------------+
| memsql-leaf1 | 3306 | 1 | memsql-leaf3 | 3306 | online | 34 | 1.279 |
| memsql-leaf2 | 3306 | 1 | memsql-leaf4 | 3306 | online | 35 | 1.163 |
| memsql-leaf3 | 3306 | 2 | memsql-leaf1 | 3306 | online | 36 | 0.828 |
| memsql-leaf4 | 3306 | 2 | memsql-leaf2 | 3306 | online | 34 | 0.770 |
| memsql-leaf5 | 3306 | 2 | memsql-leaf6 | 3306 | online | 5 | 0.893 |
| memsql-leaf6 | 3306 | 1 | memsql-leaf5 | 3306 | online | 4 | 0.702 |
+--------------+------+--------------------+--------------+-----------+--------+--------------------+------------------------------+
mysql> SHOW PARTITIONS;
+---------+--------------+------+--------+--------+
| Ordinal | Host | Port | Role | Locked |
+---------+--------------+------+--------+--------+
| 0 | memsql-leaf1 | 3306 | Master | 0 |
| 0 | memsql-leaf3 | 3306 | Slave | 0 |
| 1 | memsql-leaf2 | 3306 | Master | 0 |
| 1 | memsql-leaf4 | 3306 | Slave | 0 |
| 2 | memsql-leaf3 | 3306 | Master | 0 |
| 2 | memsql-leaf1 | 3306 | Slave | 0 |
| 3 | memsql-leaf4 | 3306 | Master | 0 |
| 3 | memsql-leaf2 | 3306 | Slave | 0 |
| 3 | memsql-leaf5 | 3306 | Slave | 0 |
| 3 | memsql-leaf6 | 3306 | Slave | 0 |
| 4 | memsql-leaf1 | 3306 | Master | 0 |
| 4 | memsql-leaf3 | 3306 | Slave | 0 |
| 5 | memsql-leaf2 | 3306 | Master | 0 |
| 5 | memsql-leaf4 | 3306 | Slave | 0 |
| 6 | memsql-leaf3 | 3306 | Master | 0 |
| 6 | memsql-leaf1 | 3306 | Slave | 0 |
| 7 | memsql-leaf4 | 3306 | Master | 0 |
| 7 | memsql-leaf2 | 3306 | Slave | 0 |
| 8 | memsql-leaf1 | 3306 | Master | 0 |
| 8 | memsql-leaf3 | 3306 | Slave | 0 |
| 9 | memsql-leaf2 | 3306 | Master | 0 |
| 9 | memsql-leaf4 | 3306 | Slave | 0 |
| 10 | memsql-leaf3 | 3306 | Master | 0 |
| 10 | memsql-leaf1 | 3306 | Slave | 0 |
| 11 | memsql-leaf4 | 3306 | Master | 0 |
| 11 | memsql-leaf2 | 3306 | Slave | 0 |
| 12 | memsql-leaf1 | 3306 | Master | 0 |
| 12 | memsql-leaf3 | 3306 | Slave | 0 |
| 13 | memsql-leaf2 | 3306 | Master | 0 |
| 13 | memsql-leaf4 | 3306 | Slave | 0 |
| 14 | memsql-leaf3 | 3306 | Master | 0 |
| 14 | memsql-leaf1 | 3306 | Slave | 0 |
| 15 | memsql-leaf4 | 3306 | Master | 0 |
| 15 | memsql-leaf2 | 3306 | Slave | 0 |
| 16 | memsql-leaf1 | 3306 | Master | 0 |
| 16 | memsql-leaf3 | 3306 | Slave | 0 |
| 17 | memsql-leaf2 | 3306 | Master | 0 |
| 17 | memsql-leaf4 | 3306 | Slave | 0 |
| 18 | memsql-leaf3 | 3306 | Master | 0 |
| 18 | memsql-leaf1 | 3306 | Slave | 0 |
| 19 | memsql-leaf4 | 3306 | Master | 0 |
| 19 | memsql-leaf2 | 3306 | Slave | 0 |
| 20 | memsql-leaf1 | 3306 | Master | 0 |
| 20 | memsql-leaf3 | 3306 | Slave | 0 |
| 21 | memsql-leaf2 | 3306 | Master | 0 |
| 21 | memsql-leaf4 | 3306 | Slave | 0 |
| 22 | memsql-leaf3 | 3306 | Master | 0 |
| 22 | memsql-leaf1 | 3306 | Slave | 0 |
| 23 | memsql-leaf4 | 3306 | Master | 0 |
| 23 | memsql-leaf2 | 3306 | Slave | 0 |
| 24 | memsql-leaf1 | 3306 | Master | 0 |
| 24 | memsql-leaf3 | 3306 | Slave | 0 |
| 25 | memsql-leaf2 | 3306 | Master | 0 |
| 25 | memsql-leaf4 | 3306 | Slave | 0 |
| 26 | memsql-leaf3 | 3306 | Master | 0 |
| 26 | memsql-leaf1 | 3306 | Slave | 0 |
| 27 | memsql-leaf4 | 3306 | Master | 0 |
| 27 | memsql-leaf2 | 3306 | Slave | 0 |
| 28 | memsql-leaf1 | 3306 | Master | 0 |
| 28 | memsql-leaf3 | 3306 | Slave | 0 |
| 29 | memsql-leaf2 | 3306 | Master | 0 |
| 29 | memsql-leaf4 | 3306 | Slave | 0 |
| 30 | memsql-leaf3 | 3306 | Master | 0 |
| 30 | memsql-leaf1 | 3306 | Slave | 0 |
| 31 | memsql-leaf4 | 3306 | Master | 0 |
| 31 | memsql-leaf2 | 3306 | Slave | 0 |
+---------+--------------+------+--------+--------+
66 rows in set (0.02 sec)
mysql> EXPLAIN REBALANCE PARTITIONS;
+-------------------+---------+--------------+-------------+-------+
| Action | Ordinal | Target_Host | Target_Port | Phase |
+-------------------+---------+--------------+-------------+-------+
| COPY PARTITION | 1 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 1 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 0 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 0 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 3 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 3 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 2 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 2 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 5 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 5 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 4 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 4 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 7 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 7 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 6 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 6 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 9 | memsql-leaf5 | 3306 | 3 |
| COPY PARTITION | 9 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 8 | memsql-leaf6 | 3306 | 3 |
| COPY PARTITION | 8 | memsql-leaf5 | 3306 | 3 |
| PROMOTE PARTITION | 0 | memsql-leaf6 | 3306 | 4 |
| PROMOTE PARTITION | 1 | memsql-leaf5 | 3306 | 4 |
| PROMOTE PARTITION | 2 | memsql-leaf6 | 3306 | 4 |
| PROMOTE PARTITION | 3 | memsql-leaf5 | 3306 | 4 |
| PROMOTE PARTITION | 4 | memsql-leaf6 | 3306 | 4 |
| PROMOTE PARTITION | 5 | memsql-leaf5 | 3306 | 4 |
| PROMOTE PARTITION | 6 | memsql-leaf6 | 3306 | 4 |
| PROMOTE PARTITION | 7 | memsql-leaf5 | 3306 | 4 |
| PROMOTE PARTITION | 8 | memsql-leaf6 | 3306 | 4 |
| PROMOTE PARTITION | 9 | memsql-leaf5 | 3306 | 4 |
| DROP PARTITION | 0 | memsql-leaf3 | 3306 | 5 |
| DROP PARTITION | 1 | memsql-leaf4 | 3306 | 5 |
| DROP PARTITION | 2 | memsql-leaf1 | 3306 | 5 |
| DROP PARTITION | 3 | memsql-leaf2 | 3306 | 5 |
| DROP PARTITION | 4 | memsql-leaf3 | 3306 | 5 |
| DROP PARTITION | 5 | memsql-leaf4 | 3306 | 5 |
| DROP PARTITION | 6 | memsql-leaf1 | 3306 | 5 |
| DROP PARTITION | 7 | memsql-leaf2 | 3306 | 5 |
| DROP PARTITION | 8 | memsql-leaf3 | 3306 | 5 |
| DROP PARTITION | 9 | memsql-leaf4 | 3306 | 5 |
+-------------------+---------+--------------+-------------+-------+
40 rows in set (0.04 sec)
mysql> REBALANCE PARTITIONS;
mysql> SHOW REBALANCE STATUS;
+-------------------+---------+--------------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| Action | Ordinal | Target_Host | Target_Port | Phase | Status | Running_Time |
+-------------------+---------+--------------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
| COPY PARTITION | 1 | memsql-leaf5 | 3306 | 3 | success | 229462 |
| COPY PARTITION | 1 | memsql-leaf6 | 3306 | 3 | success | 251414 |
| COPY PARTITION | 0 | memsql-leaf6 | 3306 | 3 | success | 256079 |
| COPY PARTITION | 0 | memsql-leaf5 | 3306 | 3 | success | 256079 |
| COPY PARTITION | 3 | memsql-leaf5 | 3306 | 3 | success | 256079 |
| COPY PARTITION | 3 | memsql-leaf6 | 3306 | 3 | success | 256077 |
| COPY PARTITION | 2 | memsql-leaf6 | 3306 | 3 | success | 256077 |
| COPY PARTITION | 2 | memsql-leaf5 | 3306 | 3 | success | 256077 |
| COPY PARTITION | 5 | memsql-leaf5 | 3306 | 3 | success | 256077 |
| COPY PARTITION | 5 | memsql-leaf6 | 3306 | 3 | success | 256076 |
| COPY PARTITION | 4 | memsql-leaf6 | 3306 | 3 | success | 256076 |
| COPY PARTITION | 4 | memsql-leaf5 | 3306 | 3 | success | 256076 |
| COPY PARTITION | 7 | memsql-leaf5 | 3306 | 3 | success | 256076 |
| COPY PARTITION | 7 | memsql-leaf6 | 3306 | 3 | success | 256076 |
| COPY PARTITION | 6 | memsql-leaf6 | 3306 | 3 | success | 260555 |
| COPY PARTITION | 6 | memsql-leaf5 | 3306 | 3 | success | 260555 |
| COPY PARTITION | 9 | memsql-leaf5 | 3306 | 3 | success | 260555 |
| COPY PARTITION | 9 | memsql-leaf6 | 3306 | 3 | success | 260554 |
| COPY PARTITION | 8 | memsql-leaf6 | 3306 | 3 | success | 260554 |
| COPY PARTITION | 8 | memsql-leaf5 | 3306 | 3 | success | 260554 |
| PROMOTE PARTITION | 0 | memsql-leaf6 | 3306 | 4 | success | 3297 |
| PROMOTE PARTITION | 1 | memsql-leaf5 | 3306 | 4 | success | 3854 |
| PROMOTE PARTITION | 2 | memsql-leaf6 | 3306 | 4 | success | 3854 |
| PROMOTE PARTITION | 3 | memsql-leaf5 | 3306 | 4 | success | 3854 |
| PROMOTE PARTITION | 4 | memsql-leaf6 | 3306 | 4 | failed (Query 'PAUSE REPLICATING `authbox_4`' failed on leaf memsql-leaf3:3306 while pausing replication (preparing to failover) with error 1733: Unable to replay files for database 'authbox_4'. Run SHOW DATABASES EXTENDED and visit http://developers.memsql.com/docs/4.0/database_states.html for more information) | 3854 |
| PROMOTE PARTITION | 5 | memsql-leaf5 | 3306 | 4 | success | 4038 |
| PROMOTE PARTITION | 6 | memsql-leaf6 | 3306 | 4 | success | 4038 |
| PROMOTE PARTITION | 7 | memsql-leaf5 | 3306 | 4 | success | 4038 |
| PROMOTE PARTITION | 8 | memsql-leaf6 | 3306 | 4 | success | 4038 |
| PROMOTE PARTITION | 9 | memsql-leaf5 | 3306 | 4 | success | 4196 |
| DROP PARTITION | 0 | memsql-leaf3 | 3306 | 5 | success | 1900 |
| DROP PARTITION | 1 | memsql-leaf4 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 2 | memsql-leaf1 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 3 | memsql-leaf2 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 4 | memsql-leaf3 | 3306 | 5 | scheduled | NULL |
| DROP PARTITION | 5 | memsql-leaf4 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 6 | memsql-leaf1 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 7 | memsql-leaf2 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 8 | memsql-leaf3 | 3306 | 5 | success | 3196 |
| DROP PARTITION | 9 | memsql-leaf4 | 3306 | 5 | success | 3196 |
+-------------------+---------+--------------+-------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+
40 rows in set (0.00 sec)
@qix
Copy link
Author

qix commented Apr 29, 2015

The new table I created seems to be gone

memsql-leaf1> SHOW TABLES IN authbox_28;
+-----------------------+
| Tables_in_authbox_28  |
+-----------------------+
| feed_action           |
| feed_action_label     |
| pivot_action_ids      |
| pivot_entity_20150409 |
| pivot_entity_20150410 |
+-----------------------+
5 rows in set (0.00 sec)

memsql-leaf1> SHOW TABLES IN authbox_30;
+-----------------------+
| Tables_in_authbox_30  |
+-----------------------+
| pivot_action_ids      |
| pivot_entity_20150409 |
| pivot_entity_20150410 |
+-----------------------+
3 rows in set (0.00 sec)

@qix
Copy link
Author

qix commented Apr 29, 2015

The table seemed to be fine from SHOW PARTITIONS / SHOW EXTENDED STATUS, but was missing the new tables.

Issue was easily fixed by manually creating the tables on 30, but with loss of data I assume.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment