InnoDB or MyISAM on MariaDB 10.4 for Shared Hosting

Desperados

Verified User
Joined
Jan 2, 2017
Messages
31
Hello,

we have a server for shared hosting, about 600 account

for mariadb 10.4 wich database engine is best ?

innodb or myisam ?

on old server we are using myisam.

we are doing setup new server and transfer accounts to new server.
 

wattie

Verified User
Joined
May 31, 2008
Messages
981
Location
Bulgaria
Briefly: use InnoDB for your projects! It is the default and superior storage engine.

InnoDB is superior in all aspects. Since MariaDB 5.5 and MySQL 5.5 it has been the default option for both systems. It is crash safe, it supports transactions, integrity checks, etc. Some old tutorials mocks it for not having full text search but this is a thing from the past - it have it now. It supports everything of MyISAM + much more.

MyISAM is NOT crash safe and it is NOT transactional. It does not have foreign keys and it does no integrity checks. It used to be faster in the past but now it is true only when the database is used mostly for reading and rare writing. In the past it was always faster than InnoDB but this is no longer the case. InnoDB uses row-level locking which is much better than the table-level locking of MyISAM when doing writes.

If (for some reason) you do need a non-transactional storage engine, you may consider using the Aria storage engine. It is superior than MyISAM in almost all aspects (it uses more disk space and memory but it is crash safe).

Summary:

1. For general purpose, use InnoDB;
2. If you have rare writes and lots of reads, you can use Aria for speed benefit;
3. Do not use MyISAM unless you are very resource limited and you have rare writes and lots of reads (eg. you can't spend some disk space for Aria - around 1G for its logs if I remember correctly).

My opinion: go with InnoDB and don't think about it :)
 
Last edited:

Desperados

Verified User
Joined
Jan 2, 2017
Messages
31
Briefly: use InnoDB for your projects! It is the default and superior storage engine.

InnoDB is superior in all aspects. Since MariaDB 5.5 and MySQL 5.5 it has been the default option for both systems. It is crash safe, it supports transactions, integrity checks, etc. Some old tutorials mocks it for not having full text search but this is a thing from the past - it have it now. It supports everything of MyISAM + much more.

MyISAM is NOT crash safe and it is NOT transactional. It does not have foreign keys and it does no integrity checks. It used to be faster in the past but now it is true only when the database is used mostly for reading and rare writing. In the past it was always faster than InnoDB but this is no longer the case. InnoDB uses row-level locking which is much better than the table-level locking of MyISAM when doing writes.

If (for some reason) you do need a non-transactional storage engine, you may consider using the Aria storage engine. It is superior than MyISAM in almost all aspects (it uses more disk space and memory but it is crash safe).

Summary:

1. For general purpose, use InnoDB;
2. If you have rare writes and lots of reads, you can use Aria for speed benefit;
3. Do not use MyISAM unless you are very resource limited and you have rare writes and lots of reads (eg. you can't spend some disk space for Aria - around 1G for its logs if I remember correctly).

My opinion: go with InnoDB and don't think about it :)
thank you for your time and help :)

on shared hosting i think we have most reading ( page views ) and writing is very less than reading ( website admin send post or user comment ,...) is it right ?

i think page views of websites are very more than change in database websites.

so MyISAM Is better ?

and can i change database engine on production and active server with more than 600 website safely ?!
 

wattie

Verified User
Joined
May 31, 2008
Messages
981
Location
Bulgaria
"Shared hosting" means many different websites. Each of them is free to use whatever database engine it needs - InnoDB, Aria, MyISAM, etc are all supported by the modern MariaDB servers.

The DBMS (database management system - eg MySQL/MariaDB in your case) do support multiple database engines in one installation. You do select the database engine on TABLE LEVEL (for example you can have one database with two tables - one InnoDB and one MyISAM table - with no problems).

The database engine is specifically selected based on the specific website it will serve. So your question is a little bit of nonsense. The shared hosting server will provide all options (it does by default!) and the specific websites will choose whatever they need. You don't mess with the choice of your clients - they choose their table engines, not you.
 
Last edited:

Desperados

Verified User
Joined
Jan 2, 2017
Messages
31
"Shared hosting" means many different websites. Each of them is free to use whatever database engine it needs - InnoDB, Aria, MyISAM, etc are all supported by the modern MariaDB servers.

The DBMS (database management system - eg MySQL/MariaDB in your case) do support multiple database engines in one installation. You do select the database engine on TABLE LEVEL (for example you can have one database with two tables - one InnoDB and one MyISAM table - with no problems).

The database engine is specifically selected based on the specific website it will serve. So your question is a little bit of nonsense. The shared hosting server will provide all options (it does by default!) and the specific websites will choose whatever they need. You don't mess with the choice of your clients - they choose their table engines, not you.
this is my server /etc/my.cnf content :
[mysqld]
bind-address = 127.0.0.1
local-infile=0
innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
max_connections = 650
innodb_buffer_pool_size=2G
open_files_limit=50000

is it ok ?
sorry for my basic questions :(
 
Top