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.