nginx(engine x) and sphinx...

[MalditohoN]

---

Hi,

I'm currently working on a website and its somehow like a search engine.

System:
dedicated webserver - Apache(ubuntu)
dedicated sqlserver - MySQL(ubuntu)

MySQL capabilities on FULLTEXT search degrades when it comes to 100,000 - millions records, properly indexed and tables are normalized, its just so happen that records need to be search are TEXT format and its over a million records. I've researched and bump on sphinx and nginx. Have anyone used it and any suggestions, in the back-end?

Your suggestions will be greatly appreciated. Thanks!

[MalditohoN]

tabang diha mga silingan...

[MalditohoN]

hiloma pud diri uy... way bisag gamay naay idea ani?

[MalditohoN]

anyone? taga-E nlng kog mga tips about DATABASE OPTIMIZATION b... hehehehe... salamat!

[ingkiang]

try to denormalized if u want speed..

[SYNCH]

I'm not familiar with MySQL full-text search but 100k-1M is not that bad (perhaps each text value is just really big). However, it is noted that MySQL chokes on this one that's why there is "Sphinx".

Sphinx creates its own indexes which it uses to pull search results from. It can manage text unto 100GB or 100,000,000 records on a single CPU.
ref: Nginx, Lighttpd, Apache, MySQL, Sphinx, memcache, Wordpress, Drupal, Joomla, CakePHP, OpenX, ImageMagick and more Managed by Reality Check Network

If this is the case, this should work assuming you got the configuration right.

First thing I would do is to check if I have the hardware required for this.

Second, check if there are anything accessing/connecting/running the server where MySQL lives on. I think that if you are planning for this search engine, it should live on its own, perhaps put Apache on some other box.

Finally, I will not think about changing code but if you do, it is worthwhile reading stuff on The Anatomy of a Search Engine which is amazing. Some good stuff in there.

[MalditohoN]

try to denormalized if u want speed..

not applicable imo suggestion... hehehe... tanx though!

I'm not familiar with MySQL full-text search but 100k-1M is not that bad (perhaps each text value is just really big). However, it is noted that MySQL chokes on this one that's why there is "Sphinx".
If this is the case, this should work assuming you got the configuration right.

First thing I would do is to check if I have the hardware required for this.

Second, check if there are anything accessing/connecting/running the server where MySQL lives on. I think that if you are planning for this search engine, it should live on its own, perhaps put Apache on some other box.

Finally, I will not think about changing code but if you do, it is worthwhile reading stuff on The Anatomy of a Search Engine which is amazing. Some good stuff in there.

I already implemented sphinx on our server. gladly the results where amazing. results ranging from 100,000 - millions were < 1 -2 sec. The service is installed on the sqlserver though because the management hasn't bought another box meant for sphinx.

the only thing lacking is the REAL TIME INDEXING. our data are always insert, update and delete. so far i do cron for creating an index.

thank you for the link. Hope someone can help me about the difference between apache and nginx.

Salamat!

[MalditohoN]

ang gusto mo suggest... welcome kaau!

[MalditohoN]

UP sa ko ni nga thread b... hehehehe...

[BeoR]

If you are planning to move to nginx from Apache, I suggest you read about php-fpm as well. The main difference, at least on my perspective, is that it is easier to scale php/fcgi under nginx into multiple servers as it can on it's own act as both a load balancer and web server. It is also generally accepted that nginx is more resource friendly compared to Apache.

For a dynamic website, Apache is faster out of the box. But if tuned properly, nginx can at least match Apache's performance if not better. If you don't want to go through the hassle of converting your Apache configurations/rewrite rules, you can also setup nginx as a reverse proxy which also improves performance quite significantly.

For MySQL optimization, it depends on your usage or query statistics. The most basic though is to buffer all your indexes into memory (ie: key_buffer_size) and a fast file-system (ie: RAID10 15k RPM SAS or Enterprise SSD's).