Arch Linux

brando56894

Member

From: NYC

Registered: 2008-08-03

Posts: 681

MariaDB and Memcached/Performance Tuning

I have a MariaDB server running on my box that contains databases for XBMC, Amarok and NewzNab (Usenet Indexer). I'm a novice when it comes to MySQL since I barely know anything about it and just set it up and got it working by following the wiki articles on MariaDB and PhpMyAdmin. I'm trying to tune it for performance since it seems to be a little slow. I know memcached can speed up performance a bit but I'm not really sure on how to implement it other than installing the package for it. The Advisor in PhpMyAdmin also suggests that I index my tables, I read that indexing all tables in a specific database can actually slow things down, so how do I know which ones I should index?

The XBMC database is used to sync my library to the multiple XBMC clients I have throughout the house. I have a huge movie collection (around 250 movies) and far more TV shows (around 30-40 TV Shows, full series of each). Cleaning the library from within XBMC takes a few minutes and so does updating the library from within XBMC. I was wondering if I could do anything to speed these up a bit. I'm assuming that most of the queries below are from XBMC since we use that constantly and the NewzNab server is still in the testing phase so it doesn't really get used that much. I'm going to be messing with it while I'm at work so it'll probably change and I'll upload a new picture later of the query breakdown.

The NewzNab database stores a whole bunch of stuff, which I'm not really sure of since I'm pretty new to NewzNab also. I'm sure it does a lot more than XBMC since it has to index/store thousands of NZB files, among other things.

Here's my my.cnf config file

[client]
port		= 3306
socket	= /run/mysqld/mysqld.sock

[mysqld]
port = 3306
socket = /run/mysqld/mysqld.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 256K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin=mysql-bin
binlog_format=mixed
server-id	= 1
big-tables
#skip-locking

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Here's the current stats of my MariaDB server after running for 1.5 days with total network traffic being 1 GB (Sent 0.9 GB, Received 19.1 MB [542.1 KB/hour] )

Here's the specs of my server
Intel Core i7 950 @ 3.4 GHz
8 GB DDR3 1600 MHz in Triple Channel configuration [don't ask how 8GB is split into three I forget how it is lol]
128 GB Crucial M4 SSD attached via SATA3. Root partition is formatted with EXT4, /var/lib/mysql formatted with ext3

Can anyone offer suggestions for my setup?

Last edited by brando56894 (2013-06-18 15:39:13)