Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted June 9, 201410 yr 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
June 9, 201410 yr 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.
June 9, 201410 yr 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
June 9, 201410 yr Author 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
June 9, 201410 yr Author 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
June 9, 201410 yr 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.
June 22, 201410 yr 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.
Archived
This topic is now archived and is closed to further replies.