New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Looking for database advice.
Hello,
So one of the servers I manage (moodle for a certain uni) has a database size of 88.56 GB, is this already considered big? Seems like performance is affected... Any effective way to optimize the database safely and maybe gain some more space and speed? (I'm using mariadb)
Comments
I think it's not that big. Check your sql query first. command : explain query. Full table scan results in slow execution.
MySQL should easily handle an 89 GB database, even on a VPS. I've dealt with ~2.5 TB MySQL databases at my day job, albeit on dedicated hardware (and the DBAs recommend starting to split the DB into separate shards once it's around 3 TB)
Some ideas:
EXPLAIN
(like @Arirang suggested above). If you're having performance issues, one of the most common reasons is that an index is missing, resulting in full table scansIf indexes are insufficient and you still have perf issues:
If you're running other stuff on the database server (for example, a web server), split them onto two separate servers. Reducing context switching will improve performance.
If the app is very read-heavy, you could use replication to have multiple replicas. Replicas are generally read-only, meaning reads can go to replicas but writes must go to the master.
It's not DB size as much as concurrent usage. One user on a 1TB database doing mainly reads is not that demanding...1,000 users slamming a single 10MB table with nonstop updates can be glacial.
Is Moodle read-heavy? If so @Daniel15's suggestions on adding read-only replicas is great. DBs are also I/O monsters so worth making sure you're running on the speediest possible storage. Definitely worth looking at indexing...the problem with code you don't control is you're stuck with their queries but you can still index and the code will benefit.
Thank you for the advices, helped a lot. In this use-case, moodle is quite read-heavy. Concurrent (logged in) users range from 250 minimum to about 18000 when the traffic peaks. Everything feels better now.
What did you end up doing to improve it?
@Daniel15 How do you backup 2.5 TB MySQL database? mysqldump from a replica? how long that usually took?
I'm not sure. I'm a developer - backing up the database is the DBA's problem The DBs my team usually deals with are much smaller (most are less than 50 GB, and some tables I maintain are only a few MB).
Did a bit of digging and it looks like an older backup system (that doesn't appear to be used any more) backed up deltas using a custom system with binlogs. Given an initial full backup, you can get the GTID for the last committed transaction that was part of the backup (I guess it's in the backup itself), then parse all binlogs after that GTID.
Seems like they might just use mysqldump for most databases now, but I'm not sure how long it takes.
Hopefully you weren't just dumping images/media as blobs in the db.
Makes sense for that huge db to do continuous incremental backups. Otherwise maybe shutdown a replica server, copy files start again.
Not blobs In my case it's things like code quality metrics, eg. imagine periodically running various linters (such as ESLint) and other metrics tools (such as sloccount, Python Lizard, etc) across millions of source code files and logging the results to a database, to keep track of code quality over time. All that data adds up over time!
All the "huge" data goes into HDFS and is queried via Presto, which I think can be easily snapshotted and backed up, but stuff that has to be queried from a UI is usually in MySQL.
Many ways to do it with single threaded or multi-threaded backup tools like mysqldump, mydumper, dedicated binlog server/proxy, mariadb backup and percona xtrabackup. With the right software tools and hardware, I usually can backup large MySQL databases at 200-600MB/s and even restore at 100-300MB/s speeds.
I ran mySQL tuner and followed the recommendations. Once I did it, performance has improved so I didn't get to follow your following advices. Thanks! I noted all your advices and will be splitting the web server and the DB once it reaches 3TB.
SharePoint has entered the chat.
Is there any slow query generating in the server? Another major factor in database performance is the version of MySQL you're currently deploying. Staying up to date with the latest version of your database can have significant impact on overall database performance.
There were, it was more than one but I already forgot what those are... MariaDB is always updated.