(12:56:21 PM) ioggstream left the room (quit: Ping timeout: 264 seconds).
(12:58:00 PM) MontgoAWAY: /nick MontgoDB
(12:58:04 PM) You are now known as MontgoDB
(12:59:56 PM) MontgoDB: nvidhive: slave doesn't need to replicate ndb_apply_statys from the master, it writes it itself to record its own position.
(1:00:25 PM) MontgoDB: nvidhive: the table needs to be altered to InnoDB or MyISAM for a non-cluster slave.
(1:06:48 PM) MontgoDB: nvidhive: http://johanandersson.blogspot.com/2009/05/ha-mysql-write-scaling-using-cluster-to.html
(1:06:54 PM) MontgoDB: good howto ^
(1:14:48 PM) MontgoDB: nvidhive: normally that table is in the cluster, becaue two sql nodes could write into the same cluster (multi-source) and that there needs to be a place for one SQL node to take over the binlogging for another... this is how they communicate their applied positions.
(1:15:53 PM) nvidhive: MontgoDB: well the table is empty on the slave
(1:16:02 PM) nvidhive: so I guess I am a little confused
(1:16:06 PM) MontgoDB: what engine?
(1:16:33 PM) nvidhive: I think it was changed wrong and it is myisam
(1:16:37 PM) nvidhive: I can verify one sec
(1:16:51 PM) MontgoDB: what version is the slave?
(1:18:12 PM) nvidhive: | mysql.ndb_apply_status | 0 Rows | InnoDB
(1:18:22 PM) nvidhive: the slave is a newer version i believe
(1:18:25 PM) nvidhive: one moment
(1:18:37 PM) MontgoDB: is it a cluster release?
(1:20:48 PM) nvidhive: yes
(1:20:59 PM) nvidhive: MontgoDB: I used the 7.1.19 cluster release
(1:21:04 PM) nvidhive: the AB rpms
(1:22:54 PM) MontgoDB: hrm, ok. are you using any replicate-do-db= options?
(1:24:32 PM) MontgoDB: The update that goes into the slave's ndb_apply_status is written into the binlog with the transaction so that it shows the epoch that corresponds with that transaction.
(1:24:55 PM) nvidhive: wait?
(1:25:00 PM) nvidhive: on the slave or master
(1:25:07 PM) nvidhive: on the slave:
(1:25:15 PM) nvidhive: replicate-ignore-db = ndbinfo
(1:25:28 PM) nvidhive: replicate-ignore-db = mysql
(1:25:33 PM) MontgoDB: yea that's bad.
(1:26:01 PM) nvidhive: its the mysql one right?
(1:26:20 PM) MontgoDB: the master writes essentially UPDATE mysql.ndb_apply_status SET epoch = <somenumber>;
(1:26:28 PM) MontgoDB: that is along side every transaction.
(1:26:33 PM) nvidhive: right
(1:26:59 PM) nvidhive: I think there was a miscommunication
(1:27:03 PM) MontgoDB: that "UPDATE" event in the binlog only occurs within the binlog.
(1:27:16 PM) MontgoDB: it doesn't actually update the ndb_apply_status of the master.
(1:27:20 PM) nvidhive: waaay back I asked if I should set mysql and ndbinfo to ignore on the disk only slave
(1:28:42 PM) MontgoDB: if I said yes, then I was wrong.
(1:29:43 PM) nvidhive: or I am remembering wrong
(1:29:48 PM) MontgoDB: iirc, normally updates to grant tables via GRANT, REVOKE , SET PASSWORD aren't written to the binlog.
(1:30:02 PM) MontgoDB: so those are skipped anyway.
(1:30:05 PM) nvidhive: this is a learning experiance for me, so no worries
(1:30:07 PM) nvidhive: yeah
(1:30:19 PM) MontgoDB: there shouldn't be any reason to skip it.
(1:30:20 PM) nvidhive: that is documented. I am familair with that
(1:30:34 PM) nvidhive: no no wait
(1:30:38 PM) nvidhive: its opposite
(1:30:41 PM) MontgoDB: the ndbinfo schema is read-only (like information_schema) so no need to skip it either.
(1:30:47 PM) nvidhive: if you do a CREATE USER
(1:30:54 PM) nvidhive: that is globally appplied
(1:30:57 PM) MontgoDB: hrm ,
(1:31:22 PM) nvidhive: but if you insert into user it gets ignored if you ignroe mysql db
(1:31:31 PM) nvidhive: so you can maintain users across DBs
(1:32:04 PM) nvidhive: I am pretty sure about this
(1:48:32 PM) MontgoDB: nvidhive: done some testing.. yes you are correct, changes on users in the master are replicated to the slave.
(1:51:01 PM) MontgoDB: nvidhive: also, after updating some ndb tables, I do get a row in ndb_apply_status on the slave.
(1:51:02 PM) MontgoDB: mysql> select * from mysql.ndb_apply_status;
(1:51:02 PM) MontgoDB: +-----------+------------------+----------+-----------+---------+
(1:51:02 PM) MontgoDB: | server_id | epoch | log_name | start_pos | end_pos |
(1:51:02 PM) MontgoDB: +-----------+------------------+----------+-----------+---------+
(1:51:02 PM) MontgoDB: | 1 | 7195311466348545 | | 0 | 0 |
(1:51:02 PM) MontgoDB: +-----------+------------------+----------+-----------+---------+
(1:53:29 PM) nvidhive: is you mysql db replicated?
(1:53:38 PM) nvidhive: MontgoDB: because I just found this http://dev.mysql.com/doc/refman/5.1/en/replication-features-mysqldb.html
(1:53:45 PM) MontgoDB: yes
(1:53:51 PM) nvidhive: so.. I think this would be veersion dependent
(1:53:53 PM) nvidhive: yeah
(1:54:19 PM) nvidhive: I think I will be organziing a time to resetup this replication ond NOT ignore "mysq"
(1:54:22 PM) nvidhive: mysql
(1:54:28 PM) nvidhive: one more question MontgoDB
(1:54:36 PM) MontgoDB: I'm using 7.1.19
(1:54:40 PM) nvidhive: ok
(1:55:09 PM) nvidhive: when we first had this there were binlogs going back to march2010
(1:55:16 PM) nvidhive: which was a huge amount in size
(1:55:47 PM) nvidhive: I had to manually remove the files and edit the binlog.index file due purge not completing
(1:55:54 PM) MontgoDB: eesh
(1:56:17 PM) nvidhive: so now the ndb_binlog_index table has millions of rows that are not being purged\
(1:56:27 PM) nvidhive: what would be the safest way to remove these rows?
(2:00:44 PM) nvidhive: | mysql.ndb_binlog_index | 201682683 Rows | MyISAM | 13211.18MB | 5231.80MB | 18442.99MB |
(2:00:55 PM) nvidhive: so it has grown to 18GB
(2:01:00 PM) nvidhive: obviously a problem
(2:01:03 PM) MontgoDB: go ahead and just DELETE FROM...
(2:01:16 PM) nvidhive: won't that lock the table?
(2:01:20 PM) nvidhive: it is myisam
(2:03:23 PM) nvidhive: also.. on the master ...
(2:03:25 PM) MontgoDB: yes
(2:03:25 PM) nvidhive: | mysql.ndb_apply_status | 0 Rows | ndbcluster
(2:03:30 PM) nvidhive: o_0
(2:03:33 PM) nvidhive: that seems wrong
(2:03:51 PM) MontgoDB: on the master it would be ndbcluster engine. and it would have 0 rows.
(2:03:55 PM) MontgoDB: since it is not a slave of anything.
(2:04:01 PM) nvidhive: oh oh oh
(2:04:04 PM) nvidhive: right right
(2:04:23 PM) nvidhive: so. wouldn't locking the binlog_index table be bad for long periods of time?
(2:04:24 PM) MontgoDB: (1:27:03 PM) MontgoDB: that "UPDATE" event in the binlog only occurs within the binlog.
(2:04:24 PM) MontgoDB: (1:27:16 PM) MontgoDB: it doesn't actually update the ndb_apply_status of the master.
(2:04:36 PM) nvidhive: I recall that now
(2:08:04 PM) maxmad left the room (quit: Ping timeout: 260 seconds).
(2:08:51 PM) MontgoDB: possibly it could lead to a state in which the master could block writing additional binlog events waiting for the lock on that table to be released, if it were not writing out binlog events, there is another buffer within the cluster that queues up these unwritten binlog that could fill... if 100 (MaxBufferedEpochs) of these epochs were buffered the SQL node would be disconnected from the cluster.
(2:09:30 PM) MontgoDB: frazer or jonas would be more authoritative on that.
(2:09:34 PM) MontgoDB: frazer: ^^ ???
(2:10:42 PM) MontgoDB: nvidhive: you could probably get by with a LIMIT clause where the lock on that table is periodically released so that the binlog can still flow.
(2:11:28 PM) frazer: nvidhive, MontgoDB : If you don't need the entries, try truncate table
(2:11:50 PM) frazer: nvidhive : Otherwise, delete with a LIMIT repeatedly like MontgoDB says
(2:11:54 PM) nvidhive: can I truncate while it is in operation?
(2:12:06 PM) nvidhive: ok well.. I am trying to think of how to limit
(2:12:14 PM) MontgoDB: frazer: would locking that table lead to MaxBufferedEpochs being overrun?
(2:12:17 PM) nvidhive: I see the index is the epoch and orig server
(2:12:26 PM) nvidhive: and orig epoch
(2:12:30 PM) frazer: MontgoDB: It could do
(2:12:39 PM) frazer: MontgoDB: So only delete it on 1 MySQLD at a time
(2:12:45 PM) frazer: MontgoDB: So only lock it on 1 MySQLD at a time
(2:13:17 PM) MontgoDB: hehe, that assumes you have the replication failover setup correctly on the slave.
(2:13:30 PM) MontgoDB: see above... no ndb_apply_status updates
(2:14:48 PM) MontgoDB: frazer: do we/should we do some sort of throtling of the DELETE when we purge master logs?
(2:15:27 PM) nvidhive: it is purging the master logs just fine from what it is aware of.. the problem is.. these are basically orphaned rows
(2:15:37 PM) nvidhive: so when I do a snapshot, it does purge the newer rows
(2:15:49 PM) nvidhive: its the old stuff stuck in there I need to get rid of
(2:15:51 PM) nvidhive: :(
(2:16:08 PM) MontgoDB: nvidhive: right, I'm talking about where we do purge and there are a large numer of logs/rows to delete
(2:16:43 PM) nvidhive: I can schedule a short period of time to truncate both nodes' tables
(2:17:10 PM) nvidhive: just not sure how it will recover and if it needs any sort of initial record
(2:17:28 PM) MontgoDB: frazer: do we make one round trip (lock/delete/unlock) of ndb_binlog_index per purged file ?
(2:18:18 PM) MontgoDB: if so there may not be a reason to add any internal DELETE... LIMIT; repeat...
(2:20:33 PM) nvidhive: I wonder how long a truncate on an 18GB table would take anyways
(2:20:35 PM) nvidhive: :|
(2:23:51 PM) nvidhive: and what are the dangers of performing the truncate on such a table? just the mount of time for deletion? or any issues with replication to the ondisk slave
(2:24:04 PM) nvidhive: *amount*
(2:27:10 PM) frazer: MontgoDB: delete from ndb_binlog_index (MyISAM) is just a DELETE statement - so 1 lock around the whole statement
(2:27:47 PM) frazer: nvidhive: Locking the table will mean that the Binlog injector thread in the MySQLD cannot record entries for logs being written - it will delay binlogging
(2:28:00 PM) frazer: nvidhive: The slave will not mind
(2:28:22 PM) frazer: nvidhive: If the Binlog Injector is delayed for too long, the data nodes may disconnect the MySQLD as MontgoDB says
(2:28:54 PM) frazer: nvidhive: A more cautious approach is to find the highest epoch which you want to delete, then write a script to delete rows with an epoch lower than this, LIMIT 1000 or so, in a looo
(2:28:57 PM) frazer: nvidhive: A more cautious approach is to find the highest epoch which you want to delete, then write a script to delete rows with an epoch lower than this, LIMIT 1000 or so, in a loop
(2:29:40 PM) frazer: nvidhive : To find the highest epoch, get your oldest Binlog file name, then find the lowest epoch in the table with that name, then find the next lower epoch in the table
(2:29:56 PM) frazer: nvidhive : Then you can delete where epoch <= x.
(2:30:11 PM) frazer: nvidhive DELETE FROM mysql.ndb_binlog_index where epoch <= <x> LIMIT 1000;
(2:30:17 PM) frazer: nvidhive : Repeat
(2:31:03 PM) nvidhive: that is the problem
(2:31:20 PM) nvidhive: so various purge operations have already occurred
(2:31:40 PM) MontgoDB: nvidhive: sure, just look at the current list of master logs.
(2:31:59 PM) MontgoDB: find the lowest epoch for that file.
(2:32:15 PM) MontgoDB: then delete anything < <x>
(2:32:21 PM) MontgoDB: not <=
(2:32:34 PM) frazer: MontgoDB : That's better :)
(2:32:39 PM) nvidhive: oh ok!
(2:32:51 PM) nvidhive: I will try that out
(2:32:52 PM) ioggstream [~rpolli@net-2-33-147-97.cust.dsl.teletu.it] entered the room.