dba Needed

[jonnyboy]

nZEDb uses a lot of queries and we could use a good dba to help us improve the performance of the queries and the indexes. If you have the time and could help us, we would definitely appreciate.

thank you

[extide]

So, you can use the EXPLAIN command to see how a query is being executed, and see if/what indexes it is using on each lookup. I went ahead and added like 3 or 4 indexes based on what I was seeing in the slow query log and the EXPLAIN thingy and got rid of most of the entries to the slow query log, which I have set at 1s atm.

So, I can't remember exactly which indexes I added, so here is a dump of all of the indexes I have right now. Maybe I will have a look at the source and see if I can optimize any of the statements. But as long as nothing is showing up in my slow query log (1-second timeout) then there probably isn't much that needs to be done.

I have about 24m rows in my parts table, just to give you an idea on the DB's size, and it is running on a HP Cloud VM with 2 cpu's and 4GB of ram (+swapfile). So the box is pretty overloaded by this DB but it actually seems to run it ok.

EDIT: Looks like I am still getting quite a few slow queries, but for some reason it wasnt logging them until I restarted mysqld. I will keep working on this but at some point adding more indexes will be counter productive.

Code: [Select]

CREATE TABLE `anidb` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `anidbID` (`anidbID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `animetitles` (
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `binaries` (
  PRIMARY KEY (`ID`),
  KEY `ix_binary_binaryhash` (`binaryhash`),
  KEY `ix_binary_partcheck` (`partcheck`),
  KEY `ix_binary_collection` (`collectionID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3514427 ;

CREATE TABLE `binaryblacklist` (
  PRIMARY KEY (`ID`),
  KEY `ix_groupname` (`groupname`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=100000 ;

CREATE TABLE `bookinfo` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=900 ;

CREATE TABLE `category` (
  PRIMARY KEY (`ID`),
  KEY `ix_category_status` (`status`),
  KEY `ix_parentid` (`parentID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=100000 ;

CREATE TABLE `collections` (
  PRIMARY KEY (`ID`),
  KEY `fromname` (`fromname`),
  KEY `date` (`date`),
  KEY `groupID` (`groupID`),
  KEY `ix_collection_filecheck` (`filecheck`),
  KEY `ix_collection_dateadded` (`dateadded`),
  KEY `ix_collection_collectionhash` (`collectionhash`),
  KEY `ix_collection_releaseID` (`releaseID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=237290 ;

CREATE TABLE `consoleinfo` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=139 ;

CREATE TABLE `content` (
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

CREATE TABLE `forumpost` (
  PRIMARY KEY (`ID`),
  KEY `parentID` (`parentID`),
  KEY `userID` (`userID`),
  KEY `createddate` (`createddate`),
  KEY `updateddate` (`updateddate`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

CREATE TABLE `genres` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=172 ;

CREATE TABLE `groups` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `name` (`name`),
  KEY `active` (`active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11358 ;

CREATE TABLE `menu` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=22 ;

CREATE TABLE `movieinfo` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `imdbID` (`imdbID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1928 ;

CREATE TABLE `musicinfo` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1257 ;

CREATE TABLE `nzbs` (
  PRIMARY KEY (`message_id`),
  KEY `ix_partnumber` (`partnumber`),
  KEY `ix_collectionhash` (`collectionhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `partrepair` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_partrepair_numberID_groupID` (`numberID`,`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `parts` (
  PRIMARY KEY (`ID`),
  KEY `binaryID` (`binaryID`),
  KEY `ix_parts_number` (`number`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=186072997 ;

CREATE TABLE `predb` (
  PRIMARY KEY (`ID`),
  KEY `ix_predb_title` (`title`),
  KEY `ix_predb_nfo` (`nfo`(255)),
  KEY `ix_predb_predate` (`predate`),
  KEY `ix_predb_adddate` (`adddate`),
  KEY `ix_predb_source` (`source`),
  KEY `ix_predb_md5` (`md5`),
  KEY `ix_predb_releaseID` (`releaseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `releaseaudio` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `releaseID` (`releaseID`,`audioID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `releasecomment` (
  PRIMARY KEY (`ID`),
  KEY `ix_releasecomment_releaseID` (`releaseID`),
  KEY `ix_releasecomment_userID` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `releaseextrafull` (
  PRIMARY KEY (`releaseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `releasefiles` (
  PRIMARY KEY (`ID`),
  KEY `ix_releasefiles_releaseID` (`releaseID`),
  KEY `ix_releasefiles_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `releasenfo` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_releasenfo_releaseID` (`releaseID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27364 ;

CREATE TABLE `releases` (
  PRIMARY KEY (`ID`),
  KEY `ix_releases_adddate` (`adddate`),
  KEY `ix_releases_postdate` (`postdate`),
  KEY `ix_releases_categoryID` (`categoryID`),
  KEY `ix_releases_rageID` (`rageID`),
  KEY `ix_releases_imdbID` (`imdbID`),
  KEY `ix_releases_guid` (`guid`),
  KEY `ix_releases_nzbstatus` (`nzbstatus`),
  KEY `ix_release_name` (`name`),
  KEY `ix_releases_relnamestatus` (`relnamestatus`),
  KEY `ix_releases_passwordstatus` (`passwordstatus`),
  KEY `ix_releases_dehashstatus` (`dehashstatus`),
  KEY `ix_releases_reqidstatus` (`reqidstatus`) USING HASH,
  KEY `ix_releases_nfostatus` (`nfostatus`) USING HASH,
  KEY `ix_releases_musicinfoID` (`musicinfoID`),
  KEY `ix_releases_consoleinfoID` (`consoleinfoID`),
  KEY `ix_releases_bookinfoID` (`bookinfoID`),
  KEY `ix_releases_haspreview` (`haspreview`) USING HASH
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=131505 ;

CREATE TABLE `releasesubs` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `releaseID` (`releaseID`,`subsID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `releasevideo` (
  PRIMARY KEY (`releaseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `site` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `setting` (`setting`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=111 ;

CREATE TABLE `tmux` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `setting` (`setting`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=65 ;

CREATE TABLE `tvrage` (
  PRIMARY KEY (`ID`),
  KEY `ix_tvrage_rageID` (`rageID`),
  KEY `ix_releasetitle` (`releasetitle`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11099 ;

CREATE TABLE `tvrageepisodes` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `rageID` (`rageID`,`fullep`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6493 ;

CREATE TABLE `upcoming` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `source` (`source`,`typeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

CREATE TABLE `usercart` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_usercart_userrelease` (`userID`,`releaseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `userdownloads` (
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `userexcat` (
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_userexcat_usercat` (`userID`,`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `userinvite` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `usermovies` (
  PRIMARY KEY (`ID`),
  KEY `ix_usermovies_userID` (`userID`,`imdbID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `userrequests` (
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE `userroles` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

CREATE TABLE `users` (
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

CREATE TABLE `userseries` (
  PRIMARY KEY (`ID`),
  KEY `ix_userseries_userID` (`userID`,`rageID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;


[kevin123]

Thanks, I added those indexes.

The queries themselves would need to be tweaked, someone helped us with a few of them but haven't seen him in a while unfortunately.

[insta]

I tweaked some on a local config and got some wicked speed improvements.  I'll create a new thread and update this post.

[nuthead]

I'm no DBA but I have a lot of mysql experience, I've forked the (dev) repo and I'll have a look to see what can be done with some of the slower queries...

[jonnyboy]

Cool, any help would be appreciated.

[nzeLover]

@nuthead - looking forward to see, what you come up with - thanks in advance from me..