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 |
+---------------------------------+---------------------------------+
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 |
+---------------------------------+---------------------------------+