1

We have an update process which currently takes over an hour and means that our DB is unusable during this period.

If I setup up replication would this solve the problem or would the replicated DB suffer from exactly the same problem that the tables would be locked during the update?

Is it possible to have the replicated DB prioritize reading over updating?

Thanks, D

DD.
  • 21,498
  • 52
  • 157
  • 246
  • Give bit more clarification, what kind of update. Can it be divided and spread over period of time. What is your storage engine? (innodb/mysql/something else) – Zimbabao Feb 17 '11 at 09:01
  • I dont want to touch the update process..its very complicated.Also I don't want to change the storage engine for the time being but we are using myISAM. – DD. Feb 17 '11 at 09:06
  • What do you mean by replication? Duplication? Or something else? – gnur Feb 17 '11 at 09:09

2 Answers2

0

I suspect that with replication you're just going to be dupolicating the issue (unless most of the time is spent in CPU and only results in a couple of records being updated).

Without knowing a lot more about the scema, distribution and size of data and the update process its impossible to say how best to resolve the problem - but you might get some mileage out of using innodb instead of C-ISAM and making sure that the update is implemented as a number of discrete steps (e.g. using stored procuedures) rather than a single DML statement.

symcbean
  • 47,736
  • 6
  • 59
  • 94
-1

MySQL gives you the ability to run queries delaye. Example: "INSERT DELAYED INTO...", this will cause the query to only be executed when MYSQL has time to take the query.

Based on your input, it sounds like you are using MyISAM tables, MyISAM only support table-wide locking. That means that a single update will lock the whole database table until the query is completed. InnoDB on the other hand uses row locking, which will not cause SELECT queries to wait(hang) for updates to complete.

So you have the best chances of a better sysadmin life if you change to InnoDB :)

When it comes to replication it is pretty normal to seperate updates and selects to two different MySQL servers, and that does tend to work very well. But if you are using MyISAM tables and does a lot of updates, the locking issue itself will still be there.

So my 2 cents: First get rid of MyISAM, then consider replication or a better scaled MySQL server if the problem still exists. (The key for good performance in MySQL is to have at least the size of all indexes across all databases as physical RAM)

Ueland
  • 31
  • 1
  • 5
  • 1
    Note:`DELAYED` only works for `INSERTS`, not for `UPDATE` or `DELETE`. & does not work with InnoDB – The Scrum Meister Feb 17 '11 at 09:31
  • Hi thanks for the reply...I was looking at running the replicated slave with low-priority-updates enabled...However, if the master is locked for a long time whilst doing an update will the slave suffer from the same effect? – DD. Feb 17 '11 at 11:27
  • Yes, since the slave will perform the exact same queries as the master, the issue will appear there too. A fix for that would be to simply stop replication while the update is running, then switch all users over to the master server, and then start replication again. So you will then simply send all client traffic against the server that does not have a update running at that given time. – Ueland Feb 17 '11 at 21:37