mysql is using too much resources

[sasiko]

so i have mysql 5.6 as ive just upgraded from 5.5 due to constant table locks and table crashes that i have to always repair every 5 times per week. the forum db is overall 400mb nothing that big.

ive tried using mysqltuner but their recommendation isnt helping since my tables (ipb forum by the way) keep crashing and mysql uses like 200-300% cpu every once in a minute constantly.

server

1245 v2, 32gb ram and currently only using 4 gb ram

my cnf

[mysqld]
local-infile=0
interactive_timeout=3
wait_timeout=6
connect_timeout=20
max_length_for_sort_data = 2048
max_sort_length = 2048
innodb_buffer_pool_size = 3G
query_cache_size = 420M
query_cache_limit = 1M
max_connections = 250
key_buffer_size = 410M
sort_buffer_size = 30M
read_rnd_buffer_size = 10M
join_buffer_size = 4M
max_allowed_packet = 16M
tmp_table_size = 35M
max_heap_table_size = 35M

[stoo2000]

so i have mysql 5.6 as ive just upgraded from 5.5 due to constant table locks and table crashes that i have to always repair every 5 times per week. the forum db is overall 400mb nothing that big.

ive tried using mysqltuner but their recommendation isnt helping since my tables (ipb forum by the way) keep crashing and mysql uses like 200-300% cpu every once in a minute constantly.

server

1245 v2, 32gb ram and currently only using 4 gb ram

my cnf

There were many changes to the Performance Schema in 5.6, I have seen quite a few posts online saying that this is causing CPU spikes on some servers.

http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

May be worth disabling it and seeing if your CPU spikes stop.

[Warzone]

Better to use PHP 5.3 version instead - i had 5.5 but i couldt have it because website went sloooooow, so i asked host to change back to 5.3

[sasiko]

php isnt the issue here, its mysql.

anyway i want to move mysql tmp files to ram by adding the tmp command on my.cnf but i dont know what it is since

tmpdir =

that doesnt work in mysql 5.6

devtmpfs 16G 196K 16G 1% /dev
tmpfs 16G 0 16G 0% /dev/shm

[GreenLinks]

First step should be converting your MyISAM tables to InnoDB imo

[sasiko]

thats something ipb staff recommend against, only convert certain tables to innodb.

anyway would appreciate if anyone could point me to the right direction of mounting mysql tmp to my ram

[Grumpy]

thats something ipb staff recommend against, only convert certain tables to innodb.

IPB is tested against myisam more than innodb as I'm aware. So, it's the basic go-to choice for IPB. But it seems like general consensus among the users here for high performance setup all use innodb, or at least partially innodb. Main difference in result will be that full-text search will have to get disabled if you use innodb on certain tables. But you'd likely be installing sphinx anyway for a high perf setup which makes the absence of full-text search neglectable.

If you're hitting table locks, innodb is really the way to go. It may also help with your broken tables as innodb as is more resilient to corruption (actually just identifying as corruption -- these are insta-solved by running repair) than myisam.

[Dmacleo]

was wondering about innodb myself, was looking today and I see I have mix of innodb and myisam.

not sure how that happened, are there any known tables that would have issues with innodb?

I do use sphinx on my end.

seeing as my backup db is myisam and live is mix I suspect the utf converter may have done this? backup was done moments before converter run.