XF 2.2 Is there any reason why MyISAM tables still exist within XenForo?

bzcomputers

Well-known member
There are still currently 4 tables that by default are set to MyISAM storage engine:
xf_search_index
xf_session
xf_session_admin
xf_session_install

Is there any reason a modern version of MySQL or MariaDB couldn't safely use the InnoDB storage engine for these tables without a noticeable performance change?
 
First of all, I'm no expert but after reading a lot about this I converted all the myisam tables to innodb a long time ago without any problems...and our db is close to 40gb with 167.000 users and 30 mln posts.
 
As far as I can tell these are left as non-innodb for legacy reasons, and there just hasn't been a drive the update them. Back in XF1, xf_search_index was MyISAM for full-text search support, which can now be done in InnoDB for supported MySQL versions.

You should also consider MEMORY tables as MyISAM, they suffer from the same full-table locks and joins to them invoke the multi-engine transaction manager. At least MEMORY tables don't crash and break the site, like using MyISAM for xf_session can when a crash happens.
 
I switched the 4 MyISAM tables listed above over to InnoDB a couple days ago now, on a live site. All has been running fine since then. Will look at changing the Memory tables over to InnoDB today and report back.
 
I switched the 4 MyISAM tables listed above over to InnoDB a couple days ago now, on a live site. All has been running fine since then. Will look at changing the Memory tables over to InnoDB today and report back.

I also switched the MyISAM over to InnoDB last week on a live site and no problems at all. I did notice the tables using memory - whats that all about ? Another thing to support legacy ?
 
I also switched the MyISAM over to InnoDB last week on a live site and no problems at all. I did notice the tables using memory - whats that all about ? Another thing to support legacy ?

I assume so, being Memory tables are less receptible to data loss during a crash than MyISAM tables.

As long as you have a correctly set innodb_buffer_pool_size then I don't see why the tables currently assigned to "Memory" storage engine shouldn't work just as good set as InnoDB storage engine.

These are the tables currently assigned to "Memory" storage engine:
xf_attachment_view
xf_session_activity
xf_thread_view

If you have Media Gallery:
xf_mg_album_view
xf_mg_media_view

If you have Resource Manager:
xf_rm_resource_view


...I'll know soon enough
 
We prefer MyIsam for the XF_search_index because this DB engine doesn't keep its tables entirely in RAM, so we can keep 16GB of RAM free. This is also not a performance problem when using an SSD.
All other tables use InnoDB and there has never been a problem with that either.
 
We prefer MyIsam for the XF_search_index because this DB engine doesn't keep its tables entirely in RAM, so we can keep 16GB of RAM free. This is also not a performance problem when using an SSD.
All other tables use InnoDB and there has never been a problem with that either.
I can confirm this, After converting search index table to InnoDB, my server hit the RAM limit and became not responsive. I had to convert it back to MyISAM
Wondering if there is any other table if I convert to MyISAM will reduce the server load
 
I can confirm this, After converting search index table to InnoDB, my server hit the RAM limit and became not responsive. I had to convert it back to MyISAM
Wondering if there is any other table if I convert to MyISAM will reduce the server load

It sounds like there is definitely something else going on there unrelated to your search_index table storage engine change. How big is your site? What kind of server are you on shared, dedicated? How much ram is allocated to your database (my.cnf settings)? How big was your search_index table before converting, then after? Did you rebuild your search_index through XenForo tools?

There really should not be much difference in storage size of a MyISAM table versus an InnoDB table and definitely not enough to hit any RAM limit if there wasn't already an unnoticed RAM (settings) issue already occurring.

With that being said, keep in mind also there is the option of using Elastic Search & Enhanced Search. If you have access to, or can install, Elastic Search and use XenForo Enhanced Search on your server the search_index table will be empty at all times.
 
Last edited:
It sounds like there is definitely something else going on there unrelated to your search_index table storage engine change. How big is your site? What kind of server are you on shared, dedicated? How much ram is allocated to your database (my.cnf settings)? How big was your search_index table before converting, then after? Did you rebuild your search_index through XenForo tools?

There really should not be much difference in storage size of a MyISAM table versus an InnoDB table and definitely not enough to hit any RAM limit if there wasn't already an unnoticed RAM (settings) issue already occurring.

With that being said, keep in mind also there is the option of using Elastic Search & Enhanced Search. If you have access to, or can install, Elastic Search and use XenForo Enhanced Search on your server the search_index table will be empty at all times.
Hello @bzcomputers , thank you for your response
My site having 60k + members and 15K threads. Daily logged in users is around 7K
My server is 4 core VPS with 4GB RAM and 40 to 50% of the RAM is always free
CPU usage is always 70%+ and hits 100% during peak hours
I use Redis cache for xenforo
Zend Opcache for php

search index was around 600MB before converting to InnoDB and after converting it went up to 900MB
Again came back to 600MB when I converted back to myISAM
I do not have the provision to install Elastic search at the moment
I will post my.cnf settings below . I will really appreciate it If you can suggest any optimization to reduce CPU usage

Code:
[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1
max_heap_table_size = 384M
port = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100G
table_open_cache = 192
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M
thread_cache_size = 96
query_cache_size = 128M
tmp_table_size = 384M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
#binlog_format=mixed
server-id = 1
expire_logs_days = 2
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M
 
@faker.king, it's hard to diagnose database issues without actually seeing the server database stats live, that being said there are a couple changes that should be safe and help with performance. I added notes to your settings below.

Code:
[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1 // query cache can actually slow your database down, the time required to get query cache and check permissions can take longer than just running a new query, it is also not supported in MySQL 8.0.3+
max_heap_table_size = 384M
port = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M // this only applies to MyISAM tables, remove if you have no MyISAM tables
max_allowed_packet = 100G // this is crazy high, since you are already distinguishing a separate value for mysqldump below this value should be something less than what is needed to dump your entire database
table_open_cache = 192
sort_buffer_size = 768K // this may be a little low, check your sort merge passes for your database; a setting of "1M" may help   
net_buffer_length = 4K // you are likely better off removing this, this database will typically be better at regulating the connection buffer itself
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M // this is only used when rebuilding indexes for MyISAM tables, can be removed if you have no MyISAM tables 
thread_cache_size = 96
query_cache_size = 128M // see query_cache_type note above
tmp_table_size = 384M // 
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
#binlog_format=mixed
server-id = 1
expire_logs_days = 2
#slow_query_log=1 // leave the slow query log off (=0) unless you are actively diagnosing something, otherwise it will just waste resources
#slow-query-log-file=/www/server/data/mysql-slow.log
#long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M // this is low, look at your databases's InnoDB stats, increasing to "768M" will likely perform much better
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 // setting to "2" reduces log flushing to disk to only once per second, this is more than safe for a forum, if you were a bank "2" would not be the recommended setting
innodb_lock_wait_timeout = 50 // this is the default setting and can be removed
innodb_max_dirty_pages_pct = 90 // this is the default setting and can be removed
innodb_read_io_threads = 4 // this is the default setting and can be removed
innodb_write_io_threads = 4 // this is the default setting and can be removed

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk] // if your're not running MyISAM tables this section can be removed
key_buffer_size = 128M 
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

Don't just blindly change anything be sure to look at your database stats first, so you can compare any changes to the stats after.
 
@faker.king, it's hard to diagnose database issues without actually seeing the server database stats live, that being said there are a couple changes that should be safe and help with performance. I added notes to your settings below.

Code:
[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1 // query cache can actually slow your database down, the time required to get query cache and check permissions can take longer than just running a new query, it is also not supported in MySQL 8.0.3+
max_heap_table_size = 384M
port = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M // this only applies to MyISAM tables, remove if you have no MyISAM tables
max_allowed_packet = 100G // this is crazy high, since you are already distinguishing a separate value for mysqldump below this value should be something less than what is needed to dump your entire database
table_open_cache = 192
sort_buffer_size = 768K // this may be a little low, check your sort merge passes for your database; a setting of "1M" may help  
net_buffer_length = 4K // you are likely better off removing this, this database will typically be better at regulating the connection buffer itself
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M // this is only used when rebuilding indexes for MyISAM tables, can be removed if you have no MyISAM tables
thread_cache_size = 96
query_cache_size = 128M // see query_cache_type note above
tmp_table_size = 384M //
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
#binlog_format=mixed
server-id = 1
expire_logs_days = 2
#slow_query_log=1 // leave the slow query log off (=0) unless you are actively diagnosing something, otherwise it will just waste resources
#slow-query-log-file=/www/server/data/mysql-slow.log
#long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M // this is low, look at your databases's InnoDB stats, increasing to "768M" will likely perform much better
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 // setting to "2" reduces log flushing to disk to only once per second, this is more than safe for a forum, if you were a bank "2" would not be the recommended setting
innodb_lock_wait_timeout = 50 // this is the default setting and can be removed
innodb_max_dirty_pages_pct = 90 // this is the default setting and can be removed
innodb_read_io_threads = 4 // this is the default setting and can be removed
innodb_write_io_threads = 4 // this is the default setting and can be removed

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk] // if your're not running MyISAM tables this section can be removed
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

Don't just blindly change anything be sure to look at your database stats first, so you can compare any changes to the stats after.
Thank you very much for your updates. Really appreciate that
I will look into this and update if there is any remarkable outcome
 
@faker.king, it's hard to diagnose database issues without actually seeing the server database stats live, that being said there are a couple changes that should be safe and help with performance. I added notes to your settings below.

Code:
[mysqld]
binlog_cache_size = 64K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1 // query cache can actually slow your database down, the time required to get query cache and check permissions can take longer than just running a new query, it is also not supported in MySQL 8.0.3+
max_heap_table_size = 384M
port = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M // this only applies to MyISAM tables, remove if you have no MyISAM tables
max_allowed_packet = 100G // this is crazy high, since you are already distinguishing a separate value for mysqldump below this value should be something less than what is needed to dump your entire database
table_open_cache = 192
sort_buffer_size = 768K // this may be a little low, check your sort merge passes for your database; a setting of "1M" may help  
net_buffer_length = 4K // you are likely better off removing this, this database will typically be better at regulating the connection buffer itself
read_buffer_size = 768K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M // this is only used when rebuilding indexes for MyISAM tables, can be removed if you have no MyISAM tables
thread_cache_size = 96
query_cache_size = 128M // see query_cache_type note above
tmp_table_size = 384M //
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-name-resolve
max_connections = 200
max_connect_errors = 100
open_files_limit = 65535

#log-bin=mysql-bin
#binlog_format=mixed
server-id = 1
expire_logs_days = 2
#slow_query_log=1 // leave the slow query log off (=0) unless you are actively diagnosing something, otherwise it will just waste resources
#slow-query-log-file=/www/server/data/mysql-slow.log
#long_query_time=3
#log_queries_not_using_indexes=on


innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 384M // this is low, look at your databases's InnoDB stats, increasing to "768M" will likely perform much better
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 // setting to "2" reduces log flushing to disk to only once per second, this is more than safe for a forum, if you were a bank "2" would not be the recommended setting
innodb_lock_wait_timeout = 50 // this is the default setting and can be removed
innodb_max_dirty_pages_pct = 90 // this is the default setting and can be removed
innodb_read_io_threads = 4 // this is the default setting and can be removed
innodb_write_io_threads = 4 // this is the default setting and can be removed

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk] // if your're not running MyISAM tables this section can be removed
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

Don't just blindly change anything be sure to look at your database stats first, so you can compare any changes to the stats after.
Can you advise on how to check mySQL Stats. Not an expert on this
 
Your created temporary tables to disk is very high. Almost all temporary tables are being written to disk instead of memory. You'll need to track down why that is happening. It appears your database has only been up and running for about 12 hours since last time it was restarted, be aware that this is a short amount of time to be making changes based off of it's stats - 24 hours plus of data, even longer is better to base database setting changes on.

As for phpmyadmin open it up:
1) Select "Status"
2) Select "all status variables". You will then be able to see all your database stats.
3) You can also easily filter them on "alert values" which will help you narrow down some possible database issues.


phpmyadmin.webp
 
Your created temporary tables to disk is very high. Almost all temporary tables are being written to disk instead of memory. You'll need to track down why that is happening. It appears your database has only been up and running for about 12 hours since last time it was restarted, be aware that this is a short amount of time to be making changes based off of it's stats - 24 hours plus of data, even longer is better to base database setting changes on.

As for phpmyadmin open it up:
1) Select "Status"
2) Select "all status variables". You will then be able to see all your database stats.
3) You can also easily filter them on "alert values" which will help you narrow down some possible database issues.


View attachment 268220
Thank you for your detailed explanation. I restarted database recently when I changed some of the parameters mentioned above and thats why the up time is less. Im attaching the phpmydmin stats below. And if thats not enough I will post the stats after 24 hours. For the time being I restored the default settings back. By the way im using MariaDB 10.3
Screenshot 2022-05-14 114622.jpg
 
Top Bottom