NewzNab/MySQL Optimizations

[brando56894]

I have everything running correctly I was just wondering if some MySQL gurus out there could give me some tips on how to best optimize my SQL database for NewzNab.

Here's the specs of my rig
Arch Linux 64 running MySQL, phpMyAdmin, Sick Beard, Couch Potato, SABnzbd, XBMC 12 and other various KDE apps because this is my machine and not just purely a server box

Core i7 950 @ 3.07 GHz (gonna OC it soon since I now have liquid cooling)
8GB DDR3 1600 MHz
/ mounted on a 128GB Crucial M4 SSD

my.cnf

Code: Select all

# MySQL config file for medium systems.

#

# This is for a system with little memory (32M - 64M) where MySQL plays

# an important part, or systems up to 128M where MySQL is used together with

# other programs (such as a web server)

#

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# http://dev.mysql.com/doc/mysql/en/option-files.html

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

 

# The following options will be passed to all MySQL clients

[client]

password =

port =

socket = /run/mysqld/mysqld.sock

 

# Here follows entries for some specific programs

 

# The MySQL server

[mysqld]

port = 3306

socket = /run/mysqld/mysqld.sock

datadir = /var/lib/mysql

skip-external-locking

#skip-name-resolve

key_buffer_size = 512M

max_allowed_packet = 10M #was 1M

table_open_cache = 512 #was 64

sort_buffer_size = 1024M #was 4M

net_buffer_length = 256K #was 8K

read_buffer_size = 512M #was 2M

read_rnd_buffer_size = 512M #was 8M

myisam_sort_buffer_size = 512M #was 8M

max_heap_table_size=2048M #was 16M

tmp_table_size=2048M

open_files_limit=2048 #was 1024

group_concat_max_len=8192 #was 1024

max_allowed_packet=12582912 #was 1048576

max_connections=350 #was set to 151

#interactve_timeout=30

wait_timeout=60 #was set to 28800

long_query_time=4 #was 10

thread_cache_size=50

slow_query-log = /var/log/mysql/mysql-slow.log

log-queries-not-using-indexes

log_output=TABLE

 

#skip-networking

bind-address = 192.168.1.114

 

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

 

# binary logging format - mixed recommended

binlog_format=mixed

 

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

 

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 1024M

#innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 64M

#innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit = 1

#innodb_lock_wait_timeout = 50

 

[mysqldump]

quick

max_allowed_packet = 32M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[myisamchk]

key_buffer_size = 128M #was 20M

sort_buffer_size = 256M #was 20M

read_buffer = 256M #was 2M

write_buffer = 256M #was 2M

 

[mysqlhotcopy]

interactive-timeout

I also seem to get this error a lot when running update_binaries:
PHP Warning: Error while sending QUERY packet. PID=26006 in /opt/nnplus/www/lib/framework/db.php on line 72

[inpheaux]

Ok, three things:

1) It looks like you're defining max_allowed_packet twice, once to 10M, once to 12582912. Nuke the second one, and try setting the first to 128M. That'll be overkill, but shouldn't cause a problem.

2) You'll likely want to bump your wait_timeout even higher. That number is in seconds, for local php debugging I tend to run with 3600, so MySQL has a whole 5 minutes to churn on stuff before it barfs and kills the connection.

3) Similar to the wait_timeout, look at setting connect_timeout to 3600.

Also, I don't know enough about NN internals to know if this is a big deal or not, but you've got all the innodb stuff commented out. Look into if any of Newznab's tables are innodb or not. innodb tends to have ridiculous perf benefits over MyISAM in nearly every use-case EXCEPT fulltext search, which should be used sparingly.

[brando56894]

Hey thanks for the info

I have been messing around with declaring system variables so much I must have put it in there twice lol I had been switching back and forth between MyISAM and InnoDB because originally the InnoDB stuff was commented out and I left it that way, then I decided to enable it for a while but my number of locked tables (?) was very high (in the thousands) so I decided to disable InnoDB and go back to MyISAM.

The type for all of my tables in my Newznab database reads as MyISAM, but yet at the bottom where it tallies all of the tables it says the type is InnoDB Is there anything special I need to do to have them set as InnoDB other than uncommenting the lines in my.cnf?

When I start the mysql daemon I get this output, so I'm assuming all is well?

Code: Select all

[bran@ra ~]$ sudo mysqld --user=mysql
130102  5:47:11 InnoDB: The InnoDB memory heap is disabled
130102  5:47:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130102  5:47:11 InnoDB: Compressed tables use zlib 1.2.7
130102  5:47:11 InnoDB: Initializing buffer pool, size = 2.0G
130102  5:47:11 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not exist:
InnoDB: a new database to be created!
130102  5:47:11  InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
130102  5:47:11  InnoDB: Log file /var/lib/mysql/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
130102  5:47:14  InnoDB: Log file /var/lib/mysql/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
130102  5:47:17  InnoDB: Waiting for the background threads to start
130102  5:47:18 InnoDB: 1.1.8 started; log sequence number 0
130102  5:47:19 [Note] Server hostname (bind-address): '192.168.1.114'; port: 3306
130102  5:47:19 [Note]   - '192.168.1.114' resolves to '192.168.1.114';
130102  5:47:19 [Note] Server socket created on IP: '192.168.1.114'.
130102  5:47:19 [Note] Event Scheduler: Loaded 0 events
130102  5:47:19 [Note] mysqld: ready for connections.
Version: '5.5.29-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Source distribution