sasiko Posted June 9, 2014 Share Posted June 9, 2014 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 Link to comment Share on other sites More sharing options...
stoo2000 Posted June 9, 2014 Share Posted June 9, 2014 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. Link to comment Share on other sites More sharing options...
Warzone Posted June 9, 2014 Share Posted June 9, 2014 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 Link to comment Share on other sites More sharing options...
sasiko Posted June 9, 2014 Author Share Posted June 9, 2014 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 Link to comment Share on other sites More sharing options...
GreenLinks Posted June 9, 2014 Share Posted June 9, 2014 First step should be converting your MyISAM tables to InnoDB imo Link to comment Share on other sites More sharing options...
sasiko Posted June 9, 2014 Author Share Posted June 9, 2014 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 Link to comment Share on other sites More sharing options...
Grumpy Posted June 9, 2014 Share Posted June 9, 2014 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. Link to comment Share on other sites More sharing options...
Dmacleo Posted June 22, 2014 Share Posted June 22, 2014 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.