MySQL University session on January 29: Scalability Challenges in an InnoDB-based Replication Environment
MySQL University: Scalability Challenges in an InnoDB-based Replication Environment This Thursday (January 29th), we're continuing our series of sessions on MySQL performance measuring and improvements with David Lutz' presentation titled Scalability Challenges in an InnoDB-based Replication Environment. David works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server. David is based in California, so note that this session will take place in the morning (America) or evening (Europe), respectively. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks (see http://forge.mysql.com/wiki/MySQL_University for a better format of this list): January 29, 200916:00 UTC / 8am PDT (Pacific) / 10am CST (Central) / 11am EST (Eastern) / 16:00 GMT (London) / 17:00 CET (Berlin) / 19:00 MDT (Moscow)Scalability Challenges in an InnoDB-based Replication Environment David Lutz February 5, 200908:00 UTC / 8:00 GMT / 9:00 CET / 11:00 MDT (Moscow) / 13:30 IST (India) / 16:00 CST (Beijing) / 17:00 JST (Tokyo) / 19:00 EDT (Melbourne) MySQL Performance and Scalability Project - Issues and Opportunities Allan Packer February 12, 2008 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Using DTrace with MySQL MC Brown February 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Developing MySQL on Solaris MC Brown Trond Norbye February 26, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Backing up MySQL using file system snapshotsLenz Grimmer March 5, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow)Good Coding Style Konstantin Osipov March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown The session address (Dimdim URL) for all sessions is: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity Please bookmark this address, since it will remain valid for all future MySQL University sessions. Remember, though, that the meeting room will open only 15 minutes before the session starts. Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording (slides and voice), and more. All you need to do to attend MySQL University sessions is point your browser to the address given above. All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash file (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page: http://forge.mysql.com/wiki/MySQL_University Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Best practice: FULLTEXT search InnoDB transactions replication
Hi, We have moved from Mysql4 to MySQL5 and are currently planning our new database schema. In this new approach we would like to move to InnoDB's storage engine for transaction support and still want to use MySQL's FULLTEXT search capabillities. And to make things easy we also want to replicate all our data to a second database. Now I have two different possible approaches: 1. All tables are of type InnoDB, except one table which is of type MyIsam = the FULLTEXT searchable table. This searchable table would have a column with searchable text and a few meta data columns to identify the originating table, column and row. I could use the triggers to index the desired columns on Inserts, updates and deletes and insert the indexed data into the MyIsam search-table. Replication would be straigtforward 1-to-1 replication in this aproach. 2. Still all tables would be of type InnoDB, but instead of creating a single searchable MyIsam table I could also alter the storage engine type for the searchable tables on de replication slave to MyIsam and delegate all searches to the slave. Which even may improve performance, because the master wont be doing full text searches anymore. Replication would be a bit more tricky because of having the InnoDB tables in the master and their corresponding MyIsam tables in the slave. I'm wondering which, if any, of the above aproaches is advisable or if there are other aproaches which are even better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup + replication problem?
Hi! Guilhem has now fixed this bug to 4.0.22. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html .. From: Don MacAskill ([EMAIL PROTECTED]) Subject: InnoDB backup + replication problem? This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2004-09-29 09:58:02 PST I've got an interesting (well, I think so anyway) problem with my replication. The slave chugs along just fine, then spits out: Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN' I check the master binlog position, and discover this: /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 35294588 #040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089 exec_time=0 error_code=1223 use mysql; SET TIMESTAMP=1096449951; BEGIN; # at 35294629 #040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089 exec_time=0 error_code=0 SET TIMESTAMP=1096449944; INSERT INTO ibbackup_binlog_marker VALUES (1); # at 35294710 #040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089 exec_time=0 error_code=1223 SET TIMESTAMP=1096449951; COMMIT; I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related. This has happened a few times now, and always around the time that I finish an InnoDB backup. Anyone else seen this? Any ideas? Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB backup + replication problem?
I've got an interesting (well, I think so anyway) problem with my replication. The slave chugs along just fine, then spits out: Query caused different errors on master and slave. Error on master: 'Can't execute the query because you have a conflicting read lock' (1223), Error on slave: 'no error' (0). Default database: 'mysql'. Query: 'BEGIN' I check the master binlog position, and discover this: /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 35294588 #040929 2:25:51 server id 1 log_pos 35294588 Query thread_id=7830089 exec_time=0 error_code=1223 use mysql; SET TIMESTAMP=1096449951; BEGIN; # at 35294629 #040929 2:25:44 server id 1 log_pos 35282293 Query thread_id=7830089 exec_time=0 error_code=0 SET TIMESTAMP=1096449944; INSERT INTO ibbackup_binlog_marker VALUES (1); # at 35294710 #040929 2:25:51 server id 1 log_pos 35294710 Query thread_id=7830089 exec_time=0 error_code=1223 SET TIMESTAMP=1096449951; COMMIT; I didn't see this prior to 4.0.21 (I was on 4.0.20), but it may or may not be related. This has happened a few times now, and always around the time that I finish an InnoDB backup. Anyone else seen this? Any ideas? Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limitations of InnoDB about REPLICATION
Hello guy ! The limitations about InnoDB on replication is present in = 4.0.9 release ? * THE LOAD TABLE FROM MASTER DOES NOT WORK ON INNODB TABLES * THE TABLES ARE LOADED AUTOMATICALLY ON SET REPLICATION is true this limitations on = 4.0.9 ? sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limitations of InnoDB about REPLICATION
Dyego, - Original Message - From: Dyego Souza do Carmo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 12:13 PM Subject: Limitations of InnoDB about REPLICATION Hello guy ! The limitations about InnoDB on replication is present in = 4.0.9 release ? * THE LOAD TABLE FROM MASTER DOES NOT WORK ON INNODB TABLES true. * THE TABLES ARE LOADED AUTOMATICALLY ON SET REPLICATION Sorry, I do not understand? Do you mean that when you set up a new slave, you make a cold or a hot backup of the InnoDB tablespace and log files and .frm files, and use them to set up the new slave? That is the normal procedure to set up a new slave. The command LOAD TABLE FROM MASTER is awkward anyway if you have lots of tables. is true this limitations on = 4.0.9 ? sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento Regards, Heikki - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limitations of InnoDB about REPLICATION
- Original Message - From: Dyego Souza do Carmo [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 12:31 PM Subject: Re[2]: Limitations of InnoDB about REPLICATION Dobrý den, quinta-feira, 23 de janeiro de 2003, 08:28:43, napsal jste: HT Dyego, ... * THE LOAD TABLE FROM MASTER DOES NOT WORK ON INNODB TABLES HT true. * THE TABLES ARE LOADED AUTOMATICALLY ON SET REPLICATION HT Sorry, I do not understand? Do you mean that when you set up a new slave, HT you make a cold or a hot backup of the InnoDB tablespace and log files and HT .frm files, and use them to set up the new slave? That is the normal HT procedure to set up a new slave. The command LOAD TABLE FROM MASTER is HT awkward anyway if you have lots of tables. ... The Command LOAD TABLE FROM MASTER work on slave with InnoDB tables ? ( = 4.0.9 ) sorry, no. The workarounds are: A. 1) convert the table to MyISAM in the master, 2) use then LOAD TABLE FROM MASTER in the slave, 3) convert back to InnoDB in the master. B. Or simply dump the table in the master and import the dumpfile to the slave. Tanks ;) sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento You are welcome :), Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB and replication
Hello there, anybody here has some background using InnoDB and MySQL replication feature ? If so, would you mind sharing those information with me ? -- João Paulo Vasconcellos Gerente de Tecnologia - NetCard Tel. 3852-9008 Ramal 31 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php