[rt-users] Migrating from Postgres to MySQL

[Kenneth Marshall]

Kage,

The main advantage is gained by avoiding I/O through the virtual
disk. The layout of the virtual disk tends to turn most I/O into
random I/O, even I/O that starts as sequential. The factor of
10 performance difference between random/sequential I/O causes
the majority of the performance problem. I have not had personal
experience with using an NFS mount point to run a database so I
cannot really comment on that. Good luck with your evaluation.

Cheers,
Ken

On Tue, Jul 28, 2009 at 04:12:55PM -0400, Kage wrote:
> (didn't do a reply-all, sorry if you get this twice, Ken)
> 
> What, if any, performance gains exist by using an NFS mountpoint for
> the Postgres database within a virtual machine? ?In this case, I/O is
> not directly placed on the virtual disk (unless the actual "act" of
> I/O itself, regardless of what is being acted upon (virtual disk or
> not), is also an issue). ?Could this potentially yield any performance
> increase?
> 
> Also, no, I have not tried MySQL yet with our RT setup. ?I'd like to
> move our database to MySQL to do some testing with that and gather
> realistic metrics of "here's our data in Postgres vs. MySQL".
> 
> On Tue, Jul 28, 2009 at 3:51 PM, Kenneth Marshall<ktm at rice.edu> wrote:
> > On Tue, Jul 28, 2009 at 03:35:12PM -0400, Kage wrote:
> >> Well, basically, Postgres is seemingly a bucket of fail specifically
> >> for the RT system I maintain. ?We run RT/Postgres on a Ubuntu Hardy
> >> LTS virtual machine running 2 virtual CPUs and the KVM maximum of 2GB
> >> RAM, but Postgres ends up using so much in resources that it actually
> >> kills the entire KVM system on the virtual host. ?I know tuning of
> >> Postgres can fix this, but we've experienced issues with Postgres on
> >> many other virtual machines, leading us to believe that it may simply
> >> be an issue with Postgres not getting along well with KVM (which is a
> >> somewhat known issue). ?Ideally, I want to migrate my RT database from
> >> Postgres to MySQL, and preserve ALL data (tickets, attachments, links,
> >> everything). ?Is there an "guaranteed to work" method to do this that
> >> anyone knows of? ?Thanks!
> >>
> >> Specs:
> >> Postgres: 8.3
> >> RT: 3.6.5
> >> MySQL candidate on Hardy: 5.0.51a-3ubuntu5.4
> >>
> >> Any help would be greatly appreciated. ?Thanks!
> >>
> >> --
> >> ~ Kage
> >> http://vitund.com
> >> http://hackthissite.org
> >
> > Hi Kage,
> >
> > I have had trouble with VMs with any I/O bound system. Have you
> > tested your system with MySQL? You may just be swapping one I/O
> > problem for another. That being said, for anything but the most
> > trivial of DBs, tuning your backend DB be it PostgreSQL, MySQL,
> > Oracle,... is critical for getting good performance.
> >
> > Another data point, I have had a simple repeated file read of a
> > DNS zone file for an XFER tank a VM. VMs work well for CPU intensive
> > tasks with small amount of I/O. For any larger amount of I/O, using
> > real storage is a must. Good luck with whichever backend you choose.
> > A big plus for both the Oracle and PostgreSQL backend is the
> > availability of full-text indexing which allows content and attachment
> > searches to use an index and not take your DB with a full table scan.
> >
> > Regards,
> > Ken
> 
> -- 
> ~ Kage
> http://vitund.com
> http://hackthissite.org
>