(thanks for including me on this thread, Russell)
Hi James
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 this ;-)
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. James