CONCAT string to longblog and mysql disk usage

mysql text
mysql blob
mysql data types
mysql blob performance optimization
mysql blob to string
mysql text vs varchar
mysql data types size
mysql varchar max

If I am an innodb table that this query executes a lot (once every second for some time with a small amount of data appended) and have innodb_files_per_table = 0 can this cause a problem with storage? The blob itself could be 2-3MB but I lost 30gb of data usage and am wondering if this code is to blame in any way or if I should be looking somewhere else.

SQL

UPDATE phppos_app_files 
SET file_data = CONCAT(file_data, "NEW LINE OF DATA")
WHERE file_id = 10;

Schema:

mysql> show create table phppos_app_files;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                             |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_app_files | CREATE TABLE `phppos_app_files` (
  `file_id` int(10) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `file_data` longblob NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7577 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

The data stored in blog column is 2-3 mb of log text; just plain text. My question I am asking though is if frequent update queries where you concat from the current value cause storage issues where it causes huge problems with database. I just want to make sure it only stores the least amount of info and if an update with concat can cause any huge disk issues.

MySQL 8.0 Reference Manual :: 8.4.2.3 Optimizing for , Index Merge Optimization Do not use this technique when the entire table is compressed by InnoDB or MyISAM . For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a See Section 8.4.​2.2, “Optimizing for Character and String Types” for reasons why a binary VARCHAR  MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings. In MySQL (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end. Here’s an

If you make frequent updates of large blobs, it could indeed use a lot of storage temporarily, and leave your tablespace fragmented.

An update copies the original data pages to the "undo log" which is just an area of pages in the tablespace. InnoDB keeps these copied pages in case you rollback your transaction and have to restore the original data. After you commit your update, the copy in the undo log is not needed anymore, and InnoDB will gradually clean up. But if your updates are frequent as you say, you could be accumulating garbage in the undo log faster than InnoDB can clean it out.

In any case, expanding the storage for more undo log content will grow the tablespace, and even after InnoDB cleans up, the tablespace will not shrink. It'll always remain the size of its high-water mark.

To mitigate this, MySQL 5.6 introduced an option to allow the undo log to be stored outside the system tablespace, and MySQL 5.7 introduced a feature to truncate undo logs after they're cleaned up. Read https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html for more information on these features.


If you're interested in the internal layout of InnoDB files, I recommend trying out the open-source tools Jeremy Cole wrote: https://github.com/jeremycole/innodb_ruby/wiki

You can dump various summaries of what's in your ibdata1 tablespace. A lot of the file could be unused, but still taking space.

Here's an example from my local MySQL sandbox. I just have one table with innondb_file_per_table=0 (all the other tables are outside the central tablespace). I ran this command:

innodb_space -s /usr/local/var/mysql/ibdata1 -T mydatabase/mytable space-extents-illustrate

Here's the illustration it created. The legend (not included) showed that the data and indexes for my one table fell in the pages between 3008 and 3392. The other stuff was mostly InnoDB system data structures (black), system tables, insert buffers (dark yellow), and unoccupied pages (grey).

MySQL Bugs: #96466: .ibd file grows indefinitely for tables with blob , In choosing between the speed and disk space usage minimisation, the For the random string test, file grows indefinitely on 8.0.13 but  Summary: in this tutorial, you will learn various ways to concatenate two or more strings together by using the MySQL CONCAT and CONCAT_WS functions. To concatenate two or more quoted string values, you place the string next to each other as the following syntax:

you can use below query to check size of your tables. In this way you can see which table is wasting your storage.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC

indeed, The total amount of space used stays same.

If you use innodb_files_per_table=0 you will have only one tablespace and since your data is changing quickly there is a possibility that your tablespace get fragmented in time.

If you decide in future to drop or truncate your table, your tablespace size will remain same (does not shrink) hence you cannot regain the space from drop operation.

4. Optimizing Schema and Data Types, The first step in deciding what data type to use for a given column is to determine Your choice determines how MySQL stores the data, in memory and on disk. The two major string types are VARCHAR and CHAR , which store character values. are TINYBLOB , SMALLBLOB , BLOB , MEDIUMBLOB , and LONGBLOB . CONCAT() function. MySQL CONCAT() function is used to add two or more strings. There may be one or more arguments. Returns the string that results from concatenating the arguments.

Do varchar/text/longtext columns in MySQL occupy full size in , So, if you use this kind of arithmetic, it will underestimate the disk space used. MyISAM is more L represents the actual length in bytes of a given string value. I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate. What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem?

Writing and Reading MySQL BLOB Using JDBC, We will use the candidates table in the mysqljdbc sample database. ALTER TABLE candidates ADD COLUMN resume LONGBLOB NULL AFTER email; String filename) { // update sql String updateSQL = "UPDATE candidates " + "SET​  Here is the query to concatenate two strings with space − mysql> update DemoTable -> set Subject=concat(Name,' ',Subject); Query OK, 3 rows affected (0.38 sec) Rows matched: 3 Changed: 3 Warnings: 0. Let us check all records from the table − mysql> select *from DemoTable; Output

Python MySQL- Insert / Retrieve file and images as a Blob in MySQL, Insert file, image, video, or a song as a blob data into MySQL table and Alternatively, if you have a table then modify it and add one extra column with BLOB as its data type. You can use the following query to a create table with a BLOB To write this binary data on hard disk we can pass the file format in  SQL CONCAT function implicitly converts arguments to string types before concatenation. We can use SQL CONVERT function as well without converting the appropriate data type. If we concatenate string using the plus( +) operator, we need to use SQL CONVERT function to convert data types. Lets’ look this using the following example.

Comments
  • without knowing more about what you store into file_data, we cannot identify issue.
  • it is 2-3mb of plain log data
  • can you check my answer before going further, because we first need to make sure that this is the table which is causing trouble
  • How many times did you add a small string to that blob before 27930.00MB vanished? In other words, I am fishing for a reproducible test case.
  • It is really hard to say. I have many databases and I would say 4 or 5 of them had some activity with this type of logging. But then I got thinking that sessions are stored as a blob too and are updated frequently and have never had issue, when this storage issue happened this was the major backend change made recently. I could be wrong but have nothing else to point to. Are their any other reasons to have MySQL just claim a bunch of space? Any commands I can run to show you any more data?
  • Could this explain 30gb of lost free space?
  • @ChrisMuench : fragmentation could explain that. Right now you want to OPTMIZE TABLE and check its size afterwards...
  • Does optimize table have any affect on storage size with Innodb file per table 0. I have many databases
  • OPTIMIZE TABLES does defragment, but it can't shrink a tablespace, unless you use innodb_file_per_table=1. A multi-table tablespace will never shrink. It can only mark empty extents as ready to be reused for future data. So it will repurpose any empty extents before it grows the tablespace again.
  • @BillKarwin thanks for pointing this out... I updated my answer
  • This shows what I would expect in terms of data sizes; but I am guess I am concerned if the query above can fill up free space since I am using innodb_file_per_table of 0
  • 27930.00MB is my free space as found from mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA_Space, -> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE_Space, create_time, table_rows, engine, create_options -> from information_schema.TABLES -> where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0 -> ORDER BY FLOOR ( FREE_Space ) DESC, FLOOR ( DATA_Space ) DESC, TABLE_SCHEMA, TABLE_NAME -> ;
  • Just wondering if this query can contribute to excess free space or if I should be looking somewhere else
  • So if I am updating a lot this can cause a possible of heavy fragmentation and possible storage issues? If I enable file per table would I not have this issue?
  • if you enable, you want have this issue on your other tables. If you keep it disabled then you will store all your tables into one tablespace and when your tablespace get fragmented, it will affect all your tables.