Hi, I work in an internet company that runs a web site with classifieds. We have got more than million page views daily and over a half a million classifieds with 2 weeks long life-cycle on an average. We use PHP and MySQL 4.0.18. We have got problems with simultaneous reads and writes to a database (DB). Now we use MyISAM tables but we are planing to change theme to InnoDB because of an InnoDB's locking on the row level feature. Unfortunately, InnoDB does not support fulltext search but we can overcome it by creating additional MyISAM tables for a fulltext purpose and migrating with rest of them to InnoDB.
Furthermore, we would like to add a separate server for read-only (RO) DB to be used only with WWW application. In this manner we would never block main DB server with connections established by clients through WWW application. Obviously we will take care that all data updating queries would be send to main DB. One of the problems is how to synchronise data from the main DB to the RO one. We could use a MySQL replication but we are afraid that we won't gain much because during synchronisation our RO DB could get blocked anyway. Now, to synchronise data without locking RO DB we do something like: CREATE TABLE table_tmp LIKE table; INSERT INTO table_tmp SELECT * FROM table; DROP TABLE table_ro; ALTER TABLE table_tmp RENAME table_ro; table - working table on main DB table_ro - table on RO DB table_tmp - temporary table Real schema is a bit more complicated because the main DB and the RO are on two different servers so instead of 'INSERT INTO SELECT FROM' we fetch data from the main DB and insert it in the RO one in bunches of 100 records (much faster than separate inserts). Additionally we add staff like LOCK TABLES, SQL_BIG_RESULT or SQL_NO_CACHE. Unfortunately, in this manner for a split moment there is no table in RO DB. We have got few questions: - What do you think about migration to InnoDB? - What do you think about RO DB idea? - Do you successfully use MySQL replication in production environment? - How big is a delay in slave update? - What do you think about "DROP TABLE/ALTER TABLE" synchronisation? - Is there a different method to synchronise data between DBs that we could use? - Could upgrade of MySQL to 4.1.7 improve DB performance? Any kind of comments or suggestions are most welcome. Sincerely, Pawel Marzec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
