Tag Info

New answers tagged

3

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


2

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


0

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


1

What you're seeing is completely unacceptable, and developers should not be expected to work around it. You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen: mysql> insert into t1 values (uuid_short()); Query OK, 1 row ...


0

There are a variety of ways you can do this. If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in ...


2

I've been looking into reclaiming disk space this morning. I'd like to add that as of version 2.6 Mongo permits use of repairDatabase on the secondaries. So the process of recovery is to simply execute db.repairDatabase() on the offending databases.


0

Here are some disadvantages For you MyISAM users out there, concurrent inserts are prohibited. Data changes cannot be read unless you use mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose. BLOB data is written "as is" rather than the SQL that created the BLOB For more information, see the MySQL Documentation


5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


1

Transaction replication would be the solution I would choose for this. More info about transaction replication can be found here: http://msdn.microsoft.com/en-us/library/ms151176.aspx To only choose the columns needed, you need to use a column filter: http://msdn.microsoft.com/en-us/library/ms151775.aspx AlwaysOn availability groups would not work ...


0

It is possible that current replication thread is taking a lot of time updating a (big) change to a (big) table causing a long lock on the table. You may want to examine what the sql thread is trying to execute using mysqlbinlog with --start-position parameter. Hope this helps.


0

Check this : Link That is Below one : Can't you use Triggers? 22.5.5: Is it possible for a trigger to update tables on a remote server? Yes. A table on a remote server could be updated using the FEDERATED storage engine. From : http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-22-5-1-5 I wouldn't recommend this because you ...


0

How I would do it, without having to reload all data: Take the slave out of production- it has drifted and has wrong data Skip replication errors until the replication is running again (but remember, with wrong data) with slave-skip-errors. Not always possible. Use pt-table-checksum to identify the master-slave differences Use pt-table-sync on the ...


2

The safest approach is to stay far, far away from trying to filter replication... and tweaking sql_log_bin on and off is a recipe for replication problems, too. In a replicated environment, the general idea is that both servers have identical data sets, and to whatever extent they don't, your setup becomes exponentially more complex. There is an easier and ...


0

You can disable binary log in a session and then create the table, use it and the drop: SET sql_log_bin = 0; CREATE TABLE t(...); ... DROP TABLE t;


0

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info ... PZ explains the scenario well in this post; ...


0

You asked the same question on Reddit. Here is a copy&paste of my answer there (without the self-promotion link): Replication is tricky. This is not enough space to tell you everything that can be problematic with it and what you should be aware of, but in summary it is: Asynchronous Single-master Statement-based No automatic consistency checking ...


3

Since replication isn't normally used on slave even though it's running That statement doesn't quite make sense. In master/master, both servers are each other's master and slave and there is no real distinction between them -- only an administrative distinction, as you seem to understand. If set up correctly, then yes, SET GLOBAL READ_ONLY = 0; on the ...


1

I still don't really know how to see what exactly the slave is spending time on, but I was able to let the slave catch up. The problem was the action of deleting old rows with DELETE FROM table WHERE timestamp < x ORDER BY y LIMIT z. In MIXED mode, that is logged as ROW, meaning z amount of rows. I now delete with: SET SESSION log_warnings=0; SET ...


1

My first thought was to restore the backup. At 1TB this becomes impractical, however. Log shipping would not allow you to write to the pre-prod instance either. I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in ...


0

The easy, albeit a bit unsecure way Stop the first secondary Delete the content of it's dbpath Restart the secondary Wait for it to catch up with the primary Repeat process with the second secondary This is a bit unsecure as it is unknown why the secondaries entered the Recovering state. The more secure, but also more intrusive way As above, but stop ...


0

You can put it anywhere that PostgreSQL can find and execute it. I put it in the data directory itself, because I consider it to be configuration in the same way the postgresql.conf and pg_hba.conf and such are, and want them to be backed up together. (That is also the directory that will be searched for the script if you don't specify a path to it. At ...


0

You're expected to ensure that /path/to/archive is a shared volume, like an NFS fileshare, that both servers can read. Or you're expected to use an appropriate command to copy the files to/from a shared storage location. I think this might need to be made more obvious in the documentation. Re your comment: Why it is necessary to create a archive ...


0

You can put it wherever you like, so long as the postgres user has permission to execute it, and permission to cd into the directory it's in. I usually put it in /usr/local/bin unless I'm working on a system that has a versioned repository of scripts, in which case it's in there - something like /opt/mycompany-scripts/postgresql/myarchivescript.sh. I ...


1

The first one (cd .) does nothing. It's a placeholder. It would be better to write true in my view, so it's more obviously intended as a no-op. Turning archive_mode on but setting a no-op archive_command is a hacky workaround for the fact that changing archive_command only requires a server reload, but changing archive_mode requires a full server restart ...


0

The solution I found for myself is: stop both master and slave remove logs replace ib* files and /var/lib/mysql/mybase/* with files from prod backup start mysql on master start mysql on slave with --skip-slave-start, then SLAVE RESET; SLAVE START; Not sure if this is correct, but it works for now.


-1

Please check select * from sys.servers; Check these column values. is_publisher is_subscriber is_distributor is_nonsql_subscriber


2

Munin's formula for "device utilization" is (milliseconds spent doing I/O)/second, which assumes you can't do any I/O in parallel, so I'm not sure that this is a meaningful metric. However, you do clearly have a genuine performance issue here since replication can't stay caught up. Is there a significant difference in the I/O subsystems between the servers? ...


3

I'm not sure if you'll be able to reliable tell if the server has ever used replication or not. For example, if someone drops replication properly, there will be no trace left behind.


0

This is covered in Frequently Asked Questions for Replication Administrators: Are tables locked during snapshot generation? The length of time that the locks are taken depends on the type of replication used: For merge publications, the Snapshot Agent does not take any locks. For transactional publications, by default the Snapshot Agent takes ...


0

Got it. See my code, there is an option when creating the publication EXEC sp_addpublication @publication = N'db_name', ... @immediate_sync = N'true' ... GO That's the equivalent of checking on the publication creation wizard the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions. That's ...


0

Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld] group header in the Slave's my.cnf as follows: [mysqld] slave-skip-errors=1452 You will have to restart mysql for this to take effect. Give it a Try !!! CAVEAT : Your data integrity will deteriorate when added ...



Top 50 recent answers are included