[rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. It looks like I have two options: * Migrate our databases from MySQL 5.5.x to Postgres SQL to get the full text index functionality *Create a new MySQL Server that has the Sphinx add-on running restore my databases into that. Has anyone had to do this before? Suggestions? Warnings? Stephen J. Cena Systems Administrator - MIS/IT Dept Quality Vision International 850 Hudson Ave Rochester,NY 14620 Phone: 585-544-0450 x300 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Please report email problems to: postmas...@qvii.com QVII MIS/IT Dept - We do what we must because we can. Thank you for helping us help you help us all. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise? Mark -- My blog: http://mark.goodge.co.uk -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
On Thu, Mar 13, 2014 at 02:06:40PM +, Mark Goodge wrote: On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise? It supported InnoDB Full Text indexing in 5.0? As was posted quite recently, here's a decent recap of the technical hurdles in using native 5.6 InnoDB indexing as it finally reaches maturity. http://www.gossamer-threads.com/lists/rt/users/114057#114057 We've come up with some possible workarounds for this, but I'm not sure when we'll have engineering resources to pursue them. Stephen - if you'd like to remain on a packaged mysql platform, several people have reported success using mariadb which ships with a sphinx plugin (there's some small shenanigans because packaged sphinx ships with bigids requiring bigint ids in RT, but that's documented in the list archives). I also know that users have built 12.04 mysql/sphinx debs (we're still running on an older LTS version of Ubuntu). -kevin pgpamusvp0KFk.pgp Description: PGP signature -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
I personally went the Sphinx route because I already have so much MySQL running (and familiarity with) here. I documented most of it here: http://parishnetworks.blogspot.com/2013/10/using-mysql-and-sphinx-for-rt-full-text.html - Brent -Original Message- From: rt-users-boun...@lists.bestpractical.com [mailto:rt-users-boun...@lists.bestpractical.com] On Behalf Of Mark Goodge Sent: Thursday, March 13, 2014 10:07 AM To: rt-users@lists.bestpractical.com Subject: Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx? On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise? Mark -- My blog: http://mark.goodge.co.uk -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
On 13/03/2014 14:32, Kevin Falcone wrote: On Thu, Mar 13, 2014 at 02:06:40PM +, Mark Goodge wrote: On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise? It supported InnoDB Full Text indexing in 5.0? No; you need to use MyISAM until 5.6. But that should not be an issue here; I know that RT uses InnoDB by default but there should be no reason why the table you want to search can't be converted to MyISAM. Mark -- My blog: http://mark.goodge.co.uk -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
On Thu, Mar 13, 2014 at 02:47:10PM +, Mark Goodge wrote: On 13/03/2014 14:32, Kevin Falcone wrote: On Thu, Mar 13, 2014 at 02:06:40PM +, Mark Goodge wrote: On 13/03/2014 14:00, Cena, Stephen (ext. 300) wrote: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. MySQL has supported fulltext indexes since version 5.0. What makes you think otherwise? It supported InnoDB Full Text indexing in 5.0? No; you need to use MyISAM until 5.6. But that should not be an issue here; I know that RT uses InnoDB by default but there should be no reason why the table you want to search can't be converted to MyISAM. Mark Hi Mark, I do not use MySQL here, but the table that is to be indexed can be very large and I certainly would not want to wait for a myisamchk to complete after a crash. The sphinx option is the only viable one for use with mysql. Moving to PostgreSQL would work but as suggested by others, if your expertise is with MySQL, stick with MySQL. Regards, Ken -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
On Thu, 2014-03-13 at 14:47 +, Mark Goodge wrote: I know that RT uses InnoDB by default but there should be no reason why the table you want to search can't be converted to MyISAM. There's a very straightforward reason why RT uses InnoDB: database-level transactions. Converting the Attachments table to MyISAM would break a notable number of internals. - Alex -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
Cena, Stephen (ext. 300) ha scritto: We currently run two RT 4.0.19 systems on Ubuntu 12.04LTS and MySQL 5.5.35. Management is now asking me to enable full text searching on the sites so we can search inside the body of the tickets. From what I've read, our current configuration won't support it due to MySQL 5.5.x not supporting it natively. It looks like I have two options: * Migrate our databases from MySQL 5.5.x to Postgres SQL to get the full text index functionality * Create a new MySQL Server that has the Sphinx add-on running restore my databases into that. Has anyone had to do this before? Suggestions? Warnings? Stephen, we initially (RT 3.8.x) wrote an extension to use Sphinx without the need to integrate it into mysql. You can find it on Wikia, but I would not encourage you to use it (and it will not work in newer versions of RT). When migrating to RT 4.0.x we tried the migration to Postgres. The migration was successful, but we were disappointed with the performance of postgres when doing full-text searches. I guess we were too used to the stellar performance of Sphinx. Finally we settled with mysql+sphinxSE, and we are very happy with it. Bye Cris -- Cristiano Guadagnino Servizio Data Administration ___ Bankadati Servizi Informatici Soc.Cons.P.A. Gruppo bancario Credito Valtellinese Via Trento, 22 - 23100 SONDRIO tel +39 0342522172 - fax +39 0342522992 guadagnino.cristi...@creval.it www.creval.ithttp://www.creval.it Il presente messaggio non è di natura personale ma inviato per esigenze lavorative; l'eventuale messaggio di risposta potrà essere conosciuto anche da altri soggetti diversi dall'originatore di questo messaggio per dette esigenze o per controllo aziendale. Questo messaggio, corredato dei relativi allegati, contiene informazioni da considerarsi strettamente riservate, ed è destinato esclusivamente al destinatario sopra indicato, il quale è l'unico autorizzato ad usarlo, copiarlo e, sotto la propria responsabilità, diffonderlo. Chiunque ricevesse questo messaggio per errore o comunque lo leggesse senza esserne legittimato è avvertito che trattenerlo, copiarlo, divulgarlo, distribuirlo a persone diverse dal destinatario è severamente proibito, ed è pregato di rinviarlo immediatamente al mittente distruggendone l'originale. -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
Re: [rt-users] Full text search/index - Migrate to Postgres or MySQL + Sphinx?
Referring to: rt-users Digest, Vol 120, Issue 13, articles 8,9,10,11,13,14,15: Thanks to those who responded. First, no; I did not know that full text indexing was supported after 5.0. I thought that was a 5.6 up coming feature. I must not have a full grasp of how it works, because if MySQL has it built in why would I need to add on Sphinx? I do have much more familiarity with MySQL over Postgres which is one reason to stay with it. I've never used Postgres before. I think Kevin's answer might be the easiest. I tried swapping out for MariaDB before, but got tripped up over table names being case sensitive. I'm trying to keep everything as simple as possible I'd rather not dive into building my own MySQL. I'll look into this some more and see what I can come up with. Thanks again to those who answered! Stephen J. Cena Systems Administrator - MIS/IT Dept Quality Vision International 850 Hudson Ave Rochester,NY 14620 Phone: 585-544-0450 x300 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Please report email problems to: postmas...@qvii.com QVII MIS/IT Dept - We do what we must because we can. Thank you for helping us help you help us all. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
