Questions regarding transition from MySQL 5.7 to MariaDB 10.5

Hi,

I’ve recently upgraded my journals from 2.4.8 to 3.3.17 and everything works great and looks amazing too, however the only problem that I’m facing is that MySQL processes are using up too much CPU in comparison to how much it used to before (huge difference!), which is ultimately slowing down all my websites.

My server is currently running on MySQL 5.7 & PHP 7.4, I’ve been wondering if I can upgrade it to MariaDB 10.5 and I wanted to know if there are any implications of doing so.

  1. Will my OJS work with the MariaDB version
  2. Anything I have to take care of before running the upgrade? Or, can I go ahead with it without a thought?
  3. Should I consider upgrading to just MySQL 8
  4. Lastly, can someone please share if the DB upgrade will be worth it in terms of performance and reducing load.

Looking forward to your replies.

Best Regards.

Hi @Jaimin,

Before you start diving into changing DBMSs, I’d recommend checking to make sure all tables use the same storage engine. It’s best if all use InnoDB, though MyISAM performs well too. However, if you have some tables in each, it’s possible to have very bad performance as a result. There are various threads on the forum about this; try searching for InnoDB.

Regards,
Alec Smecher
Public Knowledge Project Team

Hey Alec,

Thanks for your prompt reply.

I had converted all the tables in the database to InnoDB before upgrading to the latest OJS version.

Hi @Jaimin,

In that case I’d suggest searching around some of the threads in the forum on MySQL and performance. OJS 3.x is more complex than 2.x, so there will likely be a small performance hit, but it shouldn’t be anything prohibitive. Are you using a database server on the same machine as the web server, or are they on different machines?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

Yes, it exists on the same machine as the web server. One more unusual problem that I face on the web server is that, when I try to clear Data Cache & Template Cache - my journal will go into a forever loading phase without any errors.

It takes a lot of time on my localhost too, but eventually works normally after the first load (which takes around ~300 seconds).

Best Regards.

P.S. Out of curiosity, dropping my MySQL Server Configurations if someone can share if this is optimal or any tweaks that I can make to make it work efficiently. My server runs 12 OJS3 Journals.

bulk_insert_buffer_size 8388608
innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances 1
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size 25
innodb_change_buffering all
innodb_log_buffer_size 16777216
innodb_sort_buffer_size 1048576
join_buffer_size 262144
key_buffer_size 8388608
myisam_sort_buffer_size 8388608
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 131072
read_rnd_buffer_size 262144
sort_buffer_size 262144
sql_buffer_result OFF

My server’s hardware:
CPU - 2.5 GHz
CPU - 4 Cores
CPU - 8 Threads
CPU - 8 MB Cache
RAM - 8 GB