Professional Web Applications Themes

Tune performance of fulltext search - MySQL

Hi, I am running MySQL on a well equipped server, 2Xeon, 4G RAM and SCSI-RAID. I got one real big table with 10M rows and fulltext index. Most searches run fast enough, even on peak hours. Only when using very common words in a fulltext-query (but not common enough to stopword them), I get awful response times. Even when I stop all other activity on the server and I issue a fulltext query with 2 common words, the search can take longer than 30 sec! When monitoring the server activity (via vmstat) when issuing the query, I see the cpu ...

  1. #1

    Default Tune performance of fulltext search

    Hi,

    I am running MySQL on a well equipped server, 2Xeon, 4G RAM and
    SCSI-RAID.
    I got one real big table with 10M rows and fulltext index.
    Most searches run fast enough, even on peak hours. Only when using very
    common words in a fulltext-query (but not common enough to stopword
    them), I get awful response times.
    Even when I stop all other activity on the server and I issue a
    fulltext query with 2 common words, the search can take longer than 30
    sec!

    When monitoring the server activity (via vmstat) when issuing the
    query, I see the cpu idle state going down from 100% to exactly 75% and
    minor read activity from the hard disk during search time. I just
    cannot figure out, where is the bottleneck. It seems as if there were
    enough computing power to execute the query much faster.

    This is my custom configuration (all other values are default):
    key_buffer = 512M
    max_allowed_packet = 128M
    thread_stack = 512K
    max_connections = 600
    max_connect_errors = 10000
    table_cache = 256
    sort_buffer = 128M
    read_buffer_size = 8M
    read_rnd_buffer_size = 8M

    BTW: Is there a way to tell MySQL to read the whole index file of one
    specific table into memory, so that searches are executed without any
    disk access?

    Greetz,
    Jens

    jens.bertheau@gmx.de Guest

  2. #2

    Default Re: Tune performance of fulltext search

    [email]jens.bertheaugmx.de[/email] wrote:

    Jens, as a native German speaker you may want to ask your question
    in de.comp.datenbanken.mysql as well.
    > I am running MySQL on a well equipped server, 2Xeon, 4G RAM and
    > SCSI-RAID.
    I guess that's Hypertreading Xeons, so you have 2 physical but
    4 logical CPUs, right?
    > I got one real big table with 10M rows and fulltext index.
    How big is the index? (the .MYI file)
    > When monitoring the server activity (via vmstat) when issuing the
    > query, I see the cpu idle state going down from 100% to exactly 75% and
    > minor read activity from the hard disk during search time.
    With 4 CPUs and 75% idle that means, one of the CPUs is at 100%.
    "minor read activity" could be read as "data is cached", however
    this could be misleading if your disks are doing many searches
    (heads moving). iostat shows you #requests and device utilization.
    However it looks as your servers performance is CPU bound.

    ....
    > key_buffer = 512M
    With 4GB RAM you may set this as high as 2GB. If you're running on
    a 32-bit platform (I guess you are) this is also the limit.
    > read_buffer_size = 8M
    > read_rnd_buffer_size = 8M
    Setting those higher might take load from your disks.
    > BTW: Is there a way to tell MySQL to read the whole index file of one
    > specific table into memory, so that searches are executed without any
    > disk access?
    This is done automatically as long as your key_buffer is large enough.


    XL
    Axel Schwenke Guest

  3. #3

    Default Re: Tune performance of fulltext search

    > I guess that's Hypertreading Xeons, so you have 2 physical but
    > 4 logical CPUs, right?
    Correct.
    > > I got one real big table with 10M rows and fulltext index.
    > How big is the index? (the .MYI file)
    Its 3.7G, so no way to fit it completely into RAM
    > However it looks as your servers performance is CPU bound.
    Yes, probably. Is there any way to tell MySQL to handle queries with
    more than one logical CPU simultaneously? When I monitor CPU load it
    rarely drops below 50% idle, so, if technically possible, I could gain
    performance, if multiple CPUs could handle a single request.

    Jens

    jens.bertheau@gmx.de Guest

  4. #4

    Default Re: Tune performance of fulltext search

    [email]jens.bertheaugmx.de[/email] wrote:
    >
    >> > I got one real big table with 10M rows and fulltext index.
    >> How big is the index? (the .MYI file)
    >
    > It's 3.7G, so no way to fit it completely into RAM
    That's bad. To get good performance, all active indexes should fit into
    key_buffer. Otherwise you lose performance due to extra I/O operations.
    >> However it looks as your servers performance is CPU bound.
    >
    > Yes, probably. Is there any way to tell MySQL to handle queries with
    > more than one logical CPU simultaneously?
    Not with current MySQL. Future versions will be able to process a query
    in multiple threads, but this will be limited to partitioned tables.
    I don't know of any plans to use multiple threads in fulltext search.

    Maybe you want to have a look at an external search engine. I heard
    good things about Lucene.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. partial word search with FULLTEXT
    By creadcdd in forum MySQL
    Replies: 0
    Last Post: August 31st, 07:02 PM
  2. Help fine tune my function please!
    By Flash_Ghost webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 29th, 05:15 PM
  3. 5.0.7 tune problem during licensing
    By Roger Cornelius in forum SCO
    Replies: 0
    Last Post: August 22nd, 04:08 PM
  4. MySQL UNION on 2 Fulltext search queries
    By Andr Nss in forum PHP Development
    Replies: 0
    Last Post: July 21st, 11:55 PM
  5. Help me to tune my sql
    By joe bayer in forum Oracle Server
    Replies: 4
    Last Post: July 3rd, 04:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139