How to tune or optimize MySQL or MariaDB ?
-
I ran into this a lot lately, and I dont know what to use as a good base.
I do google search and find many variables and try to understand as many as possible but the thing is you worry that too much changing would affect the integrity of the database.
And since when optimizing database would require 10 performance variables, why cant we select something pre-made, I recall windows versions had something called small.ini/medium/large.
I dont think I should need to learn all that on top of building an application, I think it is stuff like this why people choose Oracle or MS SQL
This whole thing started when I began using centos with openfire, and I noticed everything is working but when pressing the users/groups in openfire it was slow (5 seconds) to show list of users which is like 60 users, and the same openfire in Windows using the integrated database was much faster, thus I reckon I need to optimize mariadb version 5.
So what is the main thing I should put in /etc/my.cnf
I reckon that it comes reconfigured that it will run in small ram environment perhaps ?What I want to do is the minimalist config changes that will give me the best performance outcome.
Using InnoDB.
Thanks.
-
Why do you feel that all this tuning is needed? And why do you feel that Oracle or SQL server would be different? Conventional experience is the opposite, those tend towards far more tuning and MariaDB less. I've never known anyone to need to tune their MariaDB. Not that you never would, but this doesn't affect most people and is certainly not a decision factor.
What kind of extreme resource limits are you under to produce this concern?
-
Integrated databases are often much faster. Especially at small sizes. If you are heavily constrained, I would expect SQLite to be faster since the RDBMS doesn't need to be loaded and no network overhead is needed.
-
Are you sure that the delays are coming from the database and not the application? MariaDB is screaming fast. Perhaps you have an IOPS problem and the system is blocked on the operation. There is not way that the database is taking five seconds to respond. That's not a reasonable assumption. Something else is wrong.
-
Perhaps you are out of memory? This could be extreme swapping?
-
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
What kind of extreme resource limits are you under to produce this concern?
the fact that the web is wealthy with documents and variables to speed up mariadb/mysql
things like:
innodb_buffer_pool_size
innodb_log_file_size
innodb_file_per_table
innodb_flush_log_at_trx_commit
query_cache_size
...etcAll this got me thinking what are the defaults of Centos MariaDB installation, and are they enough to handle any load ?
And the VM have 2GB of RAM btw.
-
using free -m I am using 512 of 2GB ram
-
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
Integrated databases are often much faster. Especially at small sizes. If you are heavily constrained, I would expect SQLite to be faster since the RDBMS doesn't need to be loaded and no network overhead is needed.
I guess this is it really then, to be honest it is more like 3 seconds, but with the integrated database it was very fast, thus I noticed it.
-
@msff-amman-Itofficer said in How to tune or optimize MySQL or MariaDB ?:
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
What kind of extreme resource limits are you under to produce this concern?
the fact that the web is wealthy with documents and variables to speed up mariadb/mysql
things like:
innodb_buffer_pool_size
innodb_log_file_size
innodb_file_per_table
innodb_flush_log_at_trx_commit
query_cache_size
...etcAll this got me thinking what are the defaults of Centos MariaDB installation, and are they enough to handle any load ?
And the VM have 2GB of RAM btw.
You haven't even mentioned what database engine you are using. I'm assuming InnoDB but that's not the most common with MariaDB. But just because the Internet is full of people talking about it doesn't tell us that it is appropriated or useful. The web is full of people recommended RAID 5 for databases but we know that's crazy (till SSD).
-
@msff-amman-Itofficer said in How to tune or optimize MySQL or MariaDB ?:
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
Integrated databases are often much faster. Especially at small sizes. If you are heavily constrained, I would expect SQLite to be faster since the RDBMS doesn't need to be loaded and no network overhead is needed.
I guess this is it really then, to be honest it is more like 3 seconds, but with the integrated database it was very fast, thus I noticed it.
But you changed everything, not just the database. It's not reasonable to think that the database is the factor in question. Possible, yes. Likely? Not at all.
-
@msff-amman-Itofficer said in How to tune or optimize MySQL or MariaDB ?:
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
Integrated databases are often much faster. Especially at small sizes. If you are heavily constrained, I would expect SQLite to be faster since the RDBMS doesn't need to be loaded and no network overhead is needed.
I guess this is it really then, to be honest it is more like 3 seconds, but with the integrated database it was very fast, thus I noticed it.
No. There is no realistic possibility that this is it. Not even slightly.
-
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
@msff-amman-Itofficer said in How to tune or optimize MySQL or MariaDB ?:
@scottalanmiller said in How to tune or optimize MySQL or MariaDB ?:
Integrated databases are often much faster. Especially at small sizes. If you are heavily constrained, I would expect SQLite to be faster since the RDBMS doesn't need to be loaded and no network overhead is needed.
I guess this is it really then, to be honest it is more like 3 seconds, but with the integrated database it was very fast, thus I noticed it.
No. There is no realistic possibility that this is it. Not even slightly.
Exactly. The ScreenConnect server performs like crap on Linux compared to Windows. But the problem is not the database. It is the .NET framework.
-
@JaredBusch said in How to tune or optimize MySQL or MariaDB ?:
Exactly. The ScreenConnect server performs like crap on Linux compared to Windows. But the problem is not the database. It is the .NET framework.
Right, I could totally see there being an application layer issue. Or a hardware problem. Or the system just being over-taxed. So many options. But that MariaDB isn't "tuned" for this, that's not a possibility. We'd measure the database response time for this in milliseconds, not seconds. And only sixty records? I've got MySQL on ancient systems with a fraction of these resources that will return literally millions of records in less time than that.
