innodb really needed?
Hello Una team,
I would like to learn if innodb engine is really needed for Una?
All Una tables seems like myisam.
İn mysqltuner innodb caches were not being used at all.
İn my test servers with mariadb I disabled unnadb engine completely and enabled Aria (which is a developed version of myisam engine).
The page speed increased 25-30 percent.
And in another test I converted all Una tables to innodb and with innodb engine and Mariadb and with all recommended settings of innodb, page load started to take double time...
(As these tests are targeting to reflect CPU usage I am using test systems to have the bottleneck on CPU usage.)
So the question is do we really need innodb engine for Una to function?
-
- · unknown
- ·
Comment by unknown is hidden. -
-
·
Alex T⚜️
- ·
It's possible to use InnoDB with UNA, it's functioning better in hight concurrency, but some server side tuning is needed to avoid deadlocks.
First set (or add) the following line into inc/header.inc.php
define('BX_DATABASE_ENGINE', 'INNODB'); ///< db engine
after
define('BX_DATABASE_NAME', 'xxxxxx'); ///< db name
Then the following server side settings for MySQL is needed in [mysqld] section:
innodb_stats_on_metadata = off innodb_buffer_pool_instances = 4 # number of CPUs innodb_buffer_pool_size = 4096M # ~80% of available RAM transaction_isolation = READ-COMMITTED # or tx_isolation = READ-COMMITTED lock_wait_timeout = 4 innodb_lock_wait_timeout = 4 innodb_rollback_on_timeout = 1 binlog_format = MIXED innodb_log_file_size = 400M
-
·
Alex T⚜️
-
- · Cem
-
·
In reply to Alex T⚜️
- ·
Alex can we completely disable the innodb engine as currently it's not being used and consuming an important fraction of the sources.
I think it's only exclusively needed if Una is using/will use transactional procedures.
Actually I disabled it and as I explained above CPU usage is good now.
-
-
·
Alex T⚜️
-
·
In reply to Cem
- ·
Yes, you can remove InnoDB from MySQL if you aren't using it.
-
·
Alex T⚜️
-
- · Kyle
-
·
In reply to Alex T⚜️
- ·
Would we need to do anything MySQL side or just do the following you mentioned. Will this cause it to auto convert existing tables or would we need to run something like this:
SET @DATABASE_NAME = 'name_of_your_db';
SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM information_schema.tables AS tb
WHERE table_schema = @DATABASE_NAME
AND `ENGINE` = 'MyISAM'
AND `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;Any extra information you can share and even risks, because like me I would be doing this on a live site and my database knowledge is like a rookie still :P ? Thank you ahead :)
-
- · Kyle
-
·
In reply to Kyle
- ·
Found Baloo's post on this and some similar questions. It would be awesome to make one big article on this in one place. Maybe in the future. I guess to be on the safe side going to do like I did to fix Froala. Download SQL, replace all MyIsam with InnoDB and re upload SQL and pray it all works.
-
- · Cem
-
·
In reply to Kyle
- ·
Kyle better to work for experiments on a trial site. You may even do it in your desktop computer ;)
-
- · Kyle
-
·
In reply to Cem
- ·
I'm still between Maria DB and updating. Did you have to make any big changes before upgrading to mariadb. I also seen your post there about Inoodb not being all that good, If I could just update to Maria DB and have less of a hassle that may be the best route? Was any table conversion needed especially for Aria? From what I have seen with Maria DB so far is it may be the best route based on my research.
-
- · Cem
-
·
In reply to Kyle
- ·
İf the server resources are very limited, go to Mariadb and disable inno, enable aria engine as described in previous posts.
İf your site is/ will be very active and there are enough server resources innodb is good.
But first thing first setup a trial server :)
-
- · Kyle
- ·
Using
#INNODB with MySQL So what I did so if anyone reads this in the future. I exported my SQL (Export Tab) from my database in PHPMyADMIN and then open it with my Notepad++ or just regular notepad works, but takes longer to load. I did a search for MyISAM and replaced all with INNODB. I then imported (Import Tab) and overwrote my tables in PHPMYADMIN. Then I did as Alex mentioned above adding the last section to my (my.cnf in my case) Mysql file and this is my end result. Alot of my changes I borrowed from others Configs here that worked great for them:performance-schema=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_stats_on_metadata = off
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size=134217728
transaction_isolation = READ-COMMITTED
lock_wait_timeout = 4
innodb_lock_wait_timeout = 4
innodb_rollback_on_timeout = 1
binlog_format = MIXED
innodb_log_file_size = 400M
max_allowed_packet=268435456
open_files_limit=10000
default-storage-engine=INNODB
innodb_file_per_table=1
key_buffer_size = 64M#max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 192
max_connections = 250
table_open_cache = 100000
max_heap_table_size = 128M
tmp_table_size = 1024M
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = on(This section I added especially for Froala in the past. So before you install Froala I would recommend this. Mine was already installed so I had to run a script to convert it. Took me a lot of Google searching)
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4If you have any other things I can trey with this MySQL CNF for better performance please share :)
-
Alex T⚜️ I made the changes mentioned above and have seen about a 30% improvement in page loads on my site. Will continue to assess as site activity picks up and report back. Thank you for this.
-
- · titanium
-
·
In reply to Alex T⚜️
- ·
When you say "server side settings for MYSQL in mysqld section" - do you mean that we just need to add those lines to /etc/mysql/my.cnf?
-
Yes, but MySQL recommends to use /etc/mysql.cnf https://dev.mysql.com/doc/refman/8.0/en/server-options.html
-
- · titanium
-
·
In reply to LeonidS
- ·
Yes, but MySQL recommends to use /etc/mysql.cnf https://dev.mysql.com/doc/refman/8.0/en/server-options.html
Ok thanks!