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-
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-
Comment