Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Mysql FULLTEXT optimization

ucablesucables EntrantCurrent User Role Beginner
i have a table of products with 200.000 products with full text search indexes
size of table is: 405 Mb, size of indexes is: 526 Mb

I would like to optimize search time, because my server load is very high.

my products table is not modified never, so i think the best way should be to use a memory engine for this table, but it doesnt support fulltext indexes,
can you tell me any other way to optimize my search?

here i have some info about index use and configuration:

my.cnf is:

mysql> show status like "key%";
+
+
+
| Variable_name | Value |
+
+
+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 6698 |
| Key_read_requests | 24950073645 |
| Key_reads | 527042599 |
| Key_write_requests | 36710021 |
| Key_writes | 2662162 |
+
+
+
7 rows in set (0.00 sec)

this is my.cnf:

[mysqld]
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
ft_min_word_len=3
key_buffer_size=1500M
open-files-limit=20000
query_cache_size= 64M
max_connections = 256
safe-show-database
skip-locking
key_buffer = 8M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size =8M
thread_cache_size = 8
delay_key_write = ALL
low_priority_updates=1
concurrent_insert=2
thread_concurrency = 8
wait_timeout = 90


Let me know if you will need more info

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    Actually, it is hard to give advice without knowing a lot about your schema, data, and the queries you run against it. But in general, as long as you can fit the data into memory, myisam + fulltext should work reasonably well. Most of our customers who can't fit it into RAM end up with Sphinx + InnoDB instead.
  • ucablesucables Entrant Current User Role Beginner
    i have much traffic at my site.
    Queries are always different, so query cache not works.
    i use key_buffer_size = 3G, to have indexed in memory.

    but its still slow.

    What will be the best way to load tables in memory to speed up access.

    i can hold tables in memory without problems but how can i do it using myisam tables?

    thank you
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.