Re: How to optimize fulltext selection?
Hello Thank you so much. I Just increase the size of some buffers: # The MySQL server [mysqld] default-character-set = cp1251 port= 3306 socket = /x/xx.sock basedir = /x datadir = /x/x log-error = /x/error.log log-slow-queries = /x/slow.log pid-file = /x/pid.file key_buffer = 512M #was 384 max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 8M # was 4 read_buffer_size = 8M # was 4 read_rnd_buffer_size = 32M # was 16 myisam_sort_buffer_size = 128M # was 64 thread_cache = 8 query_cache_size= 16M query_cache_type = 2 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections=300 skip-locking skip-name-resolve skip-innodb skip-networking server-id = 1 Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello. At first, we should ensure that most time query is spending in the ordering of the results. What state is SHOW PROCESSLIST reporting for this query? Michael Monashev [EMAIL PROTECTED] wrote: Hello GP And MySQL uses a filesort algorithm. How to make the filesort faster? May be I have to increase size of some buffers? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello GP What state is SHOW PROCESSLIST reporting for this query? Fulltext initialization Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello. Fulltext initialization Ok, now we exactly know that we should optimize the FULLTEXT part of your query. I don't know the options which directly affects the speed of the FULLTEXT searches, so I suggest you to play with key_buffer and table structure. The ratio of Key_reads to Key_read_requests is good so we don't need to increase the value of the key_buffer_size. Check if the speed of the queries have changed after running OPTIMIZE TABLE on `programms1` table. Please send the output of EXPLAIN for your query after running ANALYZE TABLE. If the output of EXPLAIN and the speed of the query has changed when you make the strings in the AGAINST clauses the same? Create a separate table (let's call it newtable) with id and search_keywords fields (linked to the programms1 on id). Make a FULLTEXT index on search_keywords and research if the speed of the query which retrieves the 'relevance' from the newtable has changed. Research how the value of the PACK_KEYS option affects the speed of the queries on your old (I guess you'll perform tests on the copy of the original table) and new table (with the structure which I've proposed). Create a separate cache for indexes from the newtable and load indexes into it (I guess the memory where FULLTEXT index is stored will be less fragmented). Create a separate cache for the programms1 table and load indexes into it on startup (for the same reason), however I'm not sure if you able to do this in production environment. The best is to perform all manipulations on the test server with the same configuration. See: http://dev.mysql.com/doc/mysql/en/index-preloading.html http://dev.mysql.com/doc/mysql/en/multiple-key-caches.html Michael Monashev [EMAIL PROTECTED] wrote: Hello GP What state is SHOW PROCESSLIST reporting for this query? Fulltext initialization Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello GP And MySQL uses a filesort algorithm. How to make the filesort faster? May be I have to increase size of some buffers? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Privet.
SELECT SQL_CACHE id, program_name, categor, subcategor, subsubcategor,
subsubsubcategor, source_url, rating, short_description, long_description,
when_modifed+0, author, searched_words, size_in_kb, licence, language,
install, win95, win98, winme, winnt, win2000, winxp, wince, unix, palm, mac,
other_versions, rus_progs , MATCH (search_keywords ) AGAINST ('ÙÐßÛ xbÊÍ
ÞÊÎÍ' ) as relevance FROM programms1 WHERE MATCH (search_keywords ) AGAINST
('+((ÙÐßÛ)) +(xbÊÍ) +(ÞÊÎÍ)' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT
1000;
I didn't done much research but first thing which comes to mind is that your
query has two potentially problematic places. The first one is a
FULLTEXT part, the second is ORDER BY clause. In my opinion the
index isn't used in the ordering (please, correct me if I'm wrong).
And MySQL uses a filesort algorithm. If a filesort must be done, all
rows that match the query without the LIMIT clause must be selected, and
most or all of them must be sorted, before it can be ascertained that
the first 1000 rows have been found. Use SHOW PROCESSLIST (mysqladmin
processlist, mytop) to find out in which states mysqld threads of the slow
queries are spending more time. In such a way we'll find the bottleneck in
the query.
Michael Monashev [EMAIL PROTECTED] wrote:
Hello
GP Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
GP statements, amount of RAM, most problematic queries (use mysqldumpslow
GP utility to find them) include tables' definitions (use SHOW CREATE
GP TABLE).
SHOW VARIABLES;
back_log50
basedir /*/
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_clientutf8
character_set_connectionutf8
character_set_database cp1251
character_set_results utf8
character_set_servercp1251
character_set_systemutf8
character_sets_dir /***/share/mysql/charsets/
collation_connectionutf8_general_ci
collation_database cp1251_general_ci
collation_servercp1251_general_ci
concurrent_insert ON
connect_timeout 5
datadir /*/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit100
delayed_insert_timeout 300
delayed_queue_size 1000
expire_logs_days0
flush OFF
flush_time 0
ft_boolean_syntax + -()~*:|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit20
ft_stopword_file(built-in)
group_concat_max_len1024
have_archiveNO
have_bdbNO
have_compress YES
have_crypt YES
have_csvNO
have_example_engine NO
have_geometry YES
have_innodb NO
have_isam NO
have_ndbcluster NO
have_opensslNO
have_query_cacheYES
have_raid NO
have_rtree_keys YES
have_symlinkYES
init_connect
init_file
init_slave
interactive_timeout 28800
join_buffer_size131072
key_buffer_size 536870912
key_cache_age_threshold 300
key_cache_block_size1024
key_cache_division_limit100
language/*/share/mysql/english/
large_files_support ON
license GPL
local_infileON
locked_in_memoryOFF
log OFF
log_bin OFF
log_error /***/error.log
log_slave_updates OFF
log_slow_queriesON
log_update OFF
log_warnings1
long_query_time 10
low_priority_updatesOFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 300
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295
max_length_for_sort_data1024
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections0
max_write_lock_count4294967295
myisam_data_pointer_size4
myisam_max_extra_sort_file_size 2147483648
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
net_buffer_length 16384
net_read_timeout30
net_retry_count 100
net_write_timeout 60
Variable_name Value
new OFF
old_passwords OFF
open_files_limit14781
pid_file/**/pid.file
port3306
preload_buffer_size 32768
protocol_version10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit4096
query_cache_size
Re: How to optimize fulltext selection?
Privet! Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS' statements, amount of RAM, most problematic queries (use mysqldumpslow utility to find them) include tables' definitions (use SHOW CREATE TABLE). Michael Monashev [EMAIL PROTECTED] wrote: Hello, I have 200-300 kb slow log daily with fulltext queries only :-( All queries using fulltext indexes. I use huge mysql cofig (huge.cfg). What can I change in the mysql configuration for better performance? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize fulltext selection?
Hello GP Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS' GP statements, amount of RAM, most problematic queries (use mysqldumpslow GP utility to find them) include tables' definitions (use SHOW CREATE GP TABLE). SHOW VARIABLES; back_log50 basedir /*/ binlog_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_clientutf8 character_set_connectionutf8 character_set_database cp1251 character_set_results utf8 character_set_servercp1251 character_set_systemutf8 character_sets_dir /***/share/mysql/charsets/ collation_connectionutf8_general_ci collation_database cp1251_general_ci collation_servercp1251_general_ci concurrent_insert ON connect_timeout 5 datadir /*/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit100 delayed_insert_timeout 300 delayed_queue_size 1000 expire_logs_days0 flush OFF flush_time 0 ft_boolean_syntax + -()~*:| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit20 ft_stopword_file(built-in) group_concat_max_len1024 have_archiveNO have_bdbNO have_compress YES have_crypt YES have_csvNO have_example_engine NO have_geometry YES have_innodb NO have_isam NO have_ndbcluster NO have_opensslNO have_query_cacheYES have_raid NO have_rtree_keys YES have_symlinkYES init_connect init_file init_slave interactive_timeout 28800 join_buffer_size131072 key_buffer_size 536870912 key_cache_age_threshold 300 key_cache_block_size1024 key_cache_division_limit100 language/*/share/mysql/english/ large_files_support ON license GPL local_infileON locked_in_memoryOFF log OFF log_bin OFF log_error /***/error.log log_slave_updates OFF log_slow_queriesON log_update OFF log_warnings1 long_query_time 10 low_priority_updatesOFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors 10 max_connections 300 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 4294967295 max_length_for_sort_data1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables 32 max_user_connections0 max_write_lock_count4294967295 myisam_data_pointer_size4 myisam_max_extra_sort_file_size 2147483648 myisam_max_sort_file_size 2147483647 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 67108864 net_buffer_length 16384 net_read_timeout30 net_retry_count 100 net_write_timeout 60 Variable_name Value new OFF old_passwords OFF open_files_limit14781 pid_file/**/pid.file port3306 preload_buffer_size 32768 protocol_version10 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit4096 query_cache_size16777216 query_cache_typeDEMAND query_cache_wlock_invalidateOFF query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size2093056 read_only OFF read_rnd_buffer_size8384512 relay_log_purge ON rpl_recovery_rank 0 secure_auth OFF server_id 1 skip_external_locking ON skip_networking ON skip_show_database OFF slave_net_timeout 3600 slow_launch_time2 socket /tmp/.sock sort_buffer_size2097144 sql_mode storage_engine MyISAM sync_binlog 0 sync_frmON system_time_zoneMSD table_cache 512 table_type MyISAM thread_cache_size 8 thread_stack196608 time_format %H:%i:%s time_zone SYSTEM tmp_table_size 33554432 tmpdir transaction_alloc_block_size8192 transaction_prealloc_size 4096 tx_isolationREPEATABLE-READ version 4.1.8-log version_comment Source distribution version_compile_machine i386 version_compile_os unknown-freebsd5.2.1 wait_timeout28800 SHOW STATUS; Aborted_clients 195 Aborted_connects161 Binlog_cache_disk_use 0 Binlog_cache_use0 Bytes_received 2375361016 Bytes_sent 1259738696 Com_admin_commands 2162461 Com_alter_db0 Com_alter_table 25 Com_analyze 0 Com_backup_table0 Com_begin 0 Com_change_db 778 Com_change_master 0 Com_check 0 Com_checksum0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index0 Com_create_table
How to optimize fulltext selection?
Hello, I have 200-300 kb slow log daily with fulltext queries only :-( All queries using fulltext indexes. I use huge mysql cofig (huge.cfg). What can I change in the mysql configuration for better performance? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
