0

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?

5
  • I think Database Administrators would be a better place for this kind of problem.
    – Filburt
    Commented Feb 25, 2021 at 11:44
  • 1
    using RAMdisk is insane. I'm sure you value your data somewhat, also it may not be faster. Include detail of your my.cnf, RAM available, enable slow query log. show global status (as pastie link). Include a mysql version by editing your question.
    – danblack
    Commented Feb 26, 2021 at 2:51
  • Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Commented Feb 26, 2021 at 20:00
  • WordPress/WooCommerce has sloppy/slow indexes on postmeta. How to fix: mysql.rjweb.org/doc.php/…
    – Rick James
    Commented Feb 26, 2021 at 22:15
  • If my index fixes are not sufficient, please provide RAM size, buffer_pool_size, and amount of data.
    – Rick James
    Commented Feb 27, 2021 at 1:10

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.