0

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
  • it might be as you used positioning in adding column i.e. AFTER clause in MySQL Alter table, can you check doing adding it without positioning it by yourself at the end and share result, it will give better insight. – Nawaz Sohail Mar 18 '16 at 8:15
  • FLOAT(m,n) is generally a mistake and should be avoided -- it causes truncation and double-rounding. Either use DECIMAL(m,n) or plain FLOAT. – Rick James Mar 20 '16 at 23:36
1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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