MariaDB / MySQL peformance

[benosman]

Hello again.

I realise this may be outside of your area of support, but its got me a bit stumped so i though i'd try.

I'm in the process of consolidating two vps (1 x apache & 1 mysql) into a larger Barracuda based vps.

I attempted to switch over a site database from the 768MB mysql vps to 1.5GB barracuda one and despite their being plenty of memory on the barracuda one and a more powerful CPU when I made the switch mysql took over all the CPU and it basically melted!

First I tried with the default mysql conf you ship with, then I tried one very close to what I use now and neither helped. I did leave it a while to see if it 'warmed up' but still no improvement.

The database is big and site is relatively busy but I don't understand why the less powerful vps manages better... :-s

[omega8cc]

Could you post your my.cnf file used on your small, working vps? Are you using InnoDB engine? What is the size of the database? Maybe there are some settings to tweak.

[benosman]

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

This was formally known as [safe_mysqld]. Both versions are currently parsed.

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/mysql/data
tmpdir = /tmp
skip-external-locking

max_user_connections = 50
max_connections = 50
max_connect_errors = 20
interactive_timeout = 200
wait_timeout = 900
connect_timeout = 20
key_buffer = 48M
key_buffer_size = 64M
max_allowed_packet = 64M
table_cache = 4000
thread_stack = 192K
thread_cache_size = 8
join_buffer = 2M
record_buffer = 1M
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M

* Query Cache Configuration

query_cache_limit = 32M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072

open_files_limit = 8196
myisam_sort_buffer_size = 64M

default-storage-engine = innodb

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

[benosman]

Could you post your my.cnf file used on your small, working vps? Are you using InnoDB
engine? What is the size of the database? Maybe there are some settings to tweak.

Its a Drupal 5 site with 264 tables, ~3million records and 1GB in size.

i don't use innodb for all tables, but i do for the major ones - sessions, watchdog, accesslog etc.

[omega8cc]

Since the example config supplied with Barracuda is not tuned for this size of database, I would recommend running those 3 scripts on Barracuda server to tune it better, since it obviously has allocated far less resources (for MyISAM) than it should with this database.

http://omega8.cc/dev/tunesql/tuning-primer.sh.txt
http://omega8.cc/dev/tunesql/mysqltuner.pl.txt
http://omega8.cc/dev/tunesql/mysqlreport.pl.txt

I believe you should particularly increase on Barracuda values related to MyISAM (it is set to use XtraDB/InnoDB by default and doesn't care about MyISAM much), so for sure watch there for:

key_buffer_size
key_buffer
max_allowed_packet
query_cache_size

Also, with this size of database, you should probably tweak:

myisam_sort_buffer_size

[benosman]

Thank you for the input, i will look into this tomorrow. I will also look to purge some of the tables like the session and perhaps convert more into innodb tables.

[benosman]

Ok, I ran the scripts and I tweaked some of those settings - despite the scripts saying things were mostly ok, I was still having problems with performance.

Even in InnoDB the sessions table had god a bit unwieldy, so after struggling for a while to remove the older entries I truncated it.

That improved matters, but finally today I converted a big batch of tables to innodb, including node_revisions which seemed to be a problematic one. Since then cpu usage has got down to a reasonable level despite it being the busiest part of the day for the site.

So thanks for the pointers :)

[omega8cc]

Use DB Tuner to get better results - http://drupal.org/project/dbtuner, and use Pressflow to avoid many GB of session table :)