(thanks for including me on this thread, Russell)
Most default DB installs work "fine" until they get a significant amount of
data, users, etc.
Migration to another DB will not solve fundamental flaws in schema or query
design or other such factors.
In addition, often schema, queries and also server setup is architected
towards using the particular original database it was designed on. Thus it's
generally unlikely that simply moving from one brand db server to another
create vast (if any) improvements - and that's apart from the pain of
migrating in the first place.
For these reasons, I generally don't recommend migrations, regardless of
which-to-which brand it is.
However, if you are familiar with PostreSQL and not with MySQL or MariaDB,
then naturally you'd be more likely to create an optimal setup with
PostgreSQL. So if the application you're using supports PostgreSQL, then that
can be the best choice for you.
If you find that you do need to use MySQL/MariaDB for this task, it's
important to configure the server appropriately. It's important to realise that
the defaults are for a functional setup with minimal disk/memory use, they
are not for a production system. Some people just up some of the numbers,
but that's not really how it works ;-)
If this is for a business/company/organisation, feel free to contact my
business for assistance. Details in signature.
What we'd need to look at is which storage engine is getting used by the app,
and what tasks it's trying to do. Based on that we can create a baseline
configuration that'll perform reasonably well, which can then be tuned
further over time.
You mentioned bulk inserts. I can insert between 80K (InnoDB) and 340K
(MyISAM - datawarehousing engine) rows per second on a single
connection/thread in MySQL. It's not a matter of whether MySQL can handle
Thanks for the input. I recognise that there is an obvious problem not related to choice
of database when tps drops from tens of thousands to hundreds, and thanks to Russell's
hints about IO monitoring I think I've identified that it is to do with some
bottleneck in my Xen installation (almost certainly related to a bug somewhere), and
I'm going to try a reboot when practical.
Last time this happened I did a bunch of things, including throwing more memory at the VM,
adding another processor, swapping the backend to innodb, upgrading mysql, upgrading
Bacula, and putting the storage on local rather than iSCSI disks. Nothing helped until I
rebooted the physical machine and then it seemed to come good after that although I
didn't make the connection at the time.