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!
Slow database on dual query at the same table
Moderator: General Moderators
feyd | Please use
and the table structure
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]
Thanks for your replies,
here are the conflicting sql queriesCode: 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']}'"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;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]I don't think these query will be causing you any problem in terms of speeed.
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 ....
Code: Select all
"SELECT count(*) FROM {$this->config['mysql']['table']} WHERE {$this->config['mysql']['hash']}='{$tor['hash']}'"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 ....