Informix Mirroring?

IDS 12.10.FC12 Solaris 10 1/13 Is it possible to generalize and say that Informix mirroring is better than using 3rd party LVM mirroring? I raise an issue in this related thread: Solaris Volume Manager I presume that using the builtin Informix mirroring would avoid any such problems. Is Informix mirroring regarded as "best practice"? I could be totally mistaken, but I seem to recall some mention by the instructor in an Informix class of long, long (>20 years) ago that her opinion was that it a 3rd party LVM was probably a better way to mirror chunks than the Informix option. I do not recall what her reasoning was. How does Informix mirroring compare to other LVMs, in terms of performance and safety? Thank you. DG ------------------------------ David Grove ------------------------------ #Informix

This question does not have a single straightforward answer. And I do get it regularly in the training classes. Informix mirroring is done at the Informix engine level so at a higher level. When you have mirroring at the Informix level, every write operation (INSERT, UPDATE, DELETE) requires 2 accesses plus updating the related indexes if the indexes are in mirrored dbspaces also. So it slows down your operations. That is why we advise people to disable mirroring if you're doing large loads of data; meaning it is better to disable mirroring (not stopping miroring altogether) temporarily, perform your major load or major batch that includes modifications et renable mirroring again after wards and let the Informix perform the sync of the mirror chunks in the background. This is because mirroring at the engine level slows down your operations; for a single row INSERT, it is trivial (you will not really see the difference). However in you do a large SELECT, the engine can read from both mirror chunks in parallel and hence accelerate the reads. With Informix mirroring, you can choose which dbspace to mirror. You do not have to mirror all of the dbspaces. However, all of the chunks that belong to a single dbspace have to be either mirrored or not. Some people mirror only rootdbs and the logs dbspace (critical dbspaces) if you do not have the space needed. If the rootdbs is not mirrored and the its chunks go down, the engine goes down. It it is mirrored, the engine continues with the mirror chunks. However if a mirrored dbspace that contains data and is not a critical dbspace, the dbspace if not mirrored goes down but the engine continues to function. Backups only use one of the mirrored chunks during saves; the system does not backup both mirrored chunks. It is very easy to setup mirroring at the begining of your instance setup or later on if desired. You do not have to request a system from the system's administrator if he/she is not available. However, you need to have the necessary disk space to do it. You need twice as much disk space if mirroring is setup. From the performance point of view, mirroring at the system level (it is done at a much lower level) is faster that mirroring at the Informix level. Here again, what type of mirroring is being put in place. I do not have experience with Solaris Volume Manager We can go on and on in the comparison . So it depends on what you are looking for is setting up mirroring: speed, security, ease of setup, manipulation. Ease of setup, reliability, security; Informix mirroring is fine and works great and has existed for a very long time. Speed: I would say that mirroring at a lower level (seen at the system level), I would go with system mirroring. Also it depends on the type of RAID used. There is no single simple answer. This subject could not be resumed in just a simple answer. -- Khaled Bentebal Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr

David: In testing long ago, I found that Informix mirroring was a bit faster that LVM and other software based mirroring but slower that firmware and hardware based mirroring. Informix mirrors are reliable and yes it would get around the problem you mentioned in your other post. Art

Thank you, Khaled, for your comprehensive and informative response. I think the essence of your wonderful explanation (Informix engine level vs. O/S level) was also the point of that Informix instructor so long ago. Thank you for refreshing my memory. Do you have any thoughts on the (rough) degree of performance that might be expected using system level mirroring (software) mirroring, vs. Informix engine level mirroring. I'm not certain how I could effectively test this, as it may be that the difference might be most significant and apparent when the system is loaded, and I can't do any testing on our Production system. Thank you, again. DG ------------------------------ David Grove ------------------------------

Art: Thank you. I always appreciate your thoughts. Just to make sure I am understanding you, you are saying that, in your experience (so not intending to put words in your mouth and assert the general case), you have found that using Informix mirroring didn't "cost" you, relative to software LVM. (In my situation that would be Solaris Volume Manager on a SPARC Solaris system. I declined to use the hardware RAID controllers available in our system because I could not split mirrors over two controllers, therefore, I could make only two mirrored pairs instead of three.) Actually, if there really is unlikely to be a performance penalty in using Informix mirroring, I think I would be inclined to switch over to it. This is because, I would gain an extra slice on each drive. (And that is because I make each slice a single chunk [except when "forced" to use offsets for additional chunks from a single slice], and I devote slice 7 to the SVM metadb... which means the mere fact of using SVM costs me a chunk for each mirrored pair.) Bottom line for me... If Informix mirroring was about the same as SVM, I'd use it. That was your experience, right? Thank you. DG Edit: P.S. Rereading your post, I see that you indicated that Informix mirroring was actually a little faster than the software LVM you used, right? Do you recall the LVM and O/S? ------------------------------ David Grove ------------------------------

David, I have worked with Informix mirroring and hardware mirroring and LVM mirroring and I think the best answer is it all depends. When I used to teach Informix classes I seem to remember in the instructor notes it said hardware mirroring was best, then software and the Informix. But I have seen some really bad hardware mirroring, mainly in cheaper disk I/O cards and on 3rd party hardware for suns. Most good (expensive) SANs do good mirroring and I have been very happy with AIX and Vertas Logical Volumn mirroring. Reads: One thing Informix mirroring does well is it splits the reads from the primary and secondary so your reads will be twice as fast. If you get any hardware or software for mirroring make sure it will do this. I have a great RAID 10 server that I really like except it only reads from the primary disks. This is a requirement for any kind of mirror. Writes: The write penalty for Informix is so small I almost cannot measure it, a write is not committed until both sides of the mirror respond with success so it will have to wait for the slower write. So performance is limited to the slowest part of the mirror. And I have been very pleased with the results of Informix mirroring. One of the best performance Disk systems was I have seen was a SAN with RAID 10 (mirroring) and the Informix mirroring on top of that... Make sure you are mirroring to separate disks. Informix mirroring is a simple way to double your read performance and add a safety net to your database. Regards - Lester #Informix

David: Informix mirroring is indeed a bit faster than software mirrors partly because, as Lester points out, some mirroring software does not read from both sides in parallel and partly because Informix knows more about the content of the mirror so the parallel reads are more intelligent. But keep Lester's caveats in mind! Art ------------------------------ Art Kagel ------------------------------

Thank you, Lester, for your informative comments. "...I used to teach Informix classes I seem to remember in the instructor notes it said hardware mirroring was best, then software and the Informix...." That was the one-sentence summary of what I thought I recalled from those long-ago classes. "Reads: One thing Informix mirroring does well is it splits the reads from the primary and secondary so your reads will be twice as fast..." Would this reading from both sides of the mirror happen, irrespective of whether PDQ is turned on? (Provided sufficient VPs are available) "Writes: The write penalty for Informix is so small I almost cannot measure it, a write is not committed until both sides of the mirror respond with success so it will have to wait for the slower write..." Great. And, since I am using SSDs for both sides of the mirror, write delays should be even less significant . "...Informix mirroring is a simple way to double your read performance and add a safety net to your database..." Well, there it is. Given comments from you, Khaled, and Art, I think the prudent way to go is to use Informix mirroring (something I have always passed over, due to that lingering memory from Informix class many years ago, and that I never re-evaluated in all these years). Thank you all for your help. Regards, David Grove ------------------------------ David Grove ------------------------------

David, It will read from both sides of the mirror without PDQ. I would agree with your choice here. Regards - Lester #Informix

Have been using Informix mirroring on many mission critical sites/instances for many years with great results. Hal Maner M Systems International, Inc.

当使用PDQ时，IDS会从一个镜像读取还是从两个镜像读取 ？ ------------------------------ 崔志伟 GBASE ------------- ----------------- ----------------- ---------------- ---------- 原始消息： 发送时间： 2020 年 6 月 1 日星期一下午 02:43 发件人：Lester Knutsen 主题：Informix 镜像？ 大卫， 连环在没有 PDQ 的情况下从镜子的读取。 我 同意 你在这里的选择翻译。 - 莱斯特 #Informix

Informix will read different pages from both sides of the mirror regardless of whether PDQ is enabled or not. Art

3Q ------------------------------ ZhiWei Cui GBASE ShiJiaZhuang ------------------------------

Hi, My thoughts... Hardware mirroring has the advantage that only 1 request is sent to the storage hence traffic to the array is less i.e. less bandwidth used across a SAN and less 'slots' at once used on any SAN switches. The disadvantage is that I have not seen hardware mirroring which supports cost effective mirroring between storage arrays. e.g. mirroring across 2 data centres I have seen Hitachi TrueCopy used for this during a cutover for storage migration but it was only enabled temporarily and I believe has an extra cost associated with it. (I know Hitachi TrueCopy is software based but I am counting it here as it sits on the storage not on the hosts). For software mirroring most software solutions these days would be smart enough to read from either side of a mirror hence Informix mirroring has no advantages there. You could also have each side of the mirror in a different data centre. The other big advantage is that 1 team then has visability of all storage redundancy and storage redundancy is handled the same way across the whole organisation. i.e. for all storage including other database products(!) or even filesystem storage. Also any failures in configuring redundancy and there is 1 team at fault! With Informix mirroring the DBA has to know about storage placement and shared components between different parts of storage which mean mistakes are easier to make and less visible as the operating system/storage team would not understand how redundancy is configured at the informix level. Communcaiton is required between DBA and Operating system/storage teams to ensure both sides of a mirror do not have shared components. In the case of a storage failure with software mirroring it is seamless at the database level and recovery would only require storage and operating system teams. With Informix mirroring after a storage failure DBA's have to take action as well hence recovery is longer. Also for seamless storage migration the operating system/storage team can configure a 3rd/4th mirror on the new storage,more than 2 mirrors are not possible with Informix mirroring making migrations more complex. I prefer either software mirrroing across data centres (seamless to me as a DBA!) or use locally attached fast storage with Informix replication (HDR/RSS/ER) i.e. no dependency on storage redundancy! David. ------------------------------ David Williams ------------------------------

<deleted> #Informix

Hi David, I am interested to know if you implemented the mirroring? If so, how does it work? performance and other things you might notice? The reason for my question is that I had an incident where parts of the chunk file system became corrupt (Veritas and Solaris virtual machines involved), which caused the entire physical machine to go down. To solve it I mount a new file system and ran a restore. My thinking is if I had my chunks mirrored using Informix to a different disk filesystem, I could have solved the problem faster or wouldn't have had that problem. Will be migrating to Linux in the near future, I would appreciate others experiences using informix chunk mirroring . Many Thanks Regards //Layth ------------------------------ Layth Yousif ------------------------------

Layth: The general consensus is that hardware mirroring is fastest followed by firmware mirroring followed by OS level mirroring with Informix mirroring bringing up the rear. The main advantages of using Informix mirroring are: The engine knows about it and will read from both sides of the mirror in parallel to increase IO read performance. The mirroring is independent of the OS and hardware so if something screws up at a lower level corrupting one side of the mirror, as it did in your case, the mirrors will be OK. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Layth, We did start using Informix mirroring, with no noticeable difference in performance. However, our system is not I/O bound, so I don't think disk I/O is a main factor in our performance. (We have a huge buffer cache, so most of the data is cached at all times; plus, all our Informix chunks are SSDs; plus we use raw devices for all out chunks.) My main interest in using Informix mirroring (which is smart and uses mirrors astutely) was the avoidance of "write-on-write" errors. Here is a brief excerpt from Informix documentation about that: Excerpt from SVM man pages: Write-On-Write Problem When mirroring data in Solaris Volume Manager, transfers from memory to the disks do not all occur at exactly the same time for all sides of the mirror. If the contents of buffers are changed while the data is in-flight to the disk (called write-on-write), then different data can end up being stored on each side of a mirror. We had never experienced any problem with write-on-write errors, but, after I read the documentation, and after not being able to get any assurance from either IBM or Oracle (we were using the Solaris LVM for mirroring) that "write-on-write" is NOT a problem in Informix/Solaris/SPARC environment, we changed to Informix mirroring. No perceivable difference, and no problems. (I never made any quantitative measurements, though.) Regards, DG ------------------------------ David Grove ------------------------------

Such write-on-write problem shouldn't be a thing in Informix mirroring since writing of a buffer to both sides of the mirror is a single operation, i.e. occurs 'simultaneously', with the buffer locked for its duration. ------------------------------ Andreas Legner ------------------------------

Thank you, Andreas, for your comment. I speculated that that might be the case, but hey, I'm not an Informix developer, so what do I know? Several years ago, I opened an Informix tech support case to try to get a definitive answer. The engineers tried to be helpful, and I appreciated their efforts, but, I mostly got answers in the subjunctive (if our program doesn't write to the buffer immediately after receiving the disk-write completion flag, then it should be OK, etc.) No one really could say for sure. They (Informix tech support) suggested that if I really wanted to be guaranteed to not have any write-on-write problems, we should use RAID 6 or ZFS. (But, they did caution about the performance burden of ZFS.) Anyway, to be safe, we switched from disk mirroring via Solaris LVM to the built-in Informix mirroring. I continue to speculate that that wasn't really necessary, but, I'm always paranoid when dealing with a production database. DG ------------------------------ David Grove ------------------------------

Hello, Thank you all for your time and answering my questions and allaying my concerns, so would you recommend mirroring all dbspace ? or just rootdbs,logdbs,plogdbs ? If I decide to mirror all dbspaces, it is posible to do it on the fly ? Thanks in Advance //Layth ------------------------------ Layth Yousif ------------------------------

The best is to mirror everything of course. If you want to mirror some dbspaces, you should at least mirror the main dbspaces that include systems information : rootdbs, logsdbs, physdbs. If any of these 3 dbspaces have problems, without mirroring, the instance goes down; with mirroring you are saved. For the other dbspaces, if any of of them goes down, the engine continues to work but the data from it is not accessible. The other dbspaces continue to be accessible. You can restore down dbspaces online. Khaled Bentebal de mon portable

In our application, we mirrored everything. Just on the general idea that it's a good idea, and for a production database, we want all the reliability we can get. If you haven't previously used mirroring, you will have to change the onconfig MIRROR parameter, which will require a bounce. But, you can add the mirror secondaries on the fly. DG

Hello, I just had time to create dbspace/chunk mirror and run some test and it looks good I will try to implement it on test environment to evaluated. Many thanks David and all for your time and advices Best Regards Layth ------------------------------ Layth Yousif ------------------------------