Slow database on dual query at the same table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
aye
Forum Commoner
Posts: 25
Joined: Wed Apr 11, 2007 9:02 am

Slow database on dual query at the same table

Post 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!
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

need to see the queries/php to help you..
tansoft
Forum Newbie
Posts: 12
Joined: Sun Jul 29, 2007 1:04 am

Post by tansoft »

Please post the sql so that we can help you out .......

thanks
aye
Forum Commoner
Posts: 25
Joined: Wed Apr 11, 2007 9:02 am

Post 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]
tansoft
Forum Newbie
Posts: 12
Joined: Sun Jul 29, 2007 1:04 am

Post 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 ....
aye
Forum Commoner
Posts: 25
Joined: Wed Apr 11, 2007 9:02 am

Post 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)?
Post Reply