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.
-
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.
