Author Topic: dba Needed  (Read 23144 times)

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
dba Needed
« on: 2013-05-19, 04:54:47 PM »
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

Offline extide

  • Junior Indexer
  • **
  • Posts: 24
  • Helpful: +0/-0
Re: dba Needed
« Reply #1 on: 2013-07-31, 09:37:57 PM »
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 ;
« Last Edit: 2013-08-01, 11:12:41 AM by extide »

Offline kevin123

  • Overlord
  • ******
  • Posts: 456
  • Helpful: +49/-0
Re: dba Needed
« Reply #2 on: 2013-08-02, 11:13:57 AM »
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.

Offline insta

  • Junior Indexer
  • **
  • Posts: 6
  • Helpful: +1/-0
Re: dba Needed
« Reply #3 on: 2013-08-02, 11:37:14 PM »
I tweaked some on a local config and got some wicked speed improvements.  I'll create a new thread and update this post.

Offline nuthead

  • Junior Indexer
  • **
  • Posts: 22
  • Helpful: +1/-0
Re: dba Needed
« Reply #4 on: 2013-11-04, 11:35:56 AM »
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...

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
Re: dba Needed
« Reply #5 on: 2013-11-04, 03:34:54 PM »
Cool, any help would be appreciated.

Offline nzeLover

  • Decent Indexer
  • ***
  • Posts: 58
  • Helpful: +6/-0
Re: dba Needed
« Reply #6 on: 2013-11-05, 09:20:19 AM »
@nuthead - looking forward to see, what you come up with - thanks in advance from me..
// nzeLover
---
Using nZEDb with Ubuntu 14.04 LTS on 2 x dedicated servers.