Mysql – RAMdisk and MySQL in Ubuntu20.04

MySQL

I'm working on a development in WordPress/WooCommerce which has a very large number of products, currently 180,000. The problem here is, of MySQL which slows down dramatically as the product number exceeds 60,000. This is mainly due to MySQL apparently using the Ubuntu filing system to store data. It would be better if a different database could be used but in the meantime I was considering setting up a RAMdisk and using that for the MySQL temp data. Now there are any number of posts on how to do it, all of which are wrong for Ubuntu 20.04. Running 'df' I can see a mount point at /dev/shm which appears to be usable and wondered if it is. My knowledge of Linux isn't great enough to be sure. Tutorials also suggest editing /etc/rc.local which no longer exists in Ubunto 20 along with /etc/apparmor.d/local/usr.sbin.mysqld which also doesn't exist. Also /etc/mysql/my.cnf has no reference to tmpdir. All this would appear to indicate that what I'd like to do (other than use a grown up database like PostgreSQL or SQL-Server) can't be done or perhaps doesn't need to be done. Of course running the whole MySQL database in RAM would be fast but unfeasible, I know.

Any suggestions?

Best Answer

... MySQL ... slows down dramatically as the product number exceeds 60,000.

MySQL does not run slowly. Trust me; it really, really doesn't.
What does get slower are the queries that you run against the data structures inside MySQL.

I was considering setting up a RAMdisk and using that for the MySQL temp data.

Ah ha!
It's not the size of the database that's giving you trouble. It's the amount of temporary space being used to process the queries. That says to me that you have some [poorly written] queries that are in need of some tuning.
Turn on the Slow Query log, see which queries appear in there and then start tuning those. Often, it's only a handful of poor queries that bring an entire system to its knees.

Since volume/size seems to be an issue, here's some common things to think about:

  • Indexes.
    Do you have [the right] indexes to support your queries?

  • Using "select *"? Don't.
    Always specify the columns you want explicitly in every query.
    Pulling back redundant columns only adds to the network overhead seen by the client.

  • Using "select distinct" to get rid of "duplicate" rows. Again, don't.
    Fix the join conditions that are introducing the "duplicates".

  • Joining to a bracketed select statement? (This might be fixed in newer versions).
    In some older versions, joining from a table to a select statement wrapped in brackets caused MySQL to "create" the bracketed result set in temporary space before joining it to the other table.

... running the whole MySQL database in RAM would be fast but unfeasible, I know.

Fast? Probably not. You're effectively "conning" the operating system into thinking that a big chunk of memory is actually disk space but, unless you have a lot more memory besides that, the O/S will probably need to page that memory in and out, to and from disk, which is slow.

Worse still, as soon as the machine was powered off, the RAMDisk and everything on it (i.e. "the whole MySQL database") would evaporate, like the morning dew.
Possibly not the most resilient operating model. :-)

Related Question