MySQL is slow to insert data inside large table

[Leppy]

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 = 50+ seconds).

 

My table (10 million rows):

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

[teng84]

if your inserting big data one by one it really takes allot oft time i guess there is no solution to that what i my case i insert data by group like first 100 then second 100 and i do this using php

[Leppy]

The data I am inserting is not big at all it only a set of number that I put in the database, would it be slow because the table is large?

[clearstatcache]

i guess yes...maybe ur table is too large....y noy modify ur table..how about instead of 1 large table make it 3 or more small tables...

[Leppy]

That's what I was thinking... Is it common practice to do something like that?

 

Maybe my table structure could be better... Since I create a row for every hour there is an action, would it be better if I create 1 row per day but have 24+ field (24 hours) in the table ?

 

Would I gain in performance? I would surely have a lot less rows in the table for starter...