Mysql takes more tmp space than the table size

alter-tableMySQLmysql-5.6temporary-tables

I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB.

See the below mysql error which the alter command was failing with

2016-03-17 01:47:53 7f8962739700 InnoDB: Error: Write to file (merge) failed at offset 140509184.
InnoDB: 1048576 bytes should have been written, only 667648 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html

My Question is why did the mysql alter command fail due to insufficient size when there is more than 2.8GB of space on /tmp.

To see how much space is mysql consuming to run the alter command. I changed the /tmp partition to a partition which has more space like 15GB.

when I ran the alter command, mysql took up 5.4GB of tmp space to process the command. This is almost twice the size of the table.

Can anybody help me understand this behaviour. I am using Server version: 5.6.22-log MySQL Community Server (GPL)

show create table jjkl_sec\G
    *************************** 1. row ***************************
           Table: jjkl_sec
    Create Table: CREATE TABLE `jjkl_sec` (
      `ia_second_UTC` datetime NOT NULL,
      `ia_second` datetime NOT NULL,
      `ia_device_id` int(11) NOT NULL,
      `ia_job_id` int(10) unsigned zerofill DEFAULT NULL,
      `ia_time` bigint(19) NOT NULL DEFAULT '0',
      `ia_network_type` varchar(45) DEFAULT NULL,
      `ia_mcc` int(10) DEFAULT NULL,
      `ia_mnc` int(10) DEFAULT NULL,
      `ia_lac_id` int(10) DEFAULT NULL,
      `ia_cell_id` int(10) DEFAULT NULL,
      `ia_signal_strengh_rssi` int(10) DEFAULT NULL,
      `ia_testcase_id` varchar(128) DEFAULT NULL,
      `ia_lte_pci` int(10) DEFAULT NULL,
      `ia_lte_rsrp` int(10) DEFAULT NULL,
      `ia_lte_rsrq` int(10) DEFAULT NULL,
      `ia_lte_rssnr` int(10) DEFAULT NULL,
      `ia_dbm` int(10) DEFAULT '-1',
      `ia_asu` int(10) DEFAULT '-1',
      `ping_avg` varchar(50) DEFAULT '0',
      `ia_ping_rtt_min` varchar(50) DEFAULT '0',
      `ia_ping_ip_address` varchar(100) DEFAULT '0',
      `ping_id` int(10) DEFAULT NULL,
      `ftpdl_id` varchar(128) DEFAULT NULL,
      `httpdl_id` varchar(128) DEFAULT NULL,
      `ftpul_id` varchar(128) DEFAULT NULL,
      `httpul_id` varchar(128) DEFAULT NULL,
      `ftpdl` float(12,0) DEFAULT '0',
      `httpdl` float(12,0) unsigned DEFAULT '0',
      `ftpul` float(12,0) DEFAULT '0',
      `httpul` float(12,0) DEFAULT '0',
      `x_param` float(16,8) DEFAULT '0.00000000',
      `y_param` float(16,8) DEFAULT '0.00000000',
      `x_indoor` float(16,8) DEFAULT '0.00000000',
      `y_indoor` float(16,8) DEFAULT '0.00000000',
      `ia_last_location_time` datetime DEFAULT NULL,
      `ia_location_provider` varchar(128) DEFAULT NULL,
      `ia_collect_altitude` float(12,0) DEFAULT '0',
      `ia_location_tags` varchar(50) DEFAULT NULL,
      `ia_arfcn` int(10) DEFAULT NULL,
      `ia_pci` int(10) DEFAULT NULL,
      `ia_rsrp_rx0` float(12,0) DEFAULT NULL,
      `ia_rsrq_rx` float(12,0) DEFAULT NULL,
      `ia_rssi_rx0` float(12,0) DEFAULT NULL,
      `ia_sinr_rx0` float(12,0) DEFAULT NULL,
      `ia_cqi_pucch` int(10) DEFAULT NULL,
      `ia_cqi_pusch` int(10) DEFAULT NULL,
      `ia_wcdma_rx0` float(12,0) DEFAULT NULL,
      `ia_evdo_rx0` float(12,0) DEFAULT NULL,
      `ia_wcdma_psc` float(12,0) DEFAULT NULL,
      `ia_cdma_rf_pn` float(12,0) DEFAULT NULL,
      `ia_wcdma_ec_io` float(12,0) DEFAULT NULL,
      `ia_evdo_asp_pilotpn_0` float(12,0) DEFAULT NULL,
      `ia_evdo_asp_pilotstr_0` float(12,0) DEFAULT NULL,
      `ftpdl_i` float(20,0) DEFAULT NULL,
      `ftpul_i` float(20,0) DEFAULT NULL,
      `httpdl_i` float(20,0) DEFAULT NULL,
      `httpul_i` float(20,0) DEFAULT NULL,
      `ia_wifi_ssid` varchar(150) DEFAULT NULL,
      `ia_wifi_bssid` varchar(150) DEFAULT NULL,
      `ia_wifi_signal_level` int(10) DEFAULT NULL,
      `ia_wifi_frequency` int(10) DEFAULT '0',
      `ia_wifi_linkspeed` varchar(255) DEFAULT NULL,
      `wholepage_duration` bigint(19) DEFAULT '0',
      `setup_time` bigint(19) DEFAULT NULL,
      `call_event` varchar(15) DEFAULT NULL,
      `call_id` varchar(128) DEFAULT NULL,
      `video_ref_file` varchar(300) DEFAULT '0',
      `video_test_estimated_FPS` float(9,4) DEFAULT NULL,
      `video_jitter` int(10) DEFAULT NULL,
      `video_PEVQ_MOS` float(9,4) DEFAULT NULL,
      `udp_datagram_size` int(10) DEFAULT NULL,
      `udp_Jitter` double(22,0) DEFAULT NULL,
      `udp_direction` varchar(30) DEFAULT NULL,
      `udp_thoughput` float(12,0) DEFAULT NULL,
      `MOSLQO` float(9,4) DEFAULT NULL,
      `vqt_call_id` varchar(128) DEFAULT NULL,
      `ia_file_url` varchar(500) DEFAULT NULL,
      `MOSLQO_UL` float(9,4) DEFAULT NULL,
      `vqt_call_id_ul` varchar(128) DEFAULT NULL,
      `ia_file_url_ul` varchar(500) DEFAULT NULL,
      `video_throughput` float(19,1) DEFAULT NULL,
      `voice_call_type` varchar(20) DEFAULT NULL,
      `voice_handover` varchar(20) DEFAULT NULL,
      `voice_jitter` int(11) DEFAULT NULL,
      `voice_jitter_ul` int(11) DEFAULT NULL,
      `playback_time` bigint(19) DEFAULT NULL,
      `data_timestamp_change` bigint(20) DEFAULT NULL,
      `data_change_type` varchar(80) DEFAULT NULL,
      `data_duration` bigint(20) DEFAULT NULL,
      `ftpdl_filesize` varchar(100) DEFAULT NULL,
      `ftpdl_progress` int(11) DEFAULT NULL,
      `ftpul_filesize` varchar(100) DEFAULT NULL,
      `ftpul_progress` int(11) DEFAULT NULL,
      PRIMARY KEY (`ia_second_UTC`,`ia_device_id`),
      KEY `MOSLQO` (`MOSLQO`),
      KEY `MOSLQO_UL` (`MOSLQO_UL`),
      KEY `udp_thoughput` (`udp_thoughput`),
      KEY `udp_direction` (`udp_direction`),
      KEY `video_PEVQ_MOS` (`video_PEVQ_MOS`),
      KEY `ftpdl` (`ftpdl`),
      KEY `httpdl` (`httpdl`),
      KEY `ftpul` (`ftpul`),
      KEY `httpul` (`httpul`),
      KEY `ia_job_id` (`ia_job_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

The alter command took 6 1/2 min to complete.

(root@localhost) [smart_report]> ALTER TABLE `jjkl_sec`
    ADD COLUMN `datagram_lost` INT DEFAULT NULL AFTER `udp_direction`;
Query OK, 0 rows affected (6 min 25.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

Best Answer

It looks like a lot of columns could be 'normalized, thereby significantly decreasing the size of the table and the cost of altering it.

Virtually all columns are optional?? (I see that most are NULL instead of NOT NULL.) This implies that the table probably could/should be split up into multiple tables, making the ALTER less disk-intensive.

Did you try the ALTER with ALGORITHM=INPLACE? If so, did it complain that AFTER prohibits such?

If it has to rebuild the table, then it also rebuilds all the indexes. This is another tmp-space hog. So, do you really need all the tables?

That leads to another possible technique...

Drop the secondary indexes.
Do the non-INPLACE ALTER.
Add the indexes back using INPLACE.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

MySQL looking up more rows than needed (indexing issue)

Related Question