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-