MySQL Fulltext Search Suchgeschwindigkeit

S

strongarm

Mitglied
Hallo alle

Vielleicht kann mir ja hier jemand helfen.

Ich hab eine Search Engine gebaut mit den MySQL Fulltext Search Features.
Am Anfang hat's auch gut funktioniert und beim einem Benchmarktest bei
dem ich die Fulltext Tabellen mit 200'000 Einträgen random Text vollgepappt
habe, hat auch noch alles gut funktioniert. Nun aber nach 35'000 Einträgen
wird die Query verflucht langsam. Zum Teil dauert sie schon bis zu 9 Sekunden
manchmal bei anderen Keywords auch wieder schneller.

Mit den Cache Optionen im my.cnf gehts zwar besser, aber ist auch noch nicht
so das Optimum.

Im Moment hab ich etwa 12'000 Single Users pro Tag, die auf die Seite reinschiessen
aber ich glaube eher, dass es kein allgemeines Performance Problem ist, da alle Queries auf andere Tabellen tiptop schnell sind. Es ist also nur diese Fulltext Search Tabelle, die langsam
durchsucht wird und man kaum noch updaten kann.

Wenn man Daten in der Tabelle updatet, dann gehts die ersten 10 Einträge schnell
aber dann nimmt die Geschwindigkeit immer mehr ab. Auch die Query von aussen
wird zu diesem Zeitpunkt verflucht langsam. Hängt das mit dem builden des Fulltext
Search Index zusammen?

Hat vielleicht jemand schon so eine Fulltext Search Engine gebastelt und weiss, auf
was man da genau achten muss?

Ich benutze MySQL Version 4
Mein Rechner ist ein P4 3Ghz mit 1GB RAM
Mein Betriebssystem ist FreeBSD 5.1

RAM ist genügend verfügbar.
Festplattenplatz ist bei 97 Prozent (5GB immer noch frei)
Die SEARCH Tabelle (Fulltext Search Indexiert) ist 13 MB gross.

Mein my.cnf sieht folgendermassen aus:

[mysqld]
ft_min_word_len=2
set-variable = key_buffer_size=30M
set-variable = query_cache_size=30M
set-variable = query_cache_limit=30M
set-variable = bdb_cache_size=30M
bdb_cache_size = 30M
set-variable = bdb_max_lock=30000
skip-networking

Meine erste Query sah folgendermassen aus:
$query = "select SQL_CALC_FOUND_ROWS b.THUMBNAIL_URL, b.PICTURE_URL,
b.PIC_NAME, b.PIC_SIZE, b.SPONSOR_TXT, b.SPONSOR_URL,
match (a.TITLE,a.BODY) against ('$string' in boolean mode) as SCORE from
".$this->SEARCHTABLE." a, ".$this->ATTRIBUTETABLE." b where match (a.TITLE,a.BODY) against
('$string' in boolean mode) and b.ID=a.ID and a.VISIBLE='Y' and b.$display='Y' order by b.CREATION DESC limit $curr,$max";

Ich hab dann rausgefunden dass es erhebliche Performance einbussen gibt, wenn man SQL_CALC_FOUND_ROWS in der Query verwendet und weitere Tabellen gleichzeitig abfragt. Also hab ich diese mal umgeschrieben.

Meine neue Query, die schneller ist sieht nun folgendermassen aus:
$query = "select ID, match (BODY) against ('$string' in boolean mode)
from SEARCH where match (BODY) against ('$string' in boolean mode) and VISIBLE='Y'
limit $curr,$max";

Mein Problem bei dieser Query ist, dass ich nicht weiss, wie ich die totale Anzahl der gefundenen Einträge rausfinde, da ich SQL_CALC_FOUND_ROWS zusammen mit dem limit Kommando nicht mehr benutzen kann. Hat da jemand eine Lösung?

Auch ist das update Problem dadurch noch nicht gelöst.

Meine Tabellen:
CREATE TABLE SEARCH (
ID int(11) NOT NULL default '0',
TITLE varchar(255) default NULL,
BODY text,
VISIBLE enum('Y','N') default NULL,
PRIMARY KEY (ID),
FULLTEXT KEY TITLE (TITLE,BODY)
) TYPE=MyISAM;

CREATE TABLE ATTRIBUTES (
ID int(11) NOT NULL auto_increment,
PIC_NAME varchar(255) default NULL,
THUMBNAIL_PATH varchar(255) default NULL,
PICTURE_PATH varchar(255) default NULL,
THUMBNAIL_URL varchar(255) default NULL,
PICTURE_URL varchar(255) default NULL,
SPONSOR_TXT varchar(255) default NULL,
SPONSOR_URL varchar(255) default NULL,
PIC_SIZE int(11) default NULL,
SEARCH_INDEXED enum('Y','N') default 'N',
CREATION datetime default NULL,
MODIFICATION datetime default NULL,
DISPLAY_PREVIEW enum('Y','N') default 'Y',
DISPLAY_MEMBERS enum('Y','N') default 'Y',
PRIMARY KEY (ID),
UNIQUE KEY ID (ID),
UNIQUE KEY THUMBNAIL_PATH (THUMBNAIL_PATH),
UNIQUE KEY PICTURE_PATH (PICTURE_PATH),
UNIQUE KEY THUMBNAIL_URL (THUMBNAIL_URL),
UNIQUE KEY PICTURE_URL (PICTURE_URL)
) TYPE=MyISAM;

mysql> show index from SEARCH;
+---------------------------------+---------------------------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------------------+---------------------------------+
| SEARCH | 0 | PRIMARY | 1 | ID | A | 35432 | NULL | NULL | | BTREE | |
| SEARCH | 1 | TITLE | 1 | TITLE | A | 17716 | NULL | NULL | YES | FULLTEXT | |
| SEARCH | 1 | TITLE | 2 | BODY | A | 17716 | NULL | NULL | YES | FULLTEXT | |
+---------------------------------+---------------------------------+
3 rows in set (0.02 sec)


Kann mir jemand einen guten Tip geben, wie ich die MySQL Fulltext Search Engine noch optimieren kann? Es kann doch nicht sein, dass mit 35'000 Einträgen schon Performance
einbussen beim suchen entstehen.

Gibt es bessere Möglichkeiten (Methoden) eine Fulltext Search Engine zu basteln? Bin da um jeden Tip froh.

Gibt es eine andere Möglichkeit als SQL_CALC_FOUND_ROWS um die totale Anzahl gefundener Einträge rauszufinden? Da ich mit limit arbeite kommt mit mysql_num_rows($result) natürlich
nur raus, was wirklich gefetched wurde.

Vielen Dank für eure Antwort falls ihr eine habt.

Gruss
Chris

mysql> show variables
-> ;
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| back_log | 50 |
| basedir | /usr/local/ |
| bdb_cache_size | 31457280 |
| bdb_log_buffer_size | 32768 |
| bdb_home | /var/db/mysql/ |
| bdb_max_lock | 30000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | /var/tmp/ |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (February 10, 2004) |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /var/db/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 2 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | YES |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | ./ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 31457280 |
| language | /usr/local/share/mysql/english/ |
| large_files_support | ON |
| local_infile | ON |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 1000000 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 11095 |
| pid_file | /var/db/mysql/nicorette.pid |
| log_error | |
| port | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 31457280 |
| query_cache_size | 31457280 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | ON |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| tx_isolation | REPEATABLE-READ |
| timezone | CEST |
| tmp_table_size | 33554432 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.18 |
| version_comment | FreeBSD port: mysql-server-4.0.18_1 |
| wait_timeout | 28800 |
+---------------------------------+---------------------------------+
 
Hallo!

Schon mal drüber nachgedacht, ob MySQL da überhaupt die richtige Lösung ist?
Ist da Postgres oder was kommerzielles eventuell besser?

Gruß,
Ansgar
 
Hallo Ansgar

Merci für deine Antwort. Ich habe noch von ein paar anderen Leuten mitbekommen, dass MySQL ziemlich Bitchy ist beim Fulltext Search und ohne Caching nichts läuft.

Also hier meine Erfahrungen bisher, falls sich mal jemand anders mal damit rumschlägt.

Bei ca. 20'000 - 30'000 Fulltext Einträgen kommt MySQL mit dem Suchen an den Anschlag ohne cashing. Gewisse Abfragen gehen schnell und andere dauern bis zu 10 Sekunden oder noch länger.

Mit Caching sind die Queries zwar schnell, aber man weiss nie, wenn man an die nächsten performance Probleme kommt. Es gibt keinen direkten Vergleich zu Random Testeinträgen und den Einträgen in der Produktion. Daher wird ein Test mit Random Einträgen kaum nutzbare Ergebnisse ausspucken. Somit ist mir diese Option zu gefährlich um das alles wirklich produktiv zu schalten.

Wenn man für die Fulltext Kolumnen den Typ "text" verwendet, ist es auch nicht möglich, eine temporäre Memory HEAP Tabelle zu erstellen und die Datensätze gleich alle in's RAM zu laden.

Da es sonst keine Möglichkeit gibt, in MySQL mit den Cashing Funktionen nur eine einzige Tabelle in's RAM zu laden (siehe my.cnf), sondern diese Werte für alle Tabellen gelten ist man bei mehreren Datenbanken auch ziemlich aufgeschmissen, da Queries gecached werden, die eigentlich gar nicht gecached werden müssten.

Es gibt die Möglichkeit, bei MySQL einen single incident case zu eröffnen für 500 Euro. Aber ich denke, das kann man sich sparen, da man wahrscheinlich von denen nicht viel mehr erfahren wird nehm ich an.

Mit Postgres hab ich leider keinerlei Erfahrungen. Hat da jemand Tests von Fulltext Abfragen mit ca. 200'000 Rows oder mehr?

Dass es mit Oracle geht, haben mir auch schon mehrere Leute gesagt. Leider kenn ich Oracle nur ein bisschen und hab die Fulltext Search Options noch nie benutzt. Aber diese Investition ist mir glaub ich zu hoch in Anbetracht, dass ich noch nicht weiss, wie gut mein Produkt auf dem Markt aufgenommen wird.

Für den Moment hab ich mein Konzept etwas vereinfacht und unterteile nur noch in Kategorien und lasse somit die Fulltext Search weg bis ich da wirklich eine sinnvolle Lösung gefunden habe.

Für weitere Gedanken oder Ideen bin ich euch natürlich immer noch dankbar.

Grüsse
Chris
 
Danke für deine Erfahrungen an dieser Stelle. Ich hab mich für die Boardsuche mit Mysql Search beschäftig. Alle Daten sind ja in der DB, aber das Resultat war in keinen Belangen zufriedenstellend. Ich hab einfach Google API für die Suche genutzt. Es wird zwar nur durchsucht, was gespiert wird, aber die Suche ist der Hammer. Der einzige Geschwindigkeitseinbruch, wir müssen es halt von einem anderen Server holen.
 
QUOTE der Artikel ist erst ein paar Tage alt

Daher hab ich ihn dann wohl nicht mit der Suchfunktion gefunden.
Danke schonmal für den Hinweis.

__Verzögerung__
Wie groß ist denn die Verzögerung bei Google überlichweise?
Normalerweise gibt es doch nur einmal im Monat einen Deep-Crawl.
Ich versteh nicht ganz, warum die Verzögerungen nicht mehr Probleme machen.

Der Links auf Google im Faq-Artiekl bringt bei mir einen 404.

Gruß,
Ansgar
 
http://www.google.com/apis da fehlte ein s
Brauchst du aber nicht, das Script ist ready to go.

QUOTE Normalerweise gibt es doch nur einmal im Monat einen Deep-Crawl.

Google spidert uns täglich. In den letzten Tagen gabs aber Ausfälle weshalb auch ein paar (Offline) Seiten im Index sind.

 
Problem gelöst?

Ich finde das Thema, dank unserer Suchmaschinen, recht interessant und würde gern wissen wie eure Erfahrungen sind.

Strongarm, bist du vorangekommen?
 
Es ist schon witzig, dass ich lange nicht mehr auf dem Board war und meinen eigenen Artikel in Google finde für ein anderes Problem. Da ist ja schon ganz schön diskutiert worden ;-)

Vielen Dank für deinen Link c-e um auf den FT-Index zu verzichten. Leider gibt mein Browser da nichts zurück. Kannst du mir nochmals erklären was du damit genau meinst? Wäre dir sehr dankbar darum.

Danke Alain für deinen Input mit der Google API. Ist leider nicht ganz dass, was ich brauche, da ich eine eigene Picture und Movie Search Engine habe und noch mehr Search Rubriken hinzufügen will. Die Bilder und Movies befinden sich auf meinem Server und sind lizensiert. Dazu kommt, dass Google die Abfragen bis auf 1000 Searches limitiert laut der Beschreibung der Google API oder nicht?

Ich hab mir jetzt einige andere Datenbanken oder Produkte um Datenbanken noch angesehen wie DB2 (auch langsam), Postgres (kenn ich einfach zu wenig), Oracle (Lizenzen). Ich denke, dass es besser ist, sich noch ein bisschen mehr mit MySQL zu beschäftigen.

Das Produkt, dass ich geplant hatte mit der Search Engine hab ich auf Kategorien umgestellt und verwende kein Fulltext Search mehr im Moment. Beim Produkt, das schon so läuft, werde ich bis Ende Jahr folgendes mit MySQL probieren:

Anstatt Text Kolumnen für den Fulltext Search mach ich Char Kolumnen. Ich erstelle einen Table namens SEARCH ohne Fulltext Indexing.

CREATE TABLE SEARCH (
ID int(11) NOT NULL default '0',
TITLE varchar(255) default NULL,
BODY char,
VISIBLE enum('Y','N') default NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM;

Dann erstelle ich eine HEAP=memory Tabelle und kopiere jeweils alle Einträge. Also sind nun alle Keywords im Memory und ich hab nicht den Effekt, dass auch andere Tabellen Einträge ins Memory auslagern.

CREATE TABLE SEARCH (
ID int(11) NOT NULL default '0',
TITLE varchar(255) default NULL,
BODY char,
VISIBLE enum('Y','N') default NULL,
PRIMARY KEY (ID),
FULLTEXT KEY TITLE (TITLE,BODY)
) TYPE=MEMORY;

Leider weiss ich noch nicht, ob man FULLTEXT Indexes in's Memory anlegen kann.

Kauf mir in ein paar Tagen einen neuen Server und mache das bis anfangs nächsten Jahres.
Ich werd euch die Resultate sicher wieder posten.

Merci
Chris
 
QUOTE Dazu kommt, dass Google die Abfragen bis auf 1000 Searches limitiert laut der Beschreibung der Google API oder nicht?

Die Google Technologie ist zweifellos genial. Leider ist die API aber nur für das benutzbar, was Google auch spidert... Die 1000 Abfragen kannst Du vervielfachen, wenn Du nett nachfragst (d.h. einen sinnvollen Grund hast).

PS Ich komme immer wieder über Google hier ins WF... ;-)
 
@strongarm

Probier es nochmal mit obiger URL es müsse nach ein paar Sekunden ein PDF kommen.

Zu dem FULLTEXT im RAM kan ich nur sagen das TEXT Columns grundsätzlich nicht bei HEAP Tabellen 'gestattet' sind.
Diese wirst du also gar nicht in eine HEAP Tabelle bekommen
dry.gif

Somit scheitert das Vorhaben...
 
Yep. Das ist gemein, das mit den Text Kolumnen.

Ich probiers mit Varchar(255). Genügt für mich soweit, da alles manuell indexiert wird und ich nicht ganze Texte in die Tabelle reinschreiben muss.

Merci
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben