MySQL Performance: MySQL => MySQL large table update/insert

MySQL large table update/insert [message #1581]

Thu, 09 August 2007 11:44

Leppy
Messages: 1
Registered: August 2007

Junior Member

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/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=500
set-variable = thread_cache_size=150
set-variable = table_cache=250
set-variable = query_cache_size=40M
set-variable = read_rnd_buffer_size=6M
set-variable = key_buffer_size=512M
set-variable = tmp_table_size=256M
set-variable = wait_timeout=60
#log=/mt/mysql_query.log
old-passwords
#log-bin
#server-id=1
#log-warnings
log-slow-queries=/var/log/mysql/slow.log

[mysql.server]
user=mysql
#basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-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-

[Updated on: Thu, 09 August 2007 17:33]

Report message to a moderator