Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Aug 2007
    Location
    Germany
    Posts
    22
    Thanks
    4
    Thanked 2 Times in 2 Posts

    MySQL simple INSERT too slow, help!

    An INSERT into the following table takes appr. 0,04 sek:

    CREATE TABLE `tbl_products_offers_format_test` (
    `fld_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `fld_offer_id` INTEGER(11) DEFAULT NULL,
    `fld_format_id` INTEGER(11) DEFAULT NULL,
    `fld_format` VARCHAR(35) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`fld_id`),
    UNIQUE KEY `fld_offer_id` (`fld_offer_id`, `fld_format`)
    )ENGINE=InnoDB
    AUTO_INCREMENT=197520 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

    server:
    Phenom X3 |64 Triple Core 2,3 GHz
    Ubuntu Linux 10.04
    Linux 2.6.32-21-server on x86_64
    RAM 4GB
    Swap Partition 4 GB

    my.cnf:
    -------------------------------------------------
    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    [mysqld]
    #
    # * Basic Settings
    #

    user = mysql
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    basedir = /usr
    datadir = /var/lib/mysql
    tmpdir = /tmp

    skip-external-locking

    #
    # * Fine Tuning
    #
    key_buffer = 256M #s 16M
    max_allowed_packet = 2M #s 16M
    thread_stack = 192K
    thread_cache_size = 8
    key_buffer_size = 256M
    myisam_sort_buffer_size = 64M
    read_rnd_buffer_size = 4M
    read_buffer_size = 4M
    sort_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    tmp_table_size = 512M #64M
    max_heap_table_size = 512M #64M
    join_buffer_size = 2M

    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover = BACKUP

    max_connections = 50
    table_cache = 256 #s 64
    thread_concurrency = 8 #s was commented

    #
    # * Query Cache Configuration
    #
    query_cache_limit = 16M #s 1M
    query_cache_size = 16M #s 16M
    #

    # * Logging and Replication
    #
    log_error = /var/log/mysql/error.log

    # Here you can see queries with especially long duration
    log_slow_queries = /var/log/mysql/mysql-slow.log
    long_query_time = 3

    #
    # * InnoDB

    default-storage-engine = INNODB
    innodb_data_home_dir = /var/lib/mysql/innodb
    innodb_data_file_path = ibdata1:10M:autoextend:max:4000M
    innodb_buffer_pool_size = 512M
    innodb_additional_mem_pool_size = 32M
    innodb_log_file_size = 256M #128M
    innodb_log_buffer_size = 8M
    innodb_lock_wait_timeout = 20
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 1
    innodb_thread_concurrency = 4
    #innodb_flush_method = O_DIRECT
    -------------------------------------------------


    on my local server with default mysql setting on windows XP it takes only 0,001 sek for an INSERT.

    I tried:
    to tune MYSQL config as well as to use default parameters, no luck 0,04 is the fastest insert. Hard disk is according to support ok. Tried to restart the whole server to empty the swap, also no luck.

    PLESE HELP! I have no idea why it takes on the web server 40 times more time for executing a simple INSERT.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    There's a possible clue here:
    Code:
    ... )ENGINE=InnoDB AUTO_INCREMENT=197520 ...
    That says to me that *probably* the table already has about 200,000 rows. And so it would make sense that adding a new row would take longer than when the table has zero rows.

    Now...40 times as long? Yes, that does seem way excessive.

    But what else is happening on that server?? Maybe it is also serving 20 other users who are all performing "heavy" SQL operations. Or maybe it's also a web server with many users requesting HTML pages. Or....

    It's pretty hard to compare your local machine--which is almost surely doing nothing else but servicing your one query--with a "loaded" server machine.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Aug 2007
    Location
    Germany
    Posts
    22
    Thanks
    4
    Thanked 2 Times in 2 Posts
    thank you Old Pedant for a rush answer, buy no of the reasons could be an answer for my problem:

    1) web server has no load, it is my testing server and only a few persons now about its existing

    2) the table is empty on local mashine and web service, and i start to fill the emptu table

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    I'm afraid I'm out of ideas. I use MySQL on Windows 7 as well as on 4 or 5 Linux machines, and the Linux machines have a large variation in their hardware capabilities (from 1 cpu with 1GB RAM to 8 cpus with 8GB RAM) and I don't see *THAT* much difference between any of the machines. At least not when they aren't loaded.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Aug 2007
    Location
    Germany
    Posts
    22
    Thanks
    4
    Thanked 2 Times in 2 Posts
    that is why i am also confused, 40 times difference is hard to explain. I also asked the support of the data center, but they are not much help(

    I have made the following experiment - i've comverted the table from innodb to myisam and .. it run much much faster, just with normal speed expected to be, even more then 40 times faster. So the problem is located - it is InnoDB

    But what could be wrong with my innodb configuration?
    Last edited by alex375; 09-11-2010 at 07:37 AM.


  •  

    Posting Permissions

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