Is it bad to create many mysql temporary tables simultaneously?

Question

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better (extremely more efficient and faster than PHP arrays; obviously due to their mechanism).

My tentative solution is to form a temporary table upon each request (i.e. connection) to import and process the data. However, I am not sure if there is a drawback for creating many temporary tables simultaneously?

Can this make problem for the mysql server? Or I can use it as an alternative to PHP arrays in numerous simultaneous requests?

Answer 1

You want to prevent making temp tables as much as possible : Prevent copying to temp table (sql)

They do not work well in MySQL Replication : How are binlogs updated for MySQL temporary tables?

Since temp tables are a fact of life in the DB world, you may have to make some unorthodox changes to accommodate their existence.

Normally, mysqld has the habit of placing tmp tables in /tmp or wherever tmpdir is configured. That's usually on some poor unsuspecting disk.

An interesting alternative would be to setup a RAM disk and reconfigure tmpdir to use it

STEP 01 : Create a Mount Point for a RAM Disk

mkdir /var/tmpfs

STEP 02 : Add the RAM disk to /etc/fstab (16GB)

echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab

STEP 03 : Add this line to /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

STEP 04 : Enable the RAM Disk

You can do one of the following:

1. Just reboot the DB Server
2. mount -t tmpfs -o size=16g none /var/tmpfs

Before you do this, make sure you have enough RAM

Give it a Try !!!

Answer 2

"Many mysql temporary tables" -- The potential issues:

• table_open_cache (and other tunables) -- You could (but are not likely to) hit some limit
• Ram bloat -- You could (but are not likely to) use more ram. If this leads to swapping, that is really bad.

I would not worry about "many" unless you really have hundreds of temp tables at once.

How many threads (SHOW PROCESSLIST; ignore 'Sleep') are running at once? Even a busy system rarely has more than 10 at once.

For implicit temp tables, let's see the SELECTs that are causing them; it may be possible to redesign the queries to avoid the temp tables.

I don't like using a ram-disk -- it takes RAM away from other caching possibilities, and runs the risk of hitting a hard limit (the disk size).