Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    7

    Unanswered: Large server config (maximize 2 gig ram), 3 days to build indexes!?!

    I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid cards, dell 4600's with single channel backplanes (new ones will have dual channel)

    All have 2 gig of ram, but I've never seen mysql use more than 300mb of ram.

    The servers handle huge loads, each day there are 30 1-2 gig files loaded into large tables, total mysql data size is 96 gig, the large tables are 2-6 gig.

    Inserts are done on dupe key ignore, this takes hours on the large files, it barely keeps up with input files.

    At the bottom of this post I've got the mysql.ini config lines, any sugestions are welcome, I'm already beyond the mysql "huge" sample they used to include in the program.

    Sample table that I load is as follows.

    each day I get 40 % new records on the text file, the input file is normally 20mb, once a week I get one that's 1-2 gig, these take all day to load.

    I need more multiple column indexes, as some querys return millions of rows that must be scanned, but the index size already exceeds the table size, and the combinations I need would result in an myi that's 5x larger than the data itself.

    Here's an example of the speed problem, the index was corrupt so I dropped all and recreated, rather than a myisam repair. I think 3 days is a little excessive for a table that's only 3.428 gig, index is 2.729 gig.

    mysql> alter table hood_stat add primary key (dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add index `niin` (`niin`), add index `stor` (`stor`), add index `dic` (`dic`), add index `ctasc` (`ctasc`);

    Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds
    Records: 45449534 Duplicates: 0 Warnings: 0


    CREATE TABLE `hood_stat` (
    `dic` char(3) NOT NULL default '',
    `fr_ric` char(3) NOT NULL default '',
    `niin` char(11) NOT NULL default '',
    `ui` char(2) NOT NULL default '',
    `qty` char(5) NOT NULL default '',
    `don` char(14) NOT NULL default '',
    `suf` char(1) NOT NULL default '',
    `dte_txn` char(5) NOT NULL default '',
    `ship_to` char(3) NOT NULL default '',
    `sta` char(2) NOT NULL default '',
    `lst_sos` char(3) NOT NULL default '',
    `esd` char(4) NOT NULL default '',
    `stor` char(3) NOT NULL default '',
    `d_t` char(4) NOT NULL default '',
    `ctasc` char(10) NOT NULL default '',
    PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ),
    KEY `don` (`don`),
    KEY `niin` (`niin`),
    KEY `stor` (`stor`),
    KEY `dic` (`dic`),
    KEY `ctasc` (`ctasc`)
    ) TYPE=MyISAM MAX_ROWS=1000000000 PACK_KEYS=1

    skip-locking
    set-variable= key_buffer=512M
    set-variable=join_buffer=512M
    set-variable= max_allowed_packet=256M
    set-variable= table_cache=512
    set-variable= sort_buffer=256M
    set-variable=tmp_table_size=400M
    set-variable= record_buffer=256M
    set-variable= thread_cache=8
    set-variable=myisam_sort_buffer_size=256M
    myisam-recover=BACKUP,FORCE
    set-variable=read_buffer_size=2M
    set-variable=interactive_timeout=7200
    set-variable=wait_timeout=7200
    log-bin
    server-id=1
    replicate-do-db=finlog
    set-variable=open-files-limit=500
    set-variable=table-cache=400
    Last edited by megadeth72; 06-29-04 at 15:42.

  2. #2
    Join Date
    Jun 2004
    Posts
    7
    PS, all run 4.0.16 on windows 2000

  3. #3
    Join Date
    Dec 2003
    Location
    Ogden Utah
    Posts
    34

    mysql

    What syntax are you using to load data in to the hood_syntax table? How come you are using so many primary keys?
    mvilla

  4. #4
    Join Date
    Jun 2004
    Posts
    7
    There is only one primary key, the fields used are what's required to keep duplicates out of the system.

    The create index command shouldnt take 3 days, mysql's performance is terrible with these large files

    Loading files into a temp table is fast

    I then do a delayed insert ignore into the main table, this is slow, very slow

    but not as slow as creating the keys, 3 days, that's pathetic, it's only a 3 gig table, that's nothing

  5. #5
    Join Date
    Jun 2004
    Posts
    7
    Bumped up a few settings

    delay_key_write=ALL
    key_buffer_size=1000M
    read_buffer_size=512M
    record_buffer=512M

    seems to help a little, but not much

    I cant tell if it's mysql that's the problem, or the hardware, Here's a screenshot of the disk IO, if I copy a file while mysql is doing the build index, the io shoots way up, which tells me, mysql is NOT maxing out the drives, and it's also not maxing out the memory.

    Unless it's doing lots and lots of seeks on the drive, which is harder to test using perfmon

    Disk IO screenshot

  6. #6
    Join Date
    Dec 2003
    Location
    Ogden Utah
    Posts
    34

    Disk Fragmentation

    I am probably 100% wrong but if your database design and syntax is solid then you might be experiencing disk fragmenation.

    http://osr5doc.ca.sco.com:457/OSAdmi....diskfrag.html
    mvilla

  7. #7
    Join Date
    Jun 2004
    Posts
    7
    I defragged our test server, and reran, no improvement

    it seems like it's a bug with large primary keys, I dont know of any other way to keep duplicates out of the database

  8. #8
    Join Date
    Jun 2004
    Posts
    7
    Nobody else knows?

    I'm thinking mysql just is not good for a large table environment, where users are allowed to query on whatever columns they need to.

    Querys are slow, loading data is slow

    I'm thinking going back to oracle is my only option at this point

  9. #9
    Join Date
    Apr 2004
    Posts
    12
    Quote Originally Posted by megadeth72
    Nobody else knows?

    I'm thinking mysql just is not good for a large table environment, where users are allowed to query on whatever columns they need to.

    Querys are slow, loading data is slow

    I'm thinking going back to oracle is my only option at this point
    I am not sure if it is helpful:
    to specify an index name in your query to force sql query analyzer using the right index like following:
    select * from table_a,table_b with (index(ix_index_1)) where ...
    I have a 4 G table, use this table hint can speed up execution.

  10. #10
    Join Date
    Aug 2004
    Posts
    4
    What is your website?

Posting Permissions

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