Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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 !!!

share|improve this answer
1  
Implicit temp tables have no impact on replication, whether SBR or RBR. Explicit temp tables send all the DDLs across the wire. –  Rick James Jan 8 '13 at 1:37

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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