Page 1 of 1

Slow database on dual query at the same table

Posted: Tue Jul 31, 2007 5:48 pm
by aye
Hey,
I have two different scripts running on cron, that targets the same database table. If these two run simultaneously, a query that normally runs on no time at all may take half a minute to complete and the mysql cpu resource usage may increase to over 50%. Is this normal and can I do anything to make it run smoother (the scripts taking turns to run is not quite an option)?
Any help would be very much appreciated!

Posted: Tue Jul 31, 2007 6:27 pm
by nathanr
need to see the queries/php to help you..

Posted: Wed Aug 01, 2007 4:12 am
by tansoft
Please post the sql so that we can help you out .......

thanks

Posted: Wed Aug 01, 2007 5:24 am
by aye
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Thanks for your replies,
here are the conflicting sql queries

Code: Select all

"UPDATE {$this->config['mysql']['table']} SET {$this->config['mysql']['trackers']}='". mysql_real_escape_string( serialize( $trackerinfo ) ) ."', {$this->config['mysql']['seeders']}='$seeders', {$this->config['mysql']['leechers']}='$leechers' WHERE id='{$tor['id']}'"

Code: Select all

"SELECT count(*) FROM {$this->config['mysql']['table']} WHERE {$this->config['mysql']['hash']}='{$tor['hash']}'"
and the table structure

Code: Select all

CREATE TABLE `torrents` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default 'N/A',
  `cat` tinyint(4) NOT NULL default '0',
  `subcat` tinyint(4) NOT NULL default '0',
  `hash` varchar(40) NOT NULL default '',
  `trackers` mediumtext NOT NULL,
  `files` text NOT NULL,
  `size` bigint(20) unsigned NOT NULL default '0',
  `seeders` int(11) NOT NULL default '0',
  `leechers` int(11) NOT NULL default '0',
  `added` int(11) NOT NULL default '0',
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`),
  KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=95596 DEFAULT CHARSET=latin1;
as you can see, it is a torrent database with almost 100k entries (290MiB).


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Aug 01, 2007 5:42 am
by tansoft
I don't think these query will be causing you any problem in terms of speeed.

Code: Select all

"SELECT count(*) FROM {$this->config['mysql']['table']} WHERE {$this->config['mysql']['hash']}='{$tor['hash']}'"
This one is simple and runs in just few micro secs....


another query performance depends up on the number of time that executes.....

so to optimize your performance you have to optimize the full script or cron .... as these query are already optimized ..... the fact is that how we will be using them ....

Posted: Wed Aug 01, 2007 6:07 am
by aye
thanks for your reply,
i don't think there is anything wrong with the scripts, they are more or less as optimized as they can get. maybe the table structure could be improved (i'm as far off an expert you could get when it comes to index keys and such)?