Dear All, thank you very much for your prompt responses!
Basically, I am looking for a smart model to run the system in a virtualized environment. The hypervisor is KVM on Centos 7. I use the standard caching (writeback) for the guest disk and no cache for nfs. The nfs connection is over 10Gb/s Ethernet which is just used for connecting two hypervisors to one NAS. The utilization of the overall system is rather low - far away from congestion. Space of the mailpiler DB is not a real issue. In my case, it consumes ca. 2 GB with automysql backup files. What I like to do, however, is to use guest machines with small disks (i. e. less than 16 GB) which permit a swift nightly live backup. Under those circumstances, separating the mysql files sounded (and sounds) like a good idea to keep the system scalable. In case I needed to revert the guest machine to a state of a few days ago (this does not happen often), the current database files including its backups would still be there. This does seem to work well with clean shutdowns, but not with unfriendly shutdowns. My practical experience is, that unfriendly shutdowns are no issue if, (a) the database files are on the virtual guest disk itself (but at the cost of not getting the advantages stated above) or (b) using MyISAM, which I noted after converting from InnoDB to MyISAM on my virtualmin web server (with the bad feeling that InnoDB is the storage engine in the focus of future development). To resolve the issue with mailpiler, I do consider the following options: 1. Change caching model. 2. Move to iSCSI. 3. Move away from nfs by providing a second virtual disk to the guest, containing just the database files. 4. Convert to MyISAM storage engine. My feeling is to try 3) first and 4) second. Do you have recommendations for me? In addition, I am considering to move the mailpiler DB to a separate server along with my other databases. Of course this does only centralize the issue so the basic problem still remains. Is it as simple as changing the 'localhost' parameter in config.php and making the database available at the new location? Regards, Michael -----Ursprüngliche Nachricht----- Von: Janos SUTO [mailto:[email protected]] Gesendet: Freitag, 15. Mai 2015 08:54 An: Piler User Betreff: Re: InnoDB Question Hello Michael, On 2015-05-15 00:44, Prof. Dr. Michael Schefczyk wrote: > > As the database can grow quite large over time, I am running mailpiler > 1.1.1 on a virtual machine which has /var/lib/mysql attached via nfs > from a NAS device. Once in a while after an unfriendly shutdown, the > database fails. The only practical solution I could find is to delete > the InnoDB files, start mysql, reimport the database from the last > backup and run a complete reindex. If possible, I would like the > database to survive an unfriendly shutdown without manual > intervention. My impression is that the old MyISAM storage engine > (while not transactional) is much more robust than InnoDB. Would it be > possible to change the engine in my.conf and in the tables back to > MyISAM without running into problems? You don't have to reindex since it's for healing (or regenerating) the sphinx database (which is a completely different story), not for fixing a mysql related issue. Anyway you are free to pick any storage engine of your choice, and though each of them has its own pros and cons, I believe that InnoDB (and its derivatives) is a better choice than MyISAM. If you chose myisam, then you have to convert the tables. Probably a good idea to stop piler while you do it. Btw. if you have little disk space on the archiving host, then consider using a remote mysql server. Janos
smime.p7s
Description: S/MIME Cryptographic Signature
