Professional Web Applications Themes

sql performance with a large number of rows in a table. - MySQL

hi, I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with 9G of ram. I have a number of machines that connect to the MySQL server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating about 1500-3000 queries/sec. Now, when the table it's writing to is empty, things are fine....very fast, the DB is able to handle all of the queries. Also, when the table starts and it's empty, most of the things it's doing are SELECTS and INSERTS. As this table fills up, that will change to SELECTS and UPDATES. Also, typically there are about ...

  1. #1

    Default sql performance with a large number of rows in a table.

    hi,
    I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
    9G of ram. I have a number of machines that connect to the MySQL
    server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
    about 1500-3000 queries/sec.

    Now, when the table it's writing to is empty, things are fine....very
    fast, the DB is able to handle all of the queries. Also, when the
    table starts and it's empty, most of the things it's doing are SELECTS
    and INSERTS. As this table fills up, that will change to SELECTS and
    UPDATES.

    Also, typically there are about 10 threads open at any given time.

    Somewhere around 60 million rows, the # of queries/sec goes down to
    about 500-1500, and the number of threads starts to grow until it
    reaches the server max. (which is set to 90)

    Here is my my.cnf file:

    log-long-format
    skip-locking
    max_allowed_packet=8M
    key_buffer_size=2048M
    sort_buffer_size=2M
    read_buffer_size=2M
    myisam_sort_buffer_size=128M
    query_cache_size=128M
    table_cache=220
    thread_concurrency=4
    bulk_insert_buffer_size=64M
    max_connections=90
    thread_cache=10
    wait_timeout=1800
    myisam_max_sort_file_size=3000000000

    does anybody have any suggestions or know why the mysql server is
    exhibiting this behavior?


    thanks in advance!!

    sawoad Guest

  2. #2

    Default Re: sql performance with a large number of rows in a table.

    sawoad wrote:
    > hi,
    > I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
    > 9G of ram. I have a number of machines that connect to the MySQL
    > server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
    > about 1500-3000 queries/sec.
    >
    > Now, when the table it's writing to is empty, things are fine....very
    > fast, the DB is able to handle all of the queries. Also, when the
    > table starts and it's empty, most of the things it's doing are SELECTS
    > and INSERTS. As this table fills up, that will change to SELECTS and
    > UPDATES.
    >
    > Also, typically there are about 10 threads open at any given time.
    >
    > Somewhere around 60 million rows, the # of queries/sec goes down to
    > about 500-1500, and the number of threads starts to grow until it
    > reaches the server max. (which is set to 90)
    >
    > Here is my my.cnf file:
    >
    > log-long-format
    > skip-locking
    > max_allowed_packet=8M
    > key_buffer_size=2048M
    > sort_buffer_size=2M
    > read_buffer_size=2M
    > myisam_sort_buffer_size=128M
    > query_cache_size=128M
    > table_cache=220
    > thread_concurrency=4
    > bulk_insert_buffer_size=64M
    > max_connections=90
    > thread_cache=10
    > wait_timeout=1800
    > myisam_max_sort_file_size=3000000000
    >
    > does anybody have any suggestions or know why the mysql server is
    > exhibiting this behavior?

    It cannot perform magic. You are most likely exceeding the capabilities of
    your hardware. You will need faster hardware or multiple machines with
    load balancing.



    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  3. #3

    Default Re: sql performance with a large number of rows in a table.

    "sawoad" <sawoad> wrote:
    > I am currently using MySQL 5.0.16 on a dual pentium 4-2G system with
    > 9G of ram. I have a number of machines that connect to the MySQL
    > server, doing INSERTS, SELECTS, and UPDATES on 1 table, and generating
    > about 1500-3000 queries/sec.
    I am not familiar with Intel model names, so what does 'pentium 4-2G'
    mean? What operating system are you running? Is it a 32- or 64-bit
    system? Probably lot of your RAM is just useless. To really *use* more
    than ~3GB of RAM you should switch to a 64-bit system.
    > Now, when the table it's writing to is empty, things are fine....very
    > fast, the DB is able to handle all of the queries. Also, when the
    > table starts and it's empty, most of the things it's doing are SELECTS
    > and INSERTS. As this table fills up, that will change to SELECTS and
    > UPDATES.
    >
    > Also, typically there are about 10 threads open at any given time.
    >
    > Somewhere around 60 million rows, the # of queries/sec goes down to
    > about 500-1500, and the number of threads starts to grow until it
    > reaches the server max. (which is set to 90)
    Details!

    What's the READ/WRITE ratio in your queries? Is it READs or WRITEs that
    get slow? What state are the threads in (SHOW FULL PROCESSLIST) ?
    Do you experience locking problems (threads in state 'Locked') ?
    What storage engine (table type) are you using?

    After all, performance degradation by factor 2 from 0 rows to 60 Mio
    rows is quite OK. Having a growing backlog (that's the number of
    threads) is also normal in this situation.
    > Here is my my.cnf file:
    >
    > key_buffer_size=2048M
    This is by far the most important setting if you use only/mostly MyISAM
    tables. As a rule of thumb all your active indexes should fit into this
    buffer space. However, if you're on a 32-bit platform, 2GB is the max.
    setting. For 60 Mio rows this would allow you ~16 bytes index per row.
    Not much.
    > query_cache_size=128M
    This might be wasted. Depends on your application. Are there (literally)
    identical SELECT statements? Are there only few WRITEs to your tables
    (that will invalidate query cache entries) ?


    More interesting detail: what is the utilization of your servers
    resources when it shows performance degradation? Most important:
    is it CPU bound or I/O bound? In most cases I/O is the more
    precious resource.


    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

  4. #4

    Default Re: sql performance with a large number of rows in a table.

    Hi Axel,

    I made a mistake regarding the CPU's. They are actually a little bit
    beefer than originally stated..anyways, linux states:

    Intel P4/Xeon Extended MCE MSRs
    Intel(R) Xeon(TM) CPU 3.20GHz stepping 01

    The exact operating system is Red Hat Enterprise Linux ES release 4
    (Nahant) It is not a 64bit system.

    As for the ram, I don't know what's using it all, but there's currently
    only 10M free. At the current moment, there are 55 million rows in the
    table, and the entire DB looks like it's handling everything fine.

    Once this one table is somewhat filled, the ratio becomes SELECTS vs.
    UPDATES, and it breaks down to 55% selects, 45% inserts.

    I should also say that...most of the actions to this DB occur in 2
    MyISAM tables. Reads/Writes/Updates occur in both tables, however,
    table 1 is 35 million rows while table 2 is 60+ million rows. Also,
    upon deleting table 2, the DB is able to keep up with all the
    connections being made to it...(table 1 has never been deleted...), and
    that is, until table 2 reaches 55-60 million rows.

    I have the DB set so that it will log slow queries. however, it's not
    really reads or writes that are slow. In fact, they do not even show
    up in the log. However, going back to what I was saying, what ends up
    happening is the DB server is not able to handle all the requests from
    all the servers, and the number of connections start to grow until it
    reaches the system max.

    As of this moment, I am not suffering any locking problems. (also, the
    DB is entirely myisam. This table in particular is a myisam table with
    delay_key_write enabled. (it speeds things up quite a bit...however, I
    was starting to wonder if that was the cause of the problems?)

    You are correct regarding the query cache size. There aren't any
    identical select statements, and there are tons of update statements
    that happen. So - I guess I can lower that option considerably.

    As for the system, here are the stats on the CPU (as of now with 55
    million rows..)

    Cpu(s): 8.4% us, 2.7% sy, 0.0% ni, 44.1% id, 44.2% wa, 0.1% hi,
    0.6% si


    UPDATE :

    so there are now about 63 million rows in this one particular table,
    and the slowdown is occuring. There are currently 69 open connections.

    Here is the same information as above now:

    Cpu(s): 13.8% us, 3.3% sy, 0.0% ni, 50.6% id, 31.2% wa, 0.1% hi,
    0.8% si

    Again, 10M of memory is all that remains free.


    Thanks for your time...and let me know if you have any other questions!
    :-)

    sawoad Guest

  5. #5

    Default Re: sql performance with a large number of rows in a table.

    "sawoad" <sawoad> wrote in message
    news:1142436439.363614.163070i39g2000cwa.googlegr oups.com...
    > As for the ram, I don't know what's using it all, but there's currently
    > only 10M free.
    I would focus my efforts here if I were you. Figure out what's taking all
    that RAM, and figure out what must be done to reduce it. That's 10MB out of
    9GB? In other words, about 99.89% of memory is occupied. It's likely that
    the actual demand on memory is higher, and your system is going crazy trying
    to page active memory to disk.

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: sql performance with a large number of rows in a table.

    sawoad wrote:
    > The exact operating system is Red Hat Enterprise Linux ES release 4
    > (Nahant) It is not a 64bit system.
    >
    > As for the ram, I don't know what's using it all, but there's currently
    > only 10M free.

    That sounds quite normal. OS file-system caching will use up as much RAM as
    you can throw at it in most cases.

    Try this:

    $ free
    total used free shared buffers cached
    Mem: 4149316 2992228 1157088 0 80188 2258512
    -/+ buffers/cache: 653528 3495788
    Swap: 2048276 144 2048132


    This is from a machine with 4GB RAM. Apparently it has 1157MB of free RAM,
    but in fact 2258MB of the RAM that is used is file-system caching, so the
    total free RAM in 3495MB.

    If your Swap:used is low (like 144k as above) then you have plenty of RAM.


    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  7. #7

    Default Re: sql performance with a large number of rows in a table.

    Hi Brian,

    you are correct - here is my output from free:

    total used free shared buffers
    cached
    Mem: 9288304 9283688 4616 0 19708
    7008448
    -/+ buffers/cache: 2255532 7032772
    Swap: 12289488 1884 12287604

    sawoad Guest

  8. #8

    Default Re: sql performance with a large number of rows in a table.

    sawoad wrote:
    > Hi Brian,
    >
    > you are correct - here is my output from free:
    >
    > total used free shared buffers
    > cached
    > Mem: 9288304 9283688 4616 0 19708
    > 7008448
    > -/+ buffers/cache: 2255532 7032772
    > Swap: 12289488 1884 12287604

    You certainly have no memory usage issue then.



    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  9. #9

    Default Re: sql performance with a large number of rows in a table.

    "sawoad" <sawoad> wrote:
    >
    > The exact operating system is Red Hat Enterprise Linux ES release 4
    > (Nahant) It is not a 64bit system.
    I'm still not aware if your CPUs support the EM64T extension that make
    it possible to run a 64-bit Linux kernel. Probably it would be of
    advantage to switch to 64-bit Linux. Especially if you could use more
    than 2GB of key_buffer. Read below for reasoning.
    > Once this one table is somewhat filled, the ratio becomes SELECTS vs.
    > UPDATES, and it breaks down to 55% selects, 45% inserts.
    INSERTs or UPDATEs? INSERTs are fast as long as your datafile has no
    "holes". In that case INSERTs and SELECTs can be done in parallel. If
    you have holes in your datafile (this happens when you DELETE rows or
    you have variable length rows and do UPDATEs that change the length of
    a row) then READ and WRITE requests become mutually exclusive. In this
    case I would expect to see many threads in the "Locked" state.
    > I have the DB set so that it will log slow queries. however, it's not
    > really reads or writes that are slow. In fact, they do not even show
    > up in the log.
    You can lower the slow-query threshold from the 10 second default.
    However I don't think you have single slow queries. It's just that
    MySQL cannot handle the throughput from your clients. That leads to
    a backlog of SQL statements in form of worker threads.
    > As for the system, here are the stats on the CPU (as of now with 55
    > million rows..)
    >
    > 8.4% us, 2.7% sy, 0.0% ni, 44.1% id, 44.2% wa, 0.1% hi, 0.6% si
    > so there are now about 63 million rows in this one particular table,
    > and the slowdown is occuring. There are currently 69 open connections.
    >
    > 13.8% us, 3.3% sy, 0.0% ni, 50.6% id, 31.2% wa, 0.1% hi, 0.8% si
    This is quite clear. Your CPUs spend 44% resp. 31% of their time
    waiting for some I/O device to get ready. You're certainly not CPU-
    bound but I/O-bound.

    Next step would be to find out, if you could spare I/O by making better
    use of the available memory.
    > Again, 10M of memory is all that remains free.
    You already know by now, that Linux uses excess memory to buffer I/O
    to the mass storage. So in fact the memory *is* used for good. However
    the buffer cache of Linux caches *all* active pages, that is

    - text segments (code) of running applications
    - all kinds of open files, including
    * MyISAM datafiles
    * MyISAM indexfiles

    So after all MyISAM index data is buffered twice, first in MySQLs
    key_buffer, second in Linux' page cache. MyISAM data files are cached
    only in the page cache. From the DBMS' standpoint that's OK, index
    data is (should be) dramatically more often accessed than row data.

    However this may not be true for certain types of workload. If you do
    lots of INSERTs and/or UPDATEs, you clearly have to a) write row data
    to disk, b) update index data. This puts more stress on the I/O system.


    So far for theory. Back to practice:


    It's possible that your I/O subsystem is just too slow. Means you need
    faster hard disks, preferrably in RAID configuration. You see this
    problem only with high data volume because the page cache reduces I/O
    demands for lower data volume.

    OTOH it's still possible that index operations slow you down. To
    evaluate this, you should look at server statistics:

    SHOW STATUS LIKE 'key_read%'

    This gives you 'Key_read_requests' and 'Key_reads'. You can calculate
    the cache-hit-ratio for the key_buffer as

    100% * (1 - Key_reads/Key_read_requests)

    For a properly configured key_buffer you will see 95% and more. If your
    key_buffer starts to become too small, it will drop. You then have to
    increase your key_buffer. In your case that would include to upgrade
    your operating system to 64 bits.


    HTH, 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. How to display limited number of rows of table indatabase
    By cgacfox in forum Coldfusion Database Access
    Replies: 12
    Last Post: March 10th, 08:54 PM
  2. Replies: 0
    Last Post: September 16th, 04:37 PM
  3. Replies: 0
    Last Post: September 10th, 10:49 PM
  4. Replies: 0
    Last Post: September 10th, 05:33 PM
  5. Replies: 0
    Last Post: September 10th, 05:29 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