Q from Sven - Are mysqlhotcopy and ibbackup only part of 6.0?
mysqlhotcopy is available in all releases. ibbackup is a separate offering that has been available for a long time
Q from Adam: Is the SQL based output portable to another database server engine e.g. MSSQL?
There are options of mysqldump that make the output as much generic as possible, and that is portable, but we can't guarantee it works with every RDBMS. The DDL is the less portable section
Q from Andrea: Is a hot mysqldump generally a good idea?
Unfortunately the answer is "it depends". With MyISAM, for example, this is not generally a good idea, since you have to lock the tables to make the dump consistent - i.e. it is not a hot dump anymore, you may call it "warm". With InnoDB the dump is consistent, provided we execute it with the Repeatable read isolation level.
Q from Simon: What is your suggestion to backup databases with tables with mixed engines?
A LVM snapshot will work. In MySQL 5.2, this will be available with the built-in backup facility. mysqldump can still do a good job, but it will lock the MyISAM tables.
Q from Francisco: Can I execute a mysqldump from a remote machine, using ftp or sftp for example?
It is possible to execute mysqldump from a remote machine, but you may experience poor performance due to the network. mysqldump will not use ftp or sftp, it will use the usual tcp connection to connect to the db server and retrieve the data.
Q from Roalt: If I apply a the lock-all-tables in the mysqldump, would that affect replication too?
If you are dumping the slave, the sql thread of replication (the thread responsible to update the slave) will stop, but in the meantime the master and the I/O thread can still work.
Q from Sven: How long will mysqlhotcopy take to backup a 50 GB database?
The time depends on several factors and it's hard to say. One thing to consider though is that mysqlhotcopy will be in any case faster than mysqldump
Q from Simon: Can I use replication and then a binary copy of the data from a slave?
You can, but you have to consider several aspects. First of all, it will always be a binary copy of that server (slave) and it may not work on the master. MyISAM tables can be recovered on the master as well, but InnoDB data files will be different.
Q from Michael: Are indexes re-generated when we apply a restore from a logical backup?
Yes, the whole table, with data and indexes, will be recovered from scratch.
Q from Martjin: Can you restore a db to a new copy, still keeping the old one (with mysqlhotcopy)?
Yes, this is definitely possible. Simply use different names for the db schema
Q from Kristiaan: Is there any way to automate the backups to run on a regular basis?
Yes, You can use any scheduling system, crontab etc, depending of your operating system.
Q from Sven-Ake: What tool do you recommend to backup a MyISAM database that is about 50 GB? We want to do a hot backup, or at least a warm backup.
A LVM snapshot would be my first choice. I would also consider replication or the use DRBD, if you need HA as well. If you use InnoDB, hotbackup may be a solution.
Q from Roalt: Is DRBD stable enough for production of large databases?
I would say that DRBD is stable and definitely good for production. The size of the DB is not important, it is more important to consider the throughput required by the write operations.
Q from Ludovico: Will the new backup API allow integration with other backup solutions? (Legato, Omniback, Data Protector, Veritas etc.)
Yes, but you should not expect this integration available when we will release the API
Q from Kelly: Can binary logs be created for a specific database or will binaries always be for all databases within your instance?
The logs are created per server, therefore there is no way to distinguish logs for different schemas
Q from Kristiaan: If we want to transfer a copy of a MySQL database each night from the production system to the database, is there a way to get it to merged?
I would not call it a typical backup procedure. Anyway, it depends on what you mean with "merge", how you want to handle conflicts and so on.
Q from Phil: Does mysql have anything that combines the core DB backup with ftp mechanism?
Not today, but mysqldump can be run remotely.
Q from Jon: In a mixed engine schema, if LVM snapshots are not an option, what would you recommend? mysqldump is quite slow for us in practice.
Backup on a slave server with replication, for example. If you use InnoDB , Innobackup is also an option.
Q from Roalt: Are there any scripts to provide incremental backups from a replicating slave database server?
Incremental backups rely on the use of binary logs or, in case of use within the server, using relay logs. The procedure is fairly simple and it can be found in the Ref Manual.
Q from Nadav: Is there a flag when using mysqldump not to insert the schema name into the dump file?
Yes, you may use the --no-create-info and --no-create-db params
Q from Krzysztof: What about backup/restore function available in MySQL Administrator?
MySQL Administrator executes a mysqldump, but it handles it through a GUI and keeps track of the backups
Q from Adam: We have a 200GB customer file, what is the best option to store?
for a db of this size, I would definitely recommend a physical/raw backup. Logical backups may be too slow.
Q from Phil: How much additional performance load does running replication create?
Very little, since it's asynchronous and transactions are acknowledge immediately on the master server. The main impact on performance is related to the use of the binlog, which is actually another write on disk.
Q from Michael: Are you aware of any problems using mysqldump with non-English characters. Do I need to have a supported locale installed on my linux to have these statement "write" correctly?
There should not be any problem, if you have encountered them, I would suggest to check for bugs
Q from Roalt: Are LVM snapshots using LVM2 on Linux any good with MyISAM?
Yes, there should not be any problem, assuming you do a FLUSH TABLES first
Q from Alessandro: How are managed calls to functions like "NOW()" in Replication?
They are converted to the current time in the binary log
Q from Ewerton: Is there any tool to verify physical or logical corruptions in tables and indexes?
Unfortunately no, not at the moment.
Q from Jonathan: NAS backup: what's the recommended method for ensuring that NAS snapshots are restorable with minimal work after the restore?
There are different methods and they all depend on your environment. The main difference is related to the use of InnoDB or MyISAM. With MyISAM, things are pretty simple whilst with InnoDB it's a little bit trickier. If you can afford to make the database read only for few seconds, that is the best and safest way to apply the snapshot.
Q from Nadav: is ZMANDA for Unix only, and if yes, is it GUI only or command line also?
ZManda Recovery Manager (ZRM) runs on Linux and it can execute backups on Linux, Solari, OS X and Windows. ZRM can be executed from command line.
Q from Cristian: If I backup a slave server, can I restore the backup on the master?
If you have not applied any change to the data model, you can restore your logical backup on the master as well on the slave. If you have a physical backup, you master and server must be configured in the same way (DB version, location, parameters etc.).
Q from Arthur: Could you explain the --tab param in mysqldump?
With --tab you define that the output will be a tab delimited set of files with the same name of the tables and sql files with the same name to create the tables. The parameter is used to pass the path where these files must be created.
Q from Jonathan: How does the NAS-based backup ensure DB consistency?
It doesn't, that is up to the database. You may ensure consistency with MyISAM flushing the tables and switching to read-only mode. For InnoDB, this approach will leave the database in a recoverable state that must be applied when the backup will be restored.
Q from Arthur: Can you point my to some more text on using the NetApp with mysql
You may have a look at this document: http://www.netapp.com/library/tr/3601.pdf
Sunday 7 October 2007
MySQL Backup and Recovery - Today and Tomorrow
Thanks to all of you who have attended! (and apologise for having posted this info after such a long time)
We have had lots of questions and very interesting feedback and suggestions.
You can find the slides and the Webex presentation here.
I would like to post more on this subject, specifically on snapshot backup, comparing the different options with NetApp, DRBD and LVM.
We have had lots of questions and very interesting feedback and suggestions.
You can find the slides and the Webex presentation here.
I would like to post more on this subject, specifically on snapshot backup, comparing the different options with NetApp, DRBD and LVM.
Wednesday 1 August 2007
Some questions and answers from the sixth session
Q from Adrian: What is BI reporting? Is it reporting?
It stands for Business Inteligence which is a bit more than just reporting - also analysing the data
Q from Assaf: What's the max realistic number of nodes in my MySql cluster?
We mostly see up to 8 data nodes (remember that you add latency with more nodes). Regarding the SQL nodes we also see customers with more than 30 nodes
Q from Assaf: What is the difference between "data" and "SQL" nodes?
MySQL Server is the SQL node, the data nodes are the "HA-Storage Engine"
Q from Adrian: How can I config HA to execute a script during a failover? Is this is possible with V2? I want to check some constraints before the slave server takes over...
Yes, you can implement your own scripts which will be executed on failover
Q from Dave: Can you have multiple MySQL servers connected to a single HA storage engine - e.g. a SAN - if so, what best practices should be observed, e.g. read-only connections for all but one of the MySQL servers
A typical solution is a active/passive one - so you can only have one MySQL Server connected - We can use external locking for MyISAM to get around this but only for this Storage engine
Q: What about cluster on virtual nodes with LVS?
We do not recommend the use of MySQL Cluster on virtual servers. MySQL Cluster is a real-time system and it's optimised to get advantage from some specific hardware and firmware features. In a virtual environment you may have unexpected behaviours, hard to catch for our tech support team.
Q from Eric: All improvements for HA goes trough replication, which seems right. But what about performance: Is it only about to choose the right storage engine?
It is about choosing the right HA Solution for your requirements (asynchronous or synchronous replication, replication over a long distance, ...) and of course using the right storage engine and having the right configurations set
It stands for Business Inteligence which is a bit more than just reporting - also analysing the data
Q from Assaf: What's the max realistic number of nodes in my MySql cluster?
We mostly see up to 8 data nodes (remember that you add latency with more nodes). Regarding the SQL nodes we also see customers with more than 30 nodes
Q from Assaf: What is the difference between "data" and "SQL" nodes?
MySQL Server is the SQL node, the data nodes are the "HA-Storage Engine"
Q from Adrian: How can I config HA to execute a script during a failover? Is this is possible with V2? I want to check some constraints before the slave server takes over...
Yes, you can implement your own scripts which will be executed on failover
Q from Dave: Can you have multiple MySQL servers connected to a single HA storage engine - e.g. a SAN - if so, what best practices should be observed, e.g. read-only connections for all but one of the MySQL servers
A typical solution is a active/passive one - so you can only have one MySQL Server connected - We can use external locking for MyISAM to get around this but only for this Storage engine
Q: What about cluster on virtual nodes with LVS?
We do not recommend the use of MySQL Cluster on virtual servers. MySQL Cluster is a real-time system and it's optimised to get advantage from some specific hardware and firmware features. In a virtual environment you may have unexpected behaviours, hard to catch for our tech support team.
Q from Eric: All improvements for HA goes trough replication, which seems right. But what about performance: Is it only about to choose the right storage engine?
It is about choosing the right HA Solution for your requirements (asynchronous or synchronous replication, replication over a long distance, ...) and of course using the right storage engine and having the right configurations set
Friday 22 June 2007
Links to material and documentation for the Online Solutions Webinars
You can find the material following these links:
- Part 1 - High Availability and Scalability Architectures: Registration to the Webex.
- Part 2 - Advanced Scalability Solutions: Registration to the Webex and slides in PDF format.
- Part 3 - MySQL Enterprise: Registration to the Webex and slides in PDF format.
- Part 4 - 99.999% High Availability with MySQL Cluster: Registration to the Webex and slides in PDF format.
- Part 5 - Performance and Benchmarks: Registration to the Webex and slides in PDF format.
- Part 6 - Advanced HA Solutions: Registration to the Webex and slides in PDF format.
Questions and Answers during the fifth session
Q from Efrat: Is there a flag I can use to tell the replication server to ignore duplicate entries? Currently it stops replicating each time it get duplicate entry
You can configure replication in order to ignore errors (all or some specific errors, such as a duplicate). In any case, the reason is that when you have a duplicate, this means there is some conflict, and the slave doesn't know which is right. There is coming conflict detection and resolution in some future version.
Q from Efrat: From where can I download a free trial version of MySql Enterprise server?
You can ontact mysql sales for a 30-day mysql enterprise trial, which includes MySQL Network Monitoring and Advisory Service
Q from Danilo: What is index merge?
It's when the optimizer merges several rows using one same index. See section 7.2.6 in the MySQL 5.0 manual.
Q from Colin: How do you monitor connections in realtime and is it possible to kill off connections that are long term impacting?
You can view that in the Connections values in the "SHOW STATUS" command. The KILL command can be used to kill off connections or statements that are taking too many resources.
Q from Owen: Query cache maybe helpful, but our application retrieves results related to time i.e now -1 hour so each retrieval is different. So how can I optimize this type of access?
This basically means that Query cache doesn't help you that much, but there are other means of optimizing access, query-cache is just one such tool. What applies are for example appropriate indexing and data caching.
Q from nir: how is the query cache flushed? I mean, when data changes, is the query cache flushed?
It is automatically flushed when a DML operation affects the cached data in the query cache
Q from Joergen: Is MySQL supported on a QNX v.6.3.0 opeating system ? And where can I download a version?
We are working on an answer to this.
Q from John: InnoDB has been bought by Oracle. Is it still going to be a part of version 6.0?
Yes. InnoDB is licensed under GPL and it will be always available, no matter who will own the code
Q from Owen: If I have an application using MyISAM with ca 300 rps and max 5 users, with queries that can involve anywhere between 2,000 and 12,000,000 rows with multi joins - how can I optimise the response?
It's a bit too difficult to answer to this specific question here. Proper indexing, caching, possible use of temp tables, load balancing are some suggestions here though, as well as optimizer hints, that help in some cases.
Q from Danilo: I'm in a multimaster scenario. So i can't use autoincrement fields to order data sequentially. Is It a good idea use unix timestamp into an integer field to order data?
Auto increment can be used. Have a look at the auto_increment_increment and auto_increment_offset parameters on how to deal with this situation.
Q from Adrian: Would you suggest to change from innodb to Falcon?
It's difficult to say. In general, Falcon should do at least well as InnoDB in most cases. For testing purposes, you can down load Falcon now. For production, I would wait for the GA version.
Q from Eric: Should the hdd disk cache be as large as possible?
In most cases this is a good idea. But when choosing between a database and a hdd cache, choose the former, mostly. The higher up in the chain, the smarter the use of the cache can be.
Q from Colin: Which engine is best for removing old data but not impacting perofmance of db?
With 5.0, have a look at MERGE tables (which is based on MyISAM). As of 5.1, partitioning can help with this.
Q from Richard: Can the file system effect the performance of mysql?
Absolutely, very much so. In particular when it comes to write intensive applications or when not a lot of data is cached by MySQL.
Q from Alfonso: Could you give me 3 good reasons by which a user might want to use MySql compared to other RDBMSs?
It's ease of use, low cost and open source
Q from Eric: Which engine would you advise for stock quotes? Meaning regular inserts (few for EOD pricing, multiple for Realtime), no update, and very simple select. Obviously not innodb (no need for transaction)
Look at a replictaed scenario. Master may well use Innodb (as this is crash resistant) and replicate to a read server using, say, HEAP (memory) tables. When you start the slave, you read all data from the master to point X. And then replication from that point.
Q from Richard: which file system would you recommend for MySQL?
This is a "religious" question. Not ext3, usually. XFS has showed good results, or maybe JFS.
Q from Colin: Can replication work with a shared db source?
Yes, if I understand your question
Q from Adrián and Kevin: You recommend not use DRBD in large databases and data warehouse. Is a 130 GB database is a larger databases?
"Large" depends on your environment, your budget and your infrastructure. Generally speaking, I would say that 130 GB is not a large database. 1TB is becoming an interestingly "large" database - but in case of DRBD, it mainly depends on the amount of data you need to write to update the DB.
Q from Eduardo: is shared storage supported by MySQL?
In terms of Active/Active servers, yes, by using external locking, but I would rarely recommend it, since it's MyISAM only and locking maybe have a negative impact on performance.
Q from Richard: Using a shared storage source would you connect via iSCSI, NFS or anything else? Which is recommended?
As this depends on locking, using NFS seems like a bad idea, as lockd is not that reliable. iSCSI should work, but remember to use a filesystem that supports this setup.
Q form Colin: Can I use ZFS on Solaris?
Yes, absolutely. I am not aware of any performance test so far though.
Q from Eric: Is it better to use a separate storage solution from the mysql db engine/server ? Or (when possible) should I keep both server and storage on the same machine? And what about replicated solutions should I still use multiple couple of engine-storage devices?
It's better to keep them on the same machine, for performance reasons in most cases. Using NFS sharing for the database is not a good idea. A SAN gives great throughput but so-so latency. Replicas typically use internal disks, but again, this depends. If a SAN is used, it is primarily used on the Master for disk management, backup etc.
Q from Paul: Can you do cross site clustering with MySQL clustering?
Yes, by using MySQL Replication between the Cluster. If you use SQL access, this work today. If you use the NDB API, this will require 5.1.
Q from Enza: Is it good idea store image blob in tables or is it better to use the filesystem?
If you always read the BLOB in full, any means works fine. As MySQL lacks a BLOB locator today, to read parts of a BLOB, you have to use the filesystem. This will change, in particular with Falcon. Also, in some cases, managing BLOBs on disk is sometimes easier, and BLOB data is mixed with other data when stored in the DB. Again, Falcon will be different here.
Q from Colin: Can the incoming connections be managed so that long process time query has less priority than short time query?
No, you can't. I assume this could somehow be managed in a load balancer, to an extent, but MySQL does not have this ability. And this is also a dangerous practice. Let's say that the query being throttled "down" has locked data that is required by high-prio queries. In general, this is much easier to manage if all connections have the same priority.
You can configure replication in order to ignore errors (all or some specific errors, such as a duplicate). In any case, the reason is that when you have a duplicate, this means there is some conflict, and the slave doesn't know which is right. There is coming conflict detection and resolution in some future version.
Q from Efrat: From where can I download a free trial version of MySql Enterprise server?
You can ontact mysql sales for a 30-day mysql enterprise trial, which includes MySQL Network Monitoring and Advisory Service
Q from Danilo: What is index merge?
It's when the optimizer merges several rows using one same index. See section 7.2.6 in the MySQL 5.0 manual.
Q from Colin: How do you monitor connections in realtime and is it possible to kill off connections that are long term impacting?
You can view that in the Connections values in the "SHOW STATUS" command. The KILL command can be used to kill off connections or statements that are taking too many resources.
Q from Owen: Query cache maybe helpful, but our application retrieves results related to time i.e now -1 hour so each retrieval is different. So how can I optimize this type of access?
This basically means that Query cache doesn't help you that much, but there are other means of optimizing access, query-cache is just one such tool. What applies are for example appropriate indexing and data caching.
Q from nir: how is the query cache flushed? I mean, when data changes, is the query cache flushed?
It is automatically flushed when a DML operation affects the cached data in the query cache
Q from Joergen: Is MySQL supported on a QNX v.6.3.0 opeating system ? And where can I download a version?
We are working on an answer to this.
Q from John: InnoDB has been bought by Oracle. Is it still going to be a part of version 6.0?
Yes. InnoDB is licensed under GPL and it will be always available, no matter who will own the code
Q from Owen: If I have an application using MyISAM with ca 300 rps and max 5 users, with queries that can involve anywhere between 2,000 and 12,000,000 rows with multi joins - how can I optimise the response?
It's a bit too difficult to answer to this specific question here. Proper indexing, caching, possible use of temp tables, load balancing are some suggestions here though, as well as optimizer hints, that help in some cases.
Q from Danilo: I'm in a multimaster scenario. So i can't use autoincrement fields to order data sequentially. Is It a good idea use unix timestamp into an integer field to order data?
Auto increment can be used. Have a look at the auto_increment_increment and auto_increment_offset parameters on how to deal with this situation.
Q from Adrian: Would you suggest to change from innodb to Falcon?
It's difficult to say. In general, Falcon should do at least well as InnoDB in most cases. For testing purposes, you can down load Falcon now. For production, I would wait for the GA version.
Q from Eric: Should the hdd disk cache be as large as possible?
In most cases this is a good idea. But when choosing between a database and a hdd cache, choose the former, mostly. The higher up in the chain, the smarter the use of the cache can be.
Q from Colin: Which engine is best for removing old data but not impacting perofmance of db?
With 5.0, have a look at MERGE tables (which is based on MyISAM). As of 5.1, partitioning can help with this.
Q from Richard: Can the file system effect the performance of mysql?
Absolutely, very much so. In particular when it comes to write intensive applications or when not a lot of data is cached by MySQL.
Q from Alfonso: Could you give me 3 good reasons by which a user might want to use MySql compared to other RDBMSs?
It's ease of use, low cost and open source
Q from Eric: Which engine would you advise for stock quotes? Meaning regular inserts (few for EOD pricing, multiple for Realtime), no update, and very simple select. Obviously not innodb (no need for transaction)
Look at a replictaed scenario. Master may well use Innodb (as this is crash resistant) and replicate to a read server using, say, HEAP (memory) tables. When you start the slave, you read all data from the master to point X. And then replication from that point.
Q from Richard: which file system would you recommend for MySQL?
This is a "religious" question. Not ext3, usually. XFS has showed good results, or maybe JFS.
Q from Colin: Can replication work with a shared db source?
Yes, if I understand your question
Q from Adrián and Kevin: You recommend not use DRBD in large databases and data warehouse. Is a 130 GB database is a larger databases?
"Large" depends on your environment, your budget and your infrastructure. Generally speaking, I would say that 130 GB is not a large database. 1TB is becoming an interestingly "large" database - but in case of DRBD, it mainly depends on the amount of data you need to write to update the DB.
Q from Eduardo: is shared storage supported by MySQL?
In terms of Active/Active servers, yes, by using external locking, but I would rarely recommend it, since it's MyISAM only and locking maybe have a negative impact on performance.
Q from Richard: Using a shared storage source would you connect via iSCSI, NFS or anything else? Which is recommended?
As this depends on locking, using NFS seems like a bad idea, as lockd is not that reliable. iSCSI should work, but remember to use a filesystem that supports this setup.
Q form Colin: Can I use ZFS on Solaris?
Yes, absolutely. I am not aware of any performance test so far though.
Q from Eric: Is it better to use a separate storage solution from the mysql db engine/server ? Or (when possible) should I keep both server and storage on the same machine? And what about replicated solutions should I still use multiple couple of engine-storage devices?
It's better to keep them on the same machine, for performance reasons in most cases. Using NFS sharing for the database is not a good idea. A SAN gives great throughput but so-so latency. Replicas typically use internal disks, but again, this depends. If a SAN is used, it is primarily used on the Master for disk management, backup etc.
Q from Paul: Can you do cross site clustering with MySQL clustering?
Yes, by using MySQL Replication between the Cluster. If you use SQL access, this work today. If you use the NDB API, this will require 5.1.
Q from Enza: Is it good idea store image blob in tables or is it better to use the filesystem?
If you always read the BLOB in full, any means works fine. As MySQL lacks a BLOB locator today, to read parts of a BLOB, you have to use the filesystem. This will change, in particular with Falcon. Also, in some cases, managing BLOBs on disk is sometimes easier, and BLOB data is mixed with other data when stored in the DB. Again, Falcon will be different here.
Q from Colin: Can the incoming connections be managed so that long process time query has less priority than short time query?
No, you can't. I assume this could somehow be managed in a load balancer, to an extent, but MySQL does not have this ability. And this is also a dangerous practice. Let's say that the query being throttled "down" has locked data that is required by high-prio queries. In general, this is much easier to manage if all connections have the same priority.
Thursday 31 May 2007
On MySQL Cluster Replication
Erik Hoekstra, from Daisycon, has pointed out this problem related to Replication in general and with a specific example on MySQL Cluster and Replication in 5.1.
I have submitted a bug - #28270 - and our tech support is working on it.
In the meantime, Erik has found a workaround to the problem, so he can implement the script. The workaround is based on the PREPARE/EXECUTE statement:
CREATE PROCEDURE check_master()
BEGIN
DECLARE master_dead BOOLEAN DEFAULT false;
DECLARE pointer CURSOR FOR
SELECT `ip` FROM `replication`.`master_alive`;
DECLARE CONTINUE HANDLER FOR
SQLSTATE 'HY000' SET master_dead = true;
OPEN pointer;
IF( master_dead ) THEN
STOP SLAVE;
SELECT @latest:=MAX(`epoch`)
FROM `mysql`.`ndb_apply_status`;
SELECT @file:=SUBSTRING_INDEX(File, '/', -1), @pos:=Position
FROM `replication`.`new_master_binlog_index`
WHERE `epoch` > @latest
ORDER BY `epoch` ASC LIMIT 1;
SET @new_master_command = CONCAT( 'CHANGE MASTER TO
MASTER_HOST=\'192.168.0.1\',
MASTER_PORT=3306,
MASTER_USER=\'replication_user\',
MASTER_PASSWORD=\'mypasswd\', MASTER_LOG_FILE=\'',@file,'\',
MASTER_LOG_POS=',@pos,';' );
PREPARE nmc FROM @new_master_command;
EXECUTE nmc;
DEALLOCATE PREPARE nmc;
START SLAVE;
ALTER EVENT `check_master_alive` DISABLE;
END IF;
END;
In this way, the server accepts the CHANGE MASTER command.
Thanks to Erik for this great workaround, in the meantime we will check that the bug will be fixed.
In the manual for 5.1 there's an entry about scripting the failover for MySQL Cluster Replication.
In this part of the manual they speak about fetching the needed variables, like the filename and position, and place them into the CHANGE MASTER TO statement.
The example is here:
CHANGE MASTER TO
MASTER_LOG_FILE='@file',
MASTER_LOG_POS=@pos;
I'm now trying to do the following:
On a slave I've created a Federated tables, 1 pointing to the current master, and 1
to the stand-in master, should the current master fail.
Federated table 1, let's say F1, is just a simple table on the current master with only one record.
Federated table 2, let's say F2, is the ndb_binlog_index table in the mysql database on the stand-in master.
I wrote a procedure which checks if F1 exists and if not (current master down) it selects the needed vars from F2. I would like to place these variables into a CHANGE MASTER TO statement so the slave can start and pickup at the right epoch, but it seems that the CHANGE MASTER TO statement doesn't accept variables.
DELIMITER //
CREATE PROCEDURE check_master()
BEGIN
DECLARE master_dead BOOLEAN DEFAULT false;
DECLARE latest BIGINT(20) DEFAULT 0;
DECLARE fil VARCHAR(255);
DECLARE pos BIGINT(20) DEFAULT 0;
DECLARE pointer CURSOR FOR
SELECT `id` FROM `replication`.`master_alive`;
DECLARE CONTINUE HANDLER FOR
SQLSTATE 'HY000' SET master_dead = true;
OPEN pointer;
IF( master_dead ) THEN
STOP SLAVE;
SELECT @latest:=MAX(`epoch`)
FROM `mysql`.`ndb_apply_status`;
SELECT @file:=SUBSTRING_INDEX(File, '/', -1), @pos:=Position
FROM `replication`.`new_master_binlog_index`
WHERE `epoch` > @latest
ORDER BY `epoch` ASC LIMIT 1;
CHANGE MASTER TO
MASTER_HOST='192.168.0.1',
MASTER_PORT=3306,
MASTER_USER='replica',
MASTER_PASSWORD='mypasswd',
MASTER_LOG_FILE='@file',
MASTER_LOG_POS=@pos;
START SLAVE;
ALTER EVENT `check_master_alive` DISABLE;
END IF;
CLOSE pointer;
END;
//
DELIMITER ;
CREATE EVENT check_master_alive
ON SCHEDULE EVERY 5 SECOND
ENABLE DO CALL check_master();
This is the error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHANGE MASTER TO MASTER_HOST='10.0.33.104', MASTER_PORT=3306, MASTER_USER='repli' at line 15
I also tried the procedure with SELECT .. INTO but it doesn't seem to work at all.
I have submitted a bug - #28270 - and our tech support is working on it.
In the meantime, Erik has found a workaround to the problem, so he can implement the script. The workaround is based on the PREPARE/EXECUTE statement:
CREATE PROCEDURE check_master()
BEGIN
DECLARE master_dead BOOLEAN DEFAULT false;
DECLARE pointer CURSOR FOR
SELECT `ip` FROM `replication`.`master_alive`;
DECLARE CONTINUE HANDLER FOR
SQLSTATE 'HY000' SET master_dead = true;
OPEN pointer;
IF( master_dead ) THEN
STOP SLAVE;
SELECT @latest:=MAX(`epoch`)
FROM `mysql`.`ndb_apply_status`;
SELECT @file:=SUBSTRING_INDEX(File, '/', -1), @pos:=Position
FROM `replication`.`new_master_binlog_index`
WHERE `epoch` > @latest
ORDER BY `epoch` ASC LIMIT 1;
SET @new_master_command = CONCAT( 'CHANGE MASTER TO
MASTER_HOST=\'192.168.0.1\',
MASTER_PORT=3306,
MASTER_USER=\'replication_user\',
MASTER_PASSWORD=\'mypasswd\', MASTER_LOG_FILE=\'',@file,'\',
MASTER_LOG_POS=',@pos,';' );
PREPARE nmc FROM @new_master_command;
EXECUTE nmc;
DEALLOCATE PREPARE nmc;
START SLAVE;
ALTER EVENT `check_master_alive` DISABLE;
END IF;
END;
In this way, the server accepts the CHANGE MASTER command.
Thanks to Erik for this great workaround, in the meantime we will check that the bug will be fixed.
Tuesday 29 May 2007
Q&A Webinar Part 4 - MySQL Cluster
Q from Olivier - Are the data nodes MySQL servers too ?
No, the data node is handled by a separate process, ndbd, that only manages data.
Q from Olivier: So, what is MySQL Cluster? A MySQL AB product ?
Technically speaking, MySQL Cluster is a storage engine, based on a network distributed database. From a commercial point of view, we refer at MySQL Cluster as a product. We also provide APIs to access to the Cluster database directly, bypassing the MySQL Server and the storage engine architecture.
Q from Ludovico: Are there load balancing mechanisms to balance sql requests between active sql server nodes?
Yes, absolutely. The SQL nodes all see the same data, and load may well be balanced between them, if necessary.
The only caveat is that transactions must be locked into the same SQL server for the duration of the transaction.
In real life, load balancing can be achieved through hardware and software load balancers, or using the capabilities provided by some connectors such as JDBC, or just handling the load balancing at http level to the web servers, assuming that on average they will generate pretty much the same workload.
Q from Ludovico: What about embedded solutions, HW load balancers or round robin dns?
Any of them should work. Keep in mind the transaction caveat though. The load balancer must be activated when the client must initiate the connection, then the transaction must be related to the same connection.
Q from Roberto: Using the API and the native access, do we lose whole or part of the HA features ?
No, not at all, Cluster, even without the SQL nodes, is fully reduntant. The HA features are at the Cluster level, below the SQL nodes.
Q from Vasilij: Does Linux support MySQL Cluster?
Absolutely, various Linux and Unices are fully supported
Q from Manuel: Is the management server is a Single Point of Failure?
No, it's not. First of all, you may have as many as you want. Secondly, it is important that the management server is on when a new node is added to the cluster or in case of failure in a small cluster with only two data nodes. During normal operations, it could be switched off.
Typically, the management node is a lightweight process and it can be colocated with other servers.
Q from Manuel: You said that the minimum number of nodes is 3. Can't we have only two nodes and the management server running on both nodes and so you have absolute redundancy with only 2 boxes?
No, having 2 nodes is not a good idea, as that might cause a split-brain situation. If you implement such configuration, MySQL Cluster will issue a warning
Q from Olivier: Where are the data stored? On MySQL servers? On the NDBD nodes ? In memory or on disk ?
Data is In the data nodes (NDB nodes) and data is in memory. With version 5.1, data can be optionally on disk, although indexes must still reside in memory.
Q from Manuel: May the management servers run on the same machine where the nodes or the SQL Servers run?
Yes, they can. There is no need for an extra physical server, provided the cluster has at least 3 physical servers.
Q from Colin: What is the simplest cluster config if all access is via C++ API?
3 nodes. 2 datanodes, one for mgm, which may be colocated with the application node, for example.
Q from John: All the examples of hostnames in the cinfig file are ipv4 addresses - does cluster support ipv6?
No, not at the moment.
Q from John: If mysql nodes and data nodes are on the same boxes can they share IP's?
Sure, they can share the same address, as they respond to different ports
Q from Manuel: What is a split-brain and how does it happen?
A split brain is a particular situation that may happen in some HA infrastructures. It may happen that in an HA solution with only two servers, in case of network fault between the servers, the two nodes cannot talk each other, but they are both active and can provide the service to their clients. In this case we have a split brain situation. MySQL cluster does not allow a split brain, since it requires a third node as arbitrator.
Q from Danilo: Is is possible to have multiple INSERT coming from different MySQL Server that require auto_increment fields?
Yes, that is absolutely possible and fully supported.
Q from Olivier: Is all the datas on every node? What happens, for example, if I have 4 nodes with 20GB of memory for each server?
First of all, you have to specify the exact number of replicas that you want. You can have only one copy of data (NoOfReplica=1), but in this scenario the system will not be redundant. You can have up to 4 replicas. WIth a classic replica of 2, you will have only two copies of the same data. In the example that you have mentioned, the total memory available will be 4 (nodes) x 20 (memory) / 2 (replica) = 40 GB in total.
Q Danilo: How many data nodes are supported?
The current limit is 48.
Q from Domenico: Can MySql Server and Data Node reside on the same physical server?
Yes, they can. For better scalability and performance, you should consider separate nodes though.
Q from Colin: If the data is not on disk which safety/backup policy is advised in case of total system failure?
There is a checkpointing and redo logging system that assure that the data are safely stored on disk. You can also used the online backup provided for disaster recovery.
Q from Ludovico: What happens if a power shortage happens and all my data nodes are turned off?
In this case you have to recover from disk up to the latest checkpoint and then you can apply everything up to the latest position in the redo log. Bear in mind you should configure the whole infrastructure in a way that is suitable for high availability.
Q from Hilmar: So in the worst case scenario, when a checkpoint was not fired after an updated, you loose power, then cluster recovers from loss of power and after the restart of the servers it has ' rolledback' to its previous checkpoint, it that correct?
First it recovers up to the latest checkpoint, then it applies the redo log.
Q from David: Does the Transaction Coordinator ensure referential integrity?
No, it does not. Foreign keys are not supported in the current release and not in 5.1
Q from Guy: Is the whole database held in memory or just the tables in use?
The whole database is in-memory. That is the attribute of an In-Memory database, to achieve deterministic performance.
Q from Ludovico: Il the FK support at SQL level coming also for NDB?
YeS, as far as the SQL interface is used.
Q from Colin: Is the FK support planned for all storage engines in future versions?
We are working on this. We are still unsure about global support for all the storage engines, but this is definitely one of the possible solutions.
Q from Manuel: If you have 2 management servers on different machines and you lose network connection between them, don't you have a split-brain situation?
Not necessarily. As long as there is an odd # of arbitration capable nodes, you don't.
Q from Jonas: Is there any estimate in time on when the 'next' MySQL version, with FK at SQL level will be available?
Not at the moment, but this is one of the hottest topics within MySQL.
Q from Manuel: Can you explain or give a URL where the use of redundant management servers is explained?
In the reference manual for version 5.0 - section 15.4.4.2 - there are examples of connection strings with multiple management nodes
Q from Guy: What happens if the DB drows significantly in a short period therefore size increases above the memory limit?
The size is fixed and preallocated in this point in time. When available memory is full it's full. 5.1 will be able to have non-indexed data on disk, which will change this.
Q from Danilo: When will you plan to introduce multiple index use in mysql select?
It is in there already, to an extent. I assume you ask about multiple indexes per table, and again, that is to an extent already implemented. The usefulness of this depends on the stoarge engine though. Falcon will support this in a very effective manner.
Q from Hilmar: How do I know for sure that a certain transaction is sent to disk asap? is possible to trigger a checkpoint using an SQL statement?
You can setup the system in order to have continuous checkpoints, although this is not the answer to the problem. The checkpoint will not be related to a single commit in any case. MySQL Cluster architecture has been designed to be an in-memory database, therefore the infrastructure underneath must be able to support the architecture. Bear in mind that MySQL cluster achieves HA through redundancy; since you can have up to 4 replicas, in the worst case scenario you have to have 4 faults on 4 servers and you may lose data executed after the last position available in the REDO LOG on disk - I am not aware of any other HA architecture that may achieve more than this, whether it is in-memory or on disk.
Q from Hilmar: But what happen if you have a power failure for the whole data center?
First of all, you should have UPS systems in place that should at least allow you to shut down the server gracefully, in order to not lose any data at all. Secondly, with version 5.1 you may use geographic replication and activate the cluster in another data center.
Q from Jonas: Is MySQL Cluster Replication is done on a node-by-node basis, or the cluster as a whole?
In 5.1, it is Cluster to Cluster.
Q from Hilmar: Has mysql cluster already been tested on sparc T1's for the SQL nodes? And are these a better option than a regular AMD box ?
SUN HW and Solaris and well supported, you have only to take care of having the same hardware type (SPARC, Intel or AMD) between nodes.
No, the data node is handled by a separate process, ndbd, that only manages data.
Q from Olivier: So, what is MySQL Cluster? A MySQL AB product ?
Technically speaking, MySQL Cluster is a storage engine, based on a network distributed database. From a commercial point of view, we refer at MySQL Cluster as a product. We also provide APIs to access to the Cluster database directly, bypassing the MySQL Server and the storage engine architecture.
Q from Ludovico: Are there load balancing mechanisms to balance sql requests between active sql server nodes?
Yes, absolutely. The SQL nodes all see the same data, and load may well be balanced between them, if necessary.
The only caveat is that transactions must be locked into the same SQL server for the duration of the transaction.
In real life, load balancing can be achieved through hardware and software load balancers, or using the capabilities provided by some connectors such as JDBC, or just handling the load balancing at http level to the web servers, assuming that on average they will generate pretty much the same workload.
Q from Ludovico: What about embedded solutions, HW load balancers or round robin dns?
Any of them should work. Keep in mind the transaction caveat though. The load balancer must be activated when the client must initiate the connection, then the transaction must be related to the same connection.
Q from Roberto: Using the API and the native access, do we lose whole or part of the HA features ?
No, not at all, Cluster, even without the SQL nodes, is fully reduntant. The HA features are at the Cluster level, below the SQL nodes.
Q from Vasilij: Does Linux support MySQL Cluster?
Absolutely, various Linux and Unices are fully supported
Q from Manuel: Is the management server is a Single Point of Failure?
No, it's not. First of all, you may have as many as you want. Secondly, it is important that the management server is on when a new node is added to the cluster or in case of failure in a small cluster with only two data nodes. During normal operations, it could be switched off.
Typically, the management node is a lightweight process and it can be colocated with other servers.
Q from Manuel: You said that the minimum number of nodes is 3. Can't we have only two nodes and the management server running on both nodes and so you have absolute redundancy with only 2 boxes?
No, having 2 nodes is not a good idea, as that might cause a split-brain situation. If you implement such configuration, MySQL Cluster will issue a warning
Q from Olivier: Where are the data stored? On MySQL servers? On the NDBD nodes ? In memory or on disk ?
Data is In the data nodes (NDB nodes) and data is in memory. With version 5.1, data can be optionally on disk, although indexes must still reside in memory.
Q from Manuel: May the management servers run on the same machine where the nodes or the SQL Servers run?
Yes, they can. There is no need for an extra physical server, provided the cluster has at least 3 physical servers.
Q from Colin: What is the simplest cluster config if all access is via C++ API?
3 nodes. 2 datanodes, one for mgm, which may be colocated with the application node, for example.
Q from John: All the examples of hostnames in the cinfig file are ipv4 addresses - does cluster support ipv6?
No, not at the moment.
Q from John: If mysql nodes and data nodes are on the same boxes can they share IP's?
Sure, they can share the same address, as they respond to different ports
Q from Manuel: What is a split-brain and how does it happen?
A split brain is a particular situation that may happen in some HA infrastructures. It may happen that in an HA solution with only two servers, in case of network fault between the servers, the two nodes cannot talk each other, but they are both active and can provide the service to their clients. In this case we have a split brain situation. MySQL cluster does not allow a split brain, since it requires a third node as arbitrator.
Q from Danilo: Is is possible to have multiple INSERT coming from different MySQL Server that require auto_increment fields?
Yes, that is absolutely possible and fully supported.
Q from Olivier: Is all the datas on every node? What happens, for example, if I have 4 nodes with 20GB of memory for each server?
First of all, you have to specify the exact number of replicas that you want. You can have only one copy of data (NoOfReplica=1), but in this scenario the system will not be redundant. You can have up to 4 replicas. WIth a classic replica of 2, you will have only two copies of the same data. In the example that you have mentioned, the total memory available will be 4 (nodes) x 20 (memory) / 2 (replica) = 40 GB in total.
Q Danilo: How many data nodes are supported?
The current limit is 48.
Q from Domenico: Can MySql Server and Data Node reside on the same physical server?
Yes, they can. For better scalability and performance, you should consider separate nodes though.
Q from Colin: If the data is not on disk which safety/backup policy is advised in case of total system failure?
There is a checkpointing and redo logging system that assure that the data are safely stored on disk. You can also used the online backup provided for disaster recovery.
Q from Ludovico: What happens if a power shortage happens and all my data nodes are turned off?
In this case you have to recover from disk up to the latest checkpoint and then you can apply everything up to the latest position in the redo log. Bear in mind you should configure the whole infrastructure in a way that is suitable for high availability.
Q from Hilmar: So in the worst case scenario, when a checkpoint was not fired after an updated, you loose power, then cluster recovers from loss of power and after the restart of the servers it has ' rolledback' to its previous checkpoint, it that correct?
First it recovers up to the latest checkpoint, then it applies the redo log.
Q from David: Does the Transaction Coordinator ensure referential integrity?
No, it does not. Foreign keys are not supported in the current release and not in 5.1
Q from Guy: Is the whole database held in memory or just the tables in use?
The whole database is in-memory. That is the attribute of an In-Memory database, to achieve deterministic performance.
Q from Ludovico: Il the FK support at SQL level coming also for NDB?
YeS, as far as the SQL interface is used.
Q from Colin: Is the FK support planned for all storage engines in future versions?
We are working on this. We are still unsure about global support for all the storage engines, but this is definitely one of the possible solutions.
Q from Manuel: If you have 2 management servers on different machines and you lose network connection between them, don't you have a split-brain situation?
Not necessarily. As long as there is an odd # of arbitration capable nodes, you don't.
Q from Jonas: Is there any estimate in time on when the 'next' MySQL version, with FK at SQL level will be available?
Not at the moment, but this is one of the hottest topics within MySQL.
Q from Manuel: Can you explain or give a URL where the use of redundant management servers is explained?
In the reference manual for version 5.0 - section 15.4.4.2 - there are examples of connection strings with multiple management nodes
Q from Guy: What happens if the DB drows significantly in a short period therefore size increases above the memory limit?
The size is fixed and preallocated in this point in time. When available memory is full it's full. 5.1 will be able to have non-indexed data on disk, which will change this.
Q from Danilo: When will you plan to introduce multiple index use in mysql select?
It is in there already, to an extent. I assume you ask about multiple indexes per table, and again, that is to an extent already implemented. The usefulness of this depends on the stoarge engine though. Falcon will support this in a very effective manner.
Q from Hilmar: How do I know for sure that a certain transaction is sent to disk asap? is possible to trigger a checkpoint using an SQL statement?
You can setup the system in order to have continuous checkpoints, although this is not the answer to the problem. The checkpoint will not be related to a single commit in any case. MySQL Cluster architecture has been designed to be an in-memory database, therefore the infrastructure underneath must be able to support the architecture. Bear in mind that MySQL cluster achieves HA through redundancy; since you can have up to 4 replicas, in the worst case scenario you have to have 4 faults on 4 servers and you may lose data executed after the last position available in the REDO LOG on disk - I am not aware of any other HA architecture that may achieve more than this, whether it is in-memory or on disk.
Q from Hilmar: But what happen if you have a power failure for the whole data center?
First of all, you should have UPS systems in place that should at least allow you to shut down the server gracefully, in order to not lose any data at all. Secondly, with version 5.1 you may use geographic replication and activate the cluster in another data center.
Q from Jonas: Is MySQL Cluster Replication is done on a node-by-node basis, or the cluster as a whole?
In 5.1, it is Cluster to Cluster.
Q from Hilmar: Has mysql cluster already been tested on sparc T1's for the SQL nodes? And are these a better option than a regular AMD box ?
SUN HW and Solaris and well supported, you have only to take care of having the same hardware type (SPARC, Intel or AMD) between nodes.
Monday 7 May 2007
Questions and Answers in the Second Session of the Online Solutions with MySQL Webinar - On Replication
Correction on the INSERT DELAYED
In slide 20 I have mentioned that the INSERT DELAYED statement can increase performance on the slave. This is wrong, since the DELAYED keyword is ignored by the SQL thread on the slave server. The INSERT DELAYED statement can increase the overall performance of an application since the control is returned to the client as soon as the row is queued into the list of inserts to execute. The INSERT DELAYED can be used with MyISAM, MEMORY and ARCHIVE.
Q from Filip: Does master & slave have to be the same db-version, and the same Operative system?
Not necessarily, you can have different versions and operating systems
Q from Danilo: Is there a way to load a backup from the master without locking tables or shutting down the master database?
Using Innodb, you can use mysqldump to produce a consistent backup without locking the tables
Q from Henk: How are primairy keys communicated back from the slave to the master
There is no need: the primary keys generated on the master are pushed "as is" on the slave / they are not regenerated
Q: How does a slave follow up an insert?
It receives the keys generated by the master, so if the slave implements foreign keys (although they would not be necessary), the data on the DB is consistent
Q from Martin: How can mysql prevent binary log corruption when link between master and slave breaks? This has happened to me, which stopped the replication thread indefinitely
The IO thread on the slave stops pumping the log. As soon as the master restart the slave restart from where it stopped
Q from Brendan: What is the latency between a transaction being committed on the master to the same transaction being committed on the slave in a replicated environment?
It depends on several factors - hardware, storage engine, workload etc. From a replication point of view, the transaction is first recorded on the relay log and then it's applied to the slave.
Q from Helen: Are all the DBs on the master server replicated? Can you selectively replicate databases?
You can selectively include or exclude DBs. This works down to the table level
Q from James: Are client-updates prohibited on a slave system when the master is active? If not then how do you prevent inconsistencies?
They are allowed. The slave can be used for any task. it is your responsibility to write only on the master. In order to make the slave read-only, you should work at a user-security level
Q from Jonas: If I understand right, I could configure the master on both servers to address them with a virtual IP, is that correct?
Yes, that is correct, you can do it in order to simplify and speed up the failover
Q from Volker: The HA solution can only work if the binlog positions on Master and HA slave are the same, right?
No, the slave can be a little delayed. For synchronous HA we use others techniques like IO replication (DRBD) or shared disk architecture
Q from Paul: How does master-master replication perform with cross continent latency? eg: UK & USA
MySQL Replication is asynchronous so it will not be affected by latency. The use of separated threads avoid that the geographical position would affect the activity on the slave DB.
Q from Danilo: What does "Non-deterministic writes" mean?
An example of it is an INSERT statement that contains the RAND() function
Q from Dave: Can replication handle 'load data infile' type operations?
Yes, the data loaded is stored in a temporary file in the tmp directory and passed to the slave through the IO thread. The SQL thread will load the data from a file available in the tmp directory of the slave server. The file are security protected from access as other.
Q from Owen: Could you use blackhole on Master 1 and split data afterwards to mutiples partioned DBs
Yes, blackhoe storage is a good way to build a relay before replication split
Q from Andrew: If the update is async, is there a possibility that an update x made after update y in the server is performed y and then x on the slave?
If done in a different transaction that commit in a reverse order it might happen. The locking mechanism of InnoDB and the default isolation level (repeatable read) make replication consistent
Q from Alessandro: I've never heard about BLACKHOLE storage engine. What is it designed for?
It is a storage engine that store nothing in the db but the binlog is generated anyway. It's used to build a relay server, reduce the use of resources and improve speed
Q from John: Can you specify a master db by host name, or must it be an IP address?
You can use either the hostname or the IP address
Q from Clive: Is there any drawback to using dual-master replication (with auto-incr offsets) to allow automatic client failover with complete safety?
Bear in mind that replication is asyncronous. For a complete safety, MySQL cluster might be the solution. or some other HA techniques : IO replication / shared disk
Q from Rob: Does master & slave have to be the same db-version, and the same Operative system. You can always replicate to a newer version right?
You can replicate to newer version and/or different OSs
Q from Rob: Is it possible to share query cache over multiple slaves?
No, the query cache are on a server basis
Q from Daniel: If I save binlogs in a NAS and net connection/write fails, will the server still work?
The master must be able to write to its current log and to generate the next one. Then, once logs are rotated, they can be pushed anywhere for safety. NAS is a solution.
Q from Carsten: Can 2 masters read from the same database files? (Implementing DFS for example) ?
This can be done using cluster-aware file systems, but there are some aspects to be carefully considered, such as the storage engine to use and the query locking.
Q from Phil: What do you think the impact of placing data and logs on different drives is?
This is an important point to optimize the IOs and it is recommended
Q from Manuel: I think multimaster replication writing on both to a table with autoincrement column will cause heavy fragmentation of data and index. How can this affect to query performance?
If load is fairly balanced between nodes, I do not see this as a problem. In other cases, it might be necessary to periodicaly optimize tables.
Q from Domenico: Is ti possible to replicate from more then one masters to fews slaves?
Currently, a slave can be linked only to one master. There are plans to change this and create multisource replication
Q from Phil: Is the use of high performance RAID important for the logs?
Absolutely, performance on the master will get benefit from this
Q from Alessandro: What is vertical partitioning?
It's a way to separate data by columns instead of rows. For example, if a large table has 10 columns, C1, C2 to C10, one can split this table into two tables: the first table will contain, for example, C1, C2 and C3, which are the most accessed columns for read and write. The second table will contain the primary key and the remaining 7 columns, that will be rarely updated. This approach can improve performance significantly, since the tables are smaller, the columns fit easier in the blocks used by the storage engine and in the caching mechanisms.
Q from Ian: When is it a good idea to choose MySQL Cluster rather than using replication for HA?
Replication and Cluster are two completely different technologies. In short terms, Replication is the right choice for scale-out solutions when there are lots of read operations, such as on web search and read. MySQL Cluster is mainly used when the ratio of read and write operations is closed to 1:1 and when queries affect small resultsets with direct key access or simple joins.
Q from Ap: What is the best method to load data into the slave, use mysqldump or use "load data from master"
LOAD DATA FROM MASTER is deprecated, the best solution is to use mysqldump
Q from Manuel: With master/slave replication you mentioned "automatic failover", how can you do that?
You can use clustering and HA software such as Linux HA to control the activity of the master. The software can switch over to the slave in case of issues. The client application will get access to the new master using a virtual IP address.
Q from Danilo: Is it possible connect master and slave with a serial link?
It's possible to connect master and slaves to any TCP/IP connection
Q from Jon: For those people who aren't lucky enough to have access to MySQL Enterprise - are there any other methods of monitoring slave status (e.g. SNMP)?
Yes, there are some monitoring tools that could help: Munin, Cacti and Ganglia might help. You can find information on these tools in the third webinar of the series (check the slides here)
Q from Jonas: If I have a 2-node cluster, with Heartbeat, each node has one IP (let's say IP1 and IP2, and share a VIP), can I configure both servers to be slaves, and the master be the VIP? Is not the active node both master and slave of himself?
Yes, in a sense, you can have a circular master replication and the master is accessible using the VIP
Q from Owen: What is best configuration for HA in a MyISAM environment with high OTW but also high muti joined selects for reporting?
IMO, the multi-joined selects are not affected by the HA solutions provided by MySQL. MyISAM can certainly help (provided that the system is generally safe from index corruption. If the requirement is to have a reporting server with multi-joined selects aside a classic OLTP server, then replication is probably the best solution.
Q from Manuel: When you configure a slave, you didn´t configure the log pos, why?
In the example, we supposed to start from a fresh installation. The log position is important when there the master and the slave must be synchronised while the system is running.
In slide 20 I have mentioned that the INSERT DELAYED statement can increase performance on the slave. This is wrong, since the DELAYED keyword is ignored by the SQL thread on the slave server. The INSERT DELAYED statement can increase the overall performance of an application since the control is returned to the client as soon as the row is queued into the list of inserts to execute. The INSERT DELAYED can be used with MyISAM, MEMORY and ARCHIVE.
Q from Filip: Does master & slave have to be the same db-version, and the same Operative system?
Not necessarily, you can have different versions and operating systems
Q from Danilo: Is there a way to load a backup from the master without locking tables or shutting down the master database?
Using Innodb, you can use mysqldump to produce a consistent backup without locking the tables
Q from Henk: How are primairy keys communicated back from the slave to the master
There is no need: the primary keys generated on the master are pushed "as is" on the slave / they are not regenerated
Q: How does a slave follow up an insert?
It receives the keys generated by the master, so if the slave implements foreign keys (although they would not be necessary), the data on the DB is consistent
Q from Martin: How can mysql prevent binary log corruption when link between master and slave breaks? This has happened to me, which stopped the replication thread indefinitely
The IO thread on the slave stops pumping the log. As soon as the master restart the slave restart from where it stopped
Q from Brendan: What is the latency between a transaction being committed on the master to the same transaction being committed on the slave in a replicated environment?
It depends on several factors - hardware, storage engine, workload etc. From a replication point of view, the transaction is first recorded on the relay log and then it's applied to the slave.
Q from Helen: Are all the DBs on the master server replicated? Can you selectively replicate databases?
You can selectively include or exclude DBs. This works down to the table level
Q from James: Are client-updates prohibited on a slave system when the master is active? If not then how do you prevent inconsistencies?
They are allowed. The slave can be used for any task. it is your responsibility to write only on the master. In order to make the slave read-only, you should work at a user-security level
Q from Jonas: If I understand right, I could configure the master on both servers to address them with a virtual IP, is that correct?
Yes, that is correct, you can do it in order to simplify and speed up the failover
Q from Volker: The HA solution can only work if the binlog positions on Master and HA slave are the same, right?
No, the slave can be a little delayed. For synchronous HA we use others techniques like IO replication (DRBD) or shared disk architecture
Q from Paul: How does master-master replication perform with cross continent latency? eg: UK & USA
MySQL Replication is asynchronous so it will not be affected by latency. The use of separated threads avoid that the geographical position would affect the activity on the slave DB.
Q from Danilo: What does "Non-deterministic writes" mean?
An example of it is an INSERT statement that contains the RAND() function
Q from Dave: Can replication handle 'load data infile' type operations?
Yes, the data loaded is stored in a temporary file in the tmp directory and passed to the slave through the IO thread. The SQL thread will load the data from a file available in the tmp directory of the slave server. The file are security protected from access as other.
Q from Owen: Could you use blackhole on Master 1 and split data afterwards to mutiples partioned DBs
Yes, blackhoe storage is a good way to build a relay before replication split
Q from Andrew: If the update is async, is there a possibility that an update x made after update y in the server is performed y and then x on the slave?
If done in a different transaction that commit in a reverse order it might happen. The locking mechanism of InnoDB and the default isolation level (repeatable read) make replication consistent
Q from Alessandro: I've never heard about BLACKHOLE storage engine. What is it designed for?
It is a storage engine that store nothing in the db but the binlog is generated anyway. It's used to build a relay server, reduce the use of resources and improve speed
Q from John: Can you specify a master db by host name, or must it be an IP address?
You can use either the hostname or the IP address
Q from Clive: Is there any drawback to using dual-master replication (with auto-incr offsets) to allow automatic client failover with complete safety?
Bear in mind that replication is asyncronous. For a complete safety, MySQL cluster might be the solution. or some other HA techniques : IO replication / shared disk
Q from Rob: Does master & slave have to be the same db-version, and the same Operative system. You can always replicate to a newer version right?
You can replicate to newer version and/or different OSs
Q from Rob: Is it possible to share query cache over multiple slaves?
No, the query cache are on a server basis
Q from Daniel: If I save binlogs in a NAS and net connection/write fails, will the server still work?
The master must be able to write to its current log and to generate the next one. Then, once logs are rotated, they can be pushed anywhere for safety. NAS is a solution.
Q from Carsten: Can 2 masters read from the same database files? (Implementing DFS for example) ?
This can be done using cluster-aware file systems, but there are some aspects to be carefully considered, such as the storage engine to use and the query locking.
Q from Phil: What do you think the impact of placing data and logs on different drives is?
This is an important point to optimize the IOs and it is recommended
Q from Manuel: I think multimaster replication writing on both to a table with autoincrement column will cause heavy fragmentation of data and index. How can this affect to query performance?
If load is fairly balanced between nodes, I do not see this as a problem. In other cases, it might be necessary to periodicaly optimize tables.
Q from Domenico: Is ti possible to replicate from more then one masters to fews slaves?
Currently, a slave can be linked only to one master. There are plans to change this and create multisource replication
Q from Phil: Is the use of high performance RAID important for the logs?
Absolutely, performance on the master will get benefit from this
Q from Alessandro: What is vertical partitioning?
It's a way to separate data by columns instead of rows. For example, if a large table has 10 columns, C1, C2 to C10, one can split this table into two tables: the first table will contain, for example, C1, C2 and C3, which are the most accessed columns for read and write. The second table will contain the primary key and the remaining 7 columns, that will be rarely updated. This approach can improve performance significantly, since the tables are smaller, the columns fit easier in the blocks used by the storage engine and in the caching mechanisms.
Q from Ian: When is it a good idea to choose MySQL Cluster rather than using replication for HA?
Replication and Cluster are two completely different technologies. In short terms, Replication is the right choice for scale-out solutions when there are lots of read operations, such as on web search and read. MySQL Cluster is mainly used when the ratio of read and write operations is closed to 1:1 and when queries affect small resultsets with direct key access or simple joins.
Q from Ap: What is the best method to load data into the slave, use mysqldump or use "load data from master"
LOAD DATA FROM MASTER is deprecated, the best solution is to use mysqldump
Q from Manuel: With master/slave replication you mentioned "automatic failover", how can you do that?
You can use clustering and HA software such as Linux HA to control the activity of the master. The software can switch over to the slave in case of issues. The client application will get access to the new master using a virtual IP address.
Q from Danilo: Is it possible connect master and slave with a serial link?
It's possible to connect master and slaves to any TCP/IP connection
Q from Jon: For those people who aren't lucky enough to have access to MySQL Enterprise - are there any other methods of monitoring slave status (e.g. SNMP)?
Yes, there are some monitoring tools that could help: Munin, Cacti and Ganglia might help. You can find information on these tools in the third webinar of the series (check the slides here)
Q from Jonas: If I have a 2-node cluster, with Heartbeat, each node has one IP (let's say IP1 and IP2, and share a VIP), can I configure both servers to be slaves, and the master be the VIP? Is not the active node both master and slave of himself?
Yes, in a sense, you can have a circular master replication and the master is accessible using the VIP
Q from Owen: What is best configuration for HA in a MyISAM environment with high OTW but also high muti joined selects for reporting?
IMO, the multi-joined selects are not affected by the HA solutions provided by MySQL. MyISAM can certainly help (provided that the system is generally safe from index corruption. If the requirement is to have a reporting server with multi-joined selects aside a classic OLTP server, then replication is probably the best solution.
Q from Manuel: When you configure a slave, you didn´t configure the log pos, why?
In the example, we supposed to start from a fresh installation. The log position is important when there the master and the slave must be synchronised while the system is running.
Friday 20 April 2007
Slides of the second webinar on "Online Solutions with MySQL"
The slides are available in PDF format here.
Thanks to all of you who have participated to the webinar!
-ivan
Thanks to all of you who have participated to the webinar!
-ivan
Wednesday 18 April 2007
The "Hands on Replication" webinar is ready to go
Tomorrow at 10.00 CET we will present the second webinar of the Online Solutions series.
We will talk about MySQL Replication and we will see some of the advanced techniques. We will set up basic, circular and multi-master replication on the fly, for those of you who are not yet familiar with this technology.
There are also hints, tips and tricks for advanced users and DBAs, with suggestions on hardware, storage engines and performance considerations, plus real examples from some big names - Yahoo, Flickr and Wikipedia to name few.
Thanks to everybody for the interest! This is the most successful set of webinars ever presented in Europe so far...
...and there's much more to see!
Stay tuned and see you tomorrow!
-ivan
P.S. The registration to the online webinar is here.
We will talk about MySQL Replication and we will see some of the advanced techniques. We will set up basic, circular and multi-master replication on the fly, for those of you who are not yet familiar with this technology.
There are also hints, tips and tricks for advanced users and DBAs, with suggestions on hardware, storage engines and performance considerations, plus real examples from some big names - Yahoo, Flickr and Wikipedia to name few.
Thanks to everybody for the interest! This is the most successful set of webinars ever presented in Europe so far...
...and there's much more to see!
Stay tuned and see you tomorrow!
-ivan
P.S. The registration to the online webinar is here.
Wednesday 28 March 2007
Questions and Answers During the First Session
On Server and Storage Engines
Q from Alessandro: What about the filesystem for MyISAM in a small company? Better ReiserFS or EXT3?
As Anders said, it's a matter of taste. XFS is a good alternative too. Ext3 is stable, but sometimes slow and a bit outdated. Many like Reiser, but it is sometimes difficult to manage. XFS often gives you good performance and reliability out of the box.
Q from Florian: MySQL 5.1 == Falcon?
Falcon is available for download today. The current version refers to 5.2 and it's an alpha version. We may change plans, but so far Falcon is related to version 5.2
Q from Henk: Will InnoDB ultimately be replaced by Falcon?
No, it will not. Falcon is an alternative to InnoDB and willl be especially excellent for online applications, but we will continue to support InnoDB for many years ahead.
Q from Paul: Will Falcon include the spatial features of MyIsam?
Anders has clearly replied to this question. Falcon will initially it will support spatial datatypes, but not R.tree indexes. GIS datatypes are available out of MyISAM since 5.0, although R-tree indexes are limited to MyISAM.
Q from Owen: what is the roadmap for myISAM 2.0
We do not have any roadmap for MyISAM 2.0 at the moment.
Q from Owen: Will not myISAM 2.0 support foreign keys?
We are considering to provide FK at an upper layer - ie not in the storage engine - hence every storage engine will support FKs.
Q from Danilo: What's the best storage engine to manage very big data?
We can't simplify too much, but generally speaking we may say that InnoDB and MyISAM are the usual suspects. InnoDB takes little time to recover in case of failure (referring to our HA webinar), but it usually needs more space than MyISAM to store the same data.
Performance-wise can handle high concurrency better, whilst MyISAM might perform better with intensive reads. These of course are only generic guidelines.
At the UC in April we will see new storage engines that might be interesting as well.
Q from Colin: The majority of data warehouses are designed using the Kimball methodology using a star schema. This relies on foreign keys. How is MyISAM ideal for this?
Well, I have spent the last 12 years of my working life on Data Warehouse solutions and I would say exactly the opposite. In 100% of the DW that I have designed there were no FKs. two valid reasons: performance and ETL issues. I tend to consider foreign keys essential in an OLTP environment, specifically when we have different applications relying on the same database or schema. But in a DW, generally speaking, we can control the loading process. Since we are loading a massive amount of data at a time, we need fast import and write - and MyISAM is perfect for that. FKs would slow everything down, checking that the data we are loading is consistent. Also, applying FKs to a DW environment means that we will be forced to load data in a specific order, otherwise we will not find references for new records to load. Hence, no FKs for me in a DW. Integrity must be handled with a good ETL process.
Q from Efrat: If I want to use foreign keys, Do I have other alternative besided InnoDB?
As Anders said, right now, we do not have alternatives, but we are working on that.
A stop-gap-solution is to use triggers.
Q from Andrew: What is the expected time frame for this FK support in the sql layer of all storage engines?
We are considering to implement FK support in 5.2 or future versions - hence, not in 5.1.
Q from Tero: Can you back up data in a single instance while reading and writing?
We will cover the backup strategies in one of the next sessions. In the meantime, Anders has answered to this question: there are some different ways for doing this. A logical backup can do the job, specifically if we use InnoDB - since we can handle concurrency and isolation. Another alternative is the use of storage engine specific backup solutions, such as the one that is included with NDB or inno backup.
Q from Andrea: Which is the Best Management and Monitoring tool to work with Mysql?
The Monitoring and Advisory Service included in MySQL Enterprise acts as a "Virtual DBA Assistant" and interface to popular monitoring tools such as HP Open View, Tivoli etc via SNMP traps
Q from Alessandro: What SMP stands for?
Symetric Multi Processing: The technology that supports multiple CPU cores to work together.
Q from Henk: Is there a list available of business applications that each need a specific type of of all the types of scaling that Ivan speaks of?
Please contact your sales rep, who will get you in contact with an SE that can help you out.
On Connectors and Development Environments
Q from Fabio: what is the VB connector?
It is a .NET native provider - http://dev.mysql.com/downloads/connector/net/5.0.html
There is also a Visual Studio plugin available for development - http://dev.mysql.com/downloads/visual-studio-plugin/index.html
Q from Wenter: How about ASP?
.NET is the connector to use here
Q from Alessandro: Is it possibile to use InnoDB features (transactions) inside VB applications (e.g. inside an Access project)? Is there any documentation around?
Yes. There are some Access - MySQL migration docs, and there is also a forum on forums.mysql.com on Access
Q from Fabio: MySQL Visual Studio don't work , it creates many problem to the IDE?
Unfortunately, the Visual Studio Plugin is still a beta version - We would encourage you to post bugs on our bug systems
Q from Henry: Any plans for a connector for Delphi?
I am not a big expert of Delphi, but I think that a connector should be available from Borland to access MySQL through the new DBX4 Layer. The layer is compatible with MySQL 5.0.
Q from Emil-Valentin: How do you have an easy GUI to administer the mysql database, as much as easy as MSAccess?
MySQL administrator is one way, MySQL Enterprise customers have access to MySQL Monitoring and Advisory Service tool. phpMyAdmin is popular in Linux / PHP environemnts. SQLYog is also common. Quest has Toad for MySQL which is also popular in some circles.
MySQL Enterprise includes GUI tools and the Monitoring and Advisory service to help you easily administer all your servers. And you can also use Access as a front end to MySQL.
Check sourceforge.net or forge.mysql.com for tools, there is a lot there. Or ask the forums at forums.mysql.com
Q from Eric: Is there a good php based load balancing script available to select the least loaded mysql (slave) server?
Not as far as I am aware. In many cases, the balancing on multiple web/application server is good enough to provide a consequent balancing on multiple slave servers - associating a round robin mechanism or a different preferred connection for each web server.
Q from Constantinos: Do you know if there is a "high available connector" for perl or php?
I am not aware of any. But for example I have found this comment quite interesting, especially in conjunction with Replication: http://uk3.php.net/function.mysql-pconnect
Q from Andrew: is there an equivalent of esqlc?
Not at the moment. There are some scripting solutions, mainly awk based. You may check this: http://dev.mysql.com/tech-resources/articles/precompiler-for-embedded-sql.html
On Replication
Q from Melvin: Is it possible to have multiple masters and a single slave?
This is not possible, strictly speaking, using a classic configuration. We have planned to have multiple sources for a slave sometimes in the future versions. At the moment, we may use the Federated Storage Engine, as we will cover in the next session.
Q from Jonas and Danilo: What about multi-master replication?
We will cover it in the next session.
Q from Jonas: Is circular replicaiton a good HA option?
Yes, this is often good enough, but not for all the applications. The main drawbacks are conflict detection and the asynchronous nature of replication. But it is definitely an option, and an attractive one.
Q from Danilo: Is there an automatic procedure to swicth a slave to master when the master die?
No, but there is pretty good documentation on the linux-ha.com website on how to do this. Usually, you don't do this though, you set up both systems as being masters AND slave, but only one is active master.
Q from Efrat: How can I make sure that the slave database is identical to the master database?
The slave keeps track of the position on the binary log of the master database and it can provide information on a delay in updating the DB, although this is acceptable only if there are no failures.
Q from Efrat: Is there a mysql command that compares the databases in a master and in the replicated slave?
Not at the moment. The Monitoring and Advisory Service can provide some extra information to prevent delays and problems though.
Q from Juan: How many mysql server clients supports a mysql master host?
This is usually limited by OS resources more than MySQL itself, although the MySQL config may limit this.
Q from Lorenzo: How many server can I use in circular-replicaton (master-master)? Is there a max value?
In theory no, but it will get complex to maintain after a while.
Q from Danilo: what about auto_increment fields... will be a sequence filed like oracle? I am asking this because this is the principal problem of multi master replication.
I agree. As I know, there is nothing planned right now.
On MySQL Cluster
Q from Matthew: When are we likely to see disk based indexing for ndb?
Disk based indexing is planned in one of the future releases, but we can't say when we will implement it. During the webinar, Anders pointed out that he does not see this as an important thing. I tend to agree with Anders, at least considering the current status of the storage engine. At the moment, ndb can perform an unbeatable job (in terms of HA and performance) on small transactions and simple queries and we should not consider it as a full replacement for the whole database, in general. The future versions of ndb will probably be more and more general purpose and at some point a full disk based ndb will be valuable. Please take this as my personal opinion.
Q from Malcolm: Is their any difference between MysQL Cluster and the telecoms version?
As Bertrand said, MySQL Cluster Carrier Grade is a specific version for telecom, developed closely with major equipment manufacturers. During the presentation I have highlighted some differences - such as the availability of more data nodes and so on. We will cover MySQL Cluster and MySQL Cluster Carrier Grade Edition in one of the future sessions.
Q from Fabio: Any plan for MySQL Cluster for Windows?
We are considering it sometimes in the future, but no plans have been made so far.
Q from Andrea: What happen if the Management Console crash in a Mysql Cluster environment?
As Anders said, it's not a big deal. The mgm is not necessary to keep the cluster running in normal cases, and you can have more than one mgm server.
Q from Owen: Is it difficult to define memory requirements for MySQL Cluster?
MySQL Cluster configuration is the most important step when you adopt this technology. We have seen several do-it-yourself configurations, running perfectly. But Cluster configuration is not straightforward and we always recommend to get some help from our Professional Services team.
Q from Sebastian: How to commit database structure changes on mysql in mysql cluster, is any tool for this?
I am not sure I understand the question - Sebastian, if you read this blog, feel free to add more details. If you refer to the possibility to change (ALTER) a table structure, this is possible and once you flush the table (FLUSH TABLE) the change is available to all the SQL nodes.
Q from Florian: Can I put an IP load balancer in front of the Interface/SQL layer in Cluster, and address the whole cluster using 1 public IP address?
Yes, this is fine. All SQL-nodes see the same data and are always in read/write mode.
Q from Xabier: how many transactions can stand a clustered mysql db?
It depends on the configuration and the application. Using the low-level NDB API, you could sustain 100's of thousands transactions per second.
Q from Juan: Is the ndb engine avaible for QNX?
Yes, there is a version for QNX 6.2.1
Q from Xabier: does a clustered mysql db require horizonal or vertical partitioning the database?
MySQL Cluster partitions the database automatically. We will cover Cluster in one of the next sessions.
Q from Andrea: When will be available the Mysql Cluster Carrier Grade Edition?
MySQL Cluster Carrier Grade Edition is available now, you can download a free whitepaper on it at: http://www.mysql.com/why-mysql/white-papers/
Q from Alessandro: Is carrier grade avalaible for download?
As Bertrand said, please contact us at http://www.mysql.com/company/contact/ if you are interested in MySQL Cluster Carrier Grade for telecom customers
On Shared Disk Cluster
Q from Efrat: Can I implement a Shared Disk Cluster using the Community Server?
Although we recommend to use MySQL Enterprise, one can use a Community Edition to implement a shared disk cluster. The recommendation to use MySQL Enterprise can be easily explained: since we are looking for High Availability and reliability, it does not make much sense to risk unplanned downtime caused by faults that can be solved using hot patches or escalating bugs. Also, the use of the Monitoring and Advisory Service could help in preventing such faults - we will cover it in one of the next sessions.
Q from Doug: Do shared disk cluster solutions require proprietary OS's? I see no community/free Linux editions stated on the slides?
Normal OS's are fine. Linux supports things like Sistina (owned by RedHat), but not even that is necessary. You can run ext3 on a SAN.
You just need drivers for the SAN. The SAN looks like any disk to the OS after those are installed, and any type of partition can be installed, but an LVM is nearly always used (but this is part of any Linux distro anyway these days).
Q from Tero: what does "External Locking" mean when we talk about Shared Disk Cluster?
As Anders perfectly explained, it is when the OS locks files, even across nodes in a distributed filesystem, like lockd when using NFS.
On DRBD
Q from Fabio: what is DRBD?
DRBD stands for Distributed replicated block device - more info here: http://www.drbd.org/
Q from Fabio: This DRBD is only for Linux ?
Yes it is. It's a Linux kernel module with userspace management tools. It doesn't support other Unices as of today.
Q from Efrat: In DRBD, if the storage of the master is damaged, doesn't it mean that the slave will be damaged too?
DRBD has some fault-detection mechanisms built-in. So disk errors aren't that much of an issue, although errors in the layers above DRBD are of course undetectable by DRBD itself.
Q from Jonas: What is better, MySQL Replication or DRBD?
Well, we can't say that something is better than something else, because it depends on the solution you are looking for. For some applications and specific needs, MySQL Replication is better; for others DRBD is the solution. As Anders pointed out, MySQL Replication is asynchronous, whilst DRBD is synchronous. Also, DRBD replicates anything that can be written on a file system, whilst MySQL Replication is only related to DB write operations. Hence, there is not a straight answer.
More Questions
Q from Efrat: Will you have a conference in Europe too?
We will have customer conferences as last year in Europe, most likely in London, Munich and Paris in Q4
Q from Emil-Valentin: I would like to download trustiness and clear howto's for web enabling and enabling openoffice as frontend for administering. May you help me?
It's not easy to find these information. Our knowledge base and the consultative support provided by MySQL Enterprise Platinum might help, but we would need to know a bit more about your requirements.
Q from Ashok: How can I find a load balancer?
There are many HW and SW load balancers, some of them are open source, others are commercial products. If you are using Linux, I would suggest to check the functionality in your Linux distribution first.
Q from Alessandro: What about the filesystem for MyISAM in a small company? Better ReiserFS or EXT3?
As Anders said, it's a matter of taste. XFS is a good alternative too. Ext3 is stable, but sometimes slow and a bit outdated. Many like Reiser, but it is sometimes difficult to manage. XFS often gives you good performance and reliability out of the box.
Q from Florian: MySQL 5.1 == Falcon?
Falcon is available for download today. The current version refers to 5.2 and it's an alpha version. We may change plans, but so far Falcon is related to version 5.2
Q from Henk: Will InnoDB ultimately be replaced by Falcon?
No, it will not. Falcon is an alternative to InnoDB and willl be especially excellent for online applications, but we will continue to support InnoDB for many years ahead.
Q from Paul: Will Falcon include the spatial features of MyIsam?
Anders has clearly replied to this question. Falcon will initially it will support spatial datatypes, but not R.tree indexes. GIS datatypes are available out of MyISAM since 5.0, although R-tree indexes are limited to MyISAM.
Q from Owen: what is the roadmap for myISAM 2.0
We do not have any roadmap for MyISAM 2.0 at the moment.
Q from Owen: Will not myISAM 2.0 support foreign keys?
We are considering to provide FK at an upper layer - ie not in the storage engine - hence every storage engine will support FKs.
Q from Danilo: What's the best storage engine to manage very big data?
We can't simplify too much, but generally speaking we may say that InnoDB and MyISAM are the usual suspects. InnoDB takes little time to recover in case of failure (referring to our HA webinar), but it usually needs more space than MyISAM to store the same data.
Performance-wise can handle high concurrency better, whilst MyISAM might perform better with intensive reads. These of course are only generic guidelines.
At the UC in April we will see new storage engines that might be interesting as well.
Q from Colin: The majority of data warehouses are designed using the Kimball methodology using a star schema. This relies on foreign keys. How is MyISAM ideal for this?
Well, I have spent the last 12 years of my working life on Data Warehouse solutions and I would say exactly the opposite. In 100% of the DW that I have designed there were no FKs. two valid reasons: performance and ETL issues. I tend to consider foreign keys essential in an OLTP environment, specifically when we have different applications relying on the same database or schema. But in a DW, generally speaking, we can control the loading process. Since we are loading a massive amount of data at a time, we need fast import and write - and MyISAM is perfect for that. FKs would slow everything down, checking that the data we are loading is consistent. Also, applying FKs to a DW environment means that we will be forced to load data in a specific order, otherwise we will not find references for new records to load. Hence, no FKs for me in a DW. Integrity must be handled with a good ETL process.
Q from Efrat: If I want to use foreign keys, Do I have other alternative besided InnoDB?
As Anders said, right now, we do not have alternatives, but we are working on that.
A stop-gap-solution is to use triggers.
Q from Andrew: What is the expected time frame for this FK support in the sql layer of all storage engines?
We are considering to implement FK support in 5.2 or future versions - hence, not in 5.1.
Q from Tero: Can you back up data in a single instance while reading and writing?
We will cover the backup strategies in one of the next sessions. In the meantime, Anders has answered to this question: there are some different ways for doing this. A logical backup can do the job, specifically if we use InnoDB - since we can handle concurrency and isolation. Another alternative is the use of storage engine specific backup solutions, such as the one that is included with NDB or inno backup.
Q from Andrea: Which is the Best Management and Monitoring tool to work with Mysql?
The Monitoring and Advisory Service included in MySQL Enterprise acts as a "Virtual DBA Assistant" and interface to popular monitoring tools such as HP Open View, Tivoli etc via SNMP traps
Q from Alessandro: What SMP stands for?
Symetric Multi Processing: The technology that supports multiple CPU cores to work together.
Q from Henk: Is there a list available of business applications that each need a specific type of of all the types of scaling that Ivan speaks of?
Please contact your sales rep, who will get you in contact with an SE that can help you out.
On Connectors and Development Environments
Q from Fabio: what is the VB connector?
It is a .NET native provider - http://dev.mysql.com/downloads/connector/net/5.0.html
There is also a Visual Studio plugin available for development - http://dev.mysql.com/downloads/visual-studio-plugin/index.html
Q from Wenter: How about ASP?
.NET is the connector to use here
Q from Alessandro: Is it possibile to use InnoDB features (transactions) inside VB applications (e.g. inside an Access project)? Is there any documentation around?
Yes. There are some Access - MySQL migration docs, and there is also a forum on forums.mysql.com on Access
Q from Fabio: MySQL Visual Studio don't work , it creates many problem to the IDE?
Unfortunately, the Visual Studio Plugin is still a beta version - We would encourage you to post bugs on our bug systems
Q from Henry: Any plans for a connector for Delphi?
I am not a big expert of Delphi, but I think that a connector should be available from Borland to access MySQL through the new DBX4 Layer. The layer is compatible with MySQL 5.0.
Q from Emil-Valentin: How do you have an easy GUI to administer the mysql database, as much as easy as MSAccess?
MySQL administrator is one way, MySQL Enterprise customers have access to MySQL Monitoring and Advisory Service tool. phpMyAdmin is popular in Linux / PHP environemnts. SQLYog is also common. Quest has Toad for MySQL which is also popular in some circles.
MySQL Enterprise includes GUI tools and the Monitoring and Advisory service to help you easily administer all your servers. And you can also use Access as a front end to MySQL.
Check sourceforge.net or forge.mysql.com for tools, there is a lot there. Or ask the forums at forums.mysql.com
Q from Eric: Is there a good php based load balancing script available to select the least loaded mysql (slave) server?
Not as far as I am aware. In many cases, the balancing on multiple web/application server is good enough to provide a consequent balancing on multiple slave servers - associating a round robin mechanism or a different preferred connection for each web server.
Q from Constantinos: Do you know if there is a "high available connector" for perl or php?
I am not aware of any. But for example I have found this comment quite interesting, especially in conjunction with Replication: http://uk3.php.net/function.mysql-pconnect
Q from Andrew: is there an equivalent of esqlc?
Not at the moment. There are some scripting solutions, mainly awk based. You may check this: http://dev.mysql.com/tech-resources/articles/precompiler-for-embedded-sql.html
On Replication
Q from Melvin: Is it possible to have multiple masters and a single slave?
This is not possible, strictly speaking, using a classic configuration. We have planned to have multiple sources for a slave sometimes in the future versions. At the moment, we may use the Federated Storage Engine, as we will cover in the next session.
Q from Jonas and Danilo: What about multi-master replication?
We will cover it in the next session.
Q from Jonas: Is circular replicaiton a good HA option?
Yes, this is often good enough, but not for all the applications. The main drawbacks are conflict detection and the asynchronous nature of replication. But it is definitely an option, and an attractive one.
Q from Danilo: Is there an automatic procedure to swicth a slave to master when the master die?
No, but there is pretty good documentation on the linux-ha.com website on how to do this. Usually, you don't do this though, you set up both systems as being masters AND slave, but only one is active master.
Q from Efrat: How can I make sure that the slave database is identical to the master database?
The slave keeps track of the position on the binary log of the master database and it can provide information on a delay in updating the DB, although this is acceptable only if there are no failures.
Q from Efrat: Is there a mysql command that compares the databases in a master and in the replicated slave?
Not at the moment. The Monitoring and Advisory Service can provide some extra information to prevent delays and problems though.
Q from Juan: How many mysql server clients supports a mysql master host?
This is usually limited by OS resources more than MySQL itself, although the MySQL config may limit this.
Q from Lorenzo: How many server can I use in circular-replicaton (master-master)? Is there a max value?
In theory no, but it will get complex to maintain after a while.
Q from Danilo: what about auto_increment fields... will be a sequence filed like oracle? I am asking this because this is the principal problem of multi master replication.
I agree. As I know, there is nothing planned right now.
On MySQL Cluster
Q from Matthew: When are we likely to see disk based indexing for ndb?
Disk based indexing is planned in one of the future releases, but we can't say when we will implement it. During the webinar, Anders pointed out that he does not see this as an important thing. I tend to agree with Anders, at least considering the current status of the storage engine. At the moment, ndb can perform an unbeatable job (in terms of HA and performance) on small transactions and simple queries and we should not consider it as a full replacement for the whole database, in general. The future versions of ndb will probably be more and more general purpose and at some point a full disk based ndb will be valuable. Please take this as my personal opinion.
Q from Malcolm: Is their any difference between MysQL Cluster and the telecoms version?
As Bertrand said, MySQL Cluster Carrier Grade is a specific version for telecom, developed closely with major equipment manufacturers. During the presentation I have highlighted some differences - such as the availability of more data nodes and so on. We will cover MySQL Cluster and MySQL Cluster Carrier Grade Edition in one of the future sessions.
Q from Fabio: Any plan for MySQL Cluster for Windows?
We are considering it sometimes in the future, but no plans have been made so far.
Q from Andrea: What happen if the Management Console crash in a Mysql Cluster environment?
As Anders said, it's not a big deal. The mgm is not necessary to keep the cluster running in normal cases, and you can have more than one mgm server.
Q from Owen: Is it difficult to define memory requirements for MySQL Cluster?
MySQL Cluster configuration is the most important step when you adopt this technology. We have seen several do-it-yourself configurations, running perfectly. But Cluster configuration is not straightforward and we always recommend to get some help from our Professional Services team.
Q from Sebastian: How to commit database structure changes on mysql in mysql cluster, is any tool for this?
I am not sure I understand the question - Sebastian, if you read this blog, feel free to add more details. If you refer to the possibility to change (ALTER) a table structure, this is possible and once you flush the table (FLUSH TABLE) the change is available to all the SQL nodes.
Q from Florian: Can I put an IP load balancer in front of the Interface/SQL layer in Cluster, and address the whole cluster using 1 public IP address?
Yes, this is fine. All SQL-nodes see the same data and are always in read/write mode.
Q from Xabier: how many transactions can stand a clustered mysql db?
It depends on the configuration and the application. Using the low-level NDB API, you could sustain 100's of thousands transactions per second.
Q from Juan: Is the ndb engine avaible for QNX?
Yes, there is a version for QNX 6.2.1
Q from Xabier: does a clustered mysql db require horizonal or vertical partitioning the database?
MySQL Cluster partitions the database automatically. We will cover Cluster in one of the next sessions.
Q from Andrea: When will be available the Mysql Cluster Carrier Grade Edition?
MySQL Cluster Carrier Grade Edition is available now, you can download a free whitepaper on it at: http://www.mysql.com/why-mysql/white-papers/
Q from Alessandro: Is carrier grade avalaible for download?
As Bertrand said, please contact us at http://www.mysql.com/company/contact/ if you are interested in MySQL Cluster Carrier Grade for telecom customers
On Shared Disk Cluster
Q from Efrat: Can I implement a Shared Disk Cluster using the Community Server?
Although we recommend to use MySQL Enterprise, one can use a Community Edition to implement a shared disk cluster. The recommendation to use MySQL Enterprise can be easily explained: since we are looking for High Availability and reliability, it does not make much sense to risk unplanned downtime caused by faults that can be solved using hot patches or escalating bugs. Also, the use of the Monitoring and Advisory Service could help in preventing such faults - we will cover it in one of the next sessions.
Q from Doug: Do shared disk cluster solutions require proprietary OS's? I see no community/free Linux editions stated on the slides?
Normal OS's are fine. Linux supports things like Sistina (owned by RedHat), but not even that is necessary. You can run ext3 on a SAN.
You just need drivers for the SAN. The SAN looks like any disk to the OS after those are installed, and any type of partition can be installed, but an LVM is nearly always used (but this is part of any Linux distro anyway these days).
Q from Tero: what does "External Locking" mean when we talk about Shared Disk Cluster?
As Anders perfectly explained, it is when the OS locks files, even across nodes in a distributed filesystem, like lockd when using NFS.
On DRBD
Q from Fabio: what is DRBD?
DRBD stands for Distributed replicated block device - more info here: http://www.drbd.org/
Q from Fabio: This DRBD is only for Linux ?
Yes it is. It's a Linux kernel module with userspace management tools. It doesn't support other Unices as of today.
Q from Efrat: In DRBD, if the storage of the master is damaged, doesn't it mean that the slave will be damaged too?
DRBD has some fault-detection mechanisms built-in. So disk errors aren't that much of an issue, although errors in the layers above DRBD are of course undetectable by DRBD itself.
Q from Jonas: What is better, MySQL Replication or DRBD?
Well, we can't say that something is better than something else, because it depends on the solution you are looking for. For some applications and specific needs, MySQL Replication is better; for others DRBD is the solution. As Anders pointed out, MySQL Replication is asynchronous, whilst DRBD is synchronous. Also, DRBD replicates anything that can be written on a file system, whilst MySQL Replication is only related to DB write operations. Hence, there is not a straight answer.
More Questions
Q from Efrat: Will you have a conference in Europe too?
We will have customer conferences as last year in Europe, most likely in London, Munich and Paris in Q4
Q from Emil-Valentin: I would like to download trustiness and clear howto's for web enabling and enabling openoffice as frontend for administering. May you help me?
It's not easy to find these information. Our knowledge base and the consultative support provided by MySQL Enterprise Platinum might help, but we would need to know a bit more about your requirements.
Q from Ashok: How can I find a load balancer?
There are many HW and SW load balancers, some of them are open source, others are commercial products. If you are using Linux, I would suggest to check the functionality in your Linux distribution first.
Tuesday 27 March 2007
27th March - The Series Begins
Welcome everybody!
Today we have presented the first webinar of the series.
Let me remind you the dates for the all the sessions:
-ivan
Today we have presented the first webinar of the series.
Let me remind you the dates for the all the sessions:
- 27th March: Part 1 - High Availability and Scalability Architectures
- 19th April: Part 2 - Advanced Scalability Solutions
- 2nd May: Part 3 - MySQL Enterprise To Control Mission Critical Online Services
- 23rd May: Part 4 - 99.999% High Availability solutions
- 13th June: Part 5 - MySQL Enterprise performance and benchmarking
- 27th June: Part 6 - Advanced HA solutions
-ivan
Subscribe to:
Posts (Atom)
