GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL large table update/insert

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL large table update/insert

    MySQL version: 5.0.45

    My problem: My update/insert queries are slow which makes large amount of data to be insert taking forever (~5000 row = 30+ seconds).

    My table:

    `id` int(11) unsigned NOT NULL auto_increment, `gid` int(11) unsigned NOT NULL default '0', `tid` int(11) unsigned NOT NULL default '0', `d` date NOT NULL default '0000-00-00', `h` time NOT NULL default '00:00:00', `rh` smallint(11) unsigned NOT NULL default '0', `uh` smallint(11) unsigned NOT NULL default '0', `rc` smallint(11) unsigned NOT NULL default '0', `uc` smallint(11) unsigned NOT NULL default '0', `rj` smallint(11) unsigned NOT NULL default '0', `uj` smallint(11) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `d` (`d`), KEY `gid` (`gid`), KEY `tid` (`tid`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10477780 ;


    I've tried to delete the indexes but it is making it worst, I've tested without any index, with 1 index and with 2 indexes and here are the results:

    With only primary key: 2.2648708820343 seconds
    With primary + 1 index (d): 0.03847599029541 seconds
    with primary + 2 index (gid): 0.02488112449646 seconds
    (with the 3rd index the time is almost the same as 2.)

    Shouldn't be suppose to be the opposite?


    I recently move my database to a standalone server so no other process than mysql should be using the CPU and the hard drive. I did not really optimize my mysql configuration because I do not know what I need to change in order to optimize the performance.

    [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockset-variable = max_connections=500set-variable = thread_cache_size=150set-variable = table_cache=250set-variable = query_cache_size=40Mset-variable = read_rnd_buffer_size=6Mset-variable = key_buffer_size=512Mset-variable = tmp_table_size=256Mset-variable = wait_timeout=60#log=/mt/mysql_query.logold-passwords#log-bin#server-id=1#log-warningslog-slow-queries=/var/log/mysql/slow.log[mysql.server]user=mysql#basedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid



    I'm using the following code (in PHP) in order to insert my data inside mysql, fairly simple query...

    $query=mysql_query("UPDATE tableA SET rh=rh+1 WHERE gid={$gid} AND tid={$tid} AND d='{$d}' AND h='{$h}:00:00'");$query =mysql_affected_rows(); if(!$_query){ mysql_query("INSERT INTO tableA (`gid`,`tid`,`d`,`h`,`rh`,`uh`) VALUES ({$gid},{$tid},'{$d}','{$h}:00:00',1,0)");}


    I tried running my script on the same server as the mysql server but it did not change anything.

    Any suggestion would be appreciated! Thank you!

    Leppy-

  • #2
    The more indexes you have the slower inserts would be
    However you need to do updates as well and these need index to run efficiently. One index.

    As you have:
    gid={$gid} AND tid={$tid} AND d='{$d}' AND h='{$h}:00:00'")

    you need key on (gid,tid,d,h) besides primary key for optimal performance

    Comment

    Working...
    X