Tag Info

Hot answers tagged

26

Store in the database with a blob A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity. Store on the filesystem with a link in the database I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. ...


17

Number 1 for complete data integrity. Use the other options if you don't care about data quality. It's that simple. Most RDBMS have optimizations for storing BLOBs (eg SQL Server filestream) anyway


13

If going for oracle, take a look at dbfs and Secure Files. Secure Files says it all, keep ALL your data safe in the database. It is organized in lobs. Secure Files is a modernized version of lobs, that should be activated. dbfs is a filesystem in the database. You can mount it similar like a network filesystem, on a Linux host. It is real powerful. See ...


8

You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option. Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps). Call expdp as usual, adding: REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION Short example (schema: mat): create or replace package remap as function ...


6

I think the right answer here depends a lot on your application, and how important those documents are. For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the ...


6

BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB As for TINY/MEDIUM/LONG; how big is your data? TINYBLOB : max 255 bytes BLOB : max 65,535 bytes MEDIUMBLOB : max 16,777,215 bytes LONGBLOB : max 4,294,967,295 bytes In some small minority of cases TINYBLOB may suffice, ...


5

Although it partly depends on the application/environment (people included), I'd go for the blob. Keeping everything in the database means replication works for file data. You'd need a separate mechanism to synchronise FS files. In some applications, the filesystem shouldn't be modified anyway. For example, on a production website, I'd avoid ever using the ...


5

Back in the day, Microsoft hyped up the ability to store images (and similar blob data types) in the database. The was a cool new feature of SQL Server 2000 (I am pretty sure it was 2000, not 7.0) and many people jumped on the bandwagon. Storing BLOBS in the database has advantages and disadvantages: On one hand, all your data and related images or ...


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


4

For postgres: It's actually straight foreward. There is a BYTEA type that can be used for storing binary strings. Per default, there are no build in utilites like the ones mentioned for MS or Oracle. So storing lots of big files and retrieving them can get tedious. You also need to do the conversion of the files within the application (like with a ...


4

Just do SELECT ItemId FROM MyTable WHERE Value = 0xAAFF You are converting the varchar representation to varbinary(max) which is not correct. SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.


4

I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in the main heap tables so as to move them out of the way of operations that may count large numbers of records. Other dbs may do something similar. The major ...


4

Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small. I have discussed TOAST before in the DBA StackExchange May 01, 2012 : what is bigger than a longblob? Mar 21, 2012 : Are many NULL columns harmful in mysql ...


4

My vote would be for neither. Store the data in a system like Amazon S3 or Microsft's CDN and store that URL in the database. This way you get reliability of having the data always accessible without having monster sized databases to deal with.


3

Consider using de-duplication if you have lots of identical XML - documentation link. I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!


3

Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully. In your specific case, however, there may be ...


3

I have created a very simple demo of how partition switching might work for you: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1 IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date ...


2

Part 1 of the answer to your question is no, you can't selectively tell MySQL what not to store in the InnoDB buffer pool. It's an integral part of how InnoDB manipulates table data. Part 2 is that you don't really want to or need to, because it's smart enough to work out what should stay there if the pool is not large enough to retain your entire data ...


2

Yes, a prefix length of one will allow you to have a working index for looking up NULLs. Here's the SQLFiddle: http://sqlfiddle.com/#!2/12bf3/1/0 The EXPLAIN says that it is using the desired index. Of course you might want to try a larger test to make sure it performs as well in reality as you hope.


2

I see that you looking for very quick turn around and partitioning might work well as long as you have Enterprise Version of SQL SERVER. just wanted to show the alternate "customized horizontally partitioned method". As you said there is not much time to rollout bigger changes to each client; in my view once tested internally this kind of only DB changes ...


1

There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor. If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this ...


1

Splitting BLOG fields into another table is generally a pretty good idea - so you are on the right track. It benefits you by making sure that the stuff you care about being in memory (the index) stays there and doesn't get polluted by the oversized BLOB pages. How large are your BLOBs? Might it be worth storing them outside the database in a document NoSQL ...


1

Using InnoDB and the Barracuda file format permits you to deploy the Dynamic row_format. This configuration will aid you due to the way it will store the BLOBs off page. Queries not using the columns with the BLOBs shouldn't need to touch their pages; http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-row-format-dynamic.html


1

Unless I'm oversimplifying this, you could create a dedicated file group for the table in question, perhaps on an SSD for performance. Assuming you are expecting to remove approximately the same number of rows as are being added, you only need enough room for 3 days worth of data (perhaps I'd go with enough room for 2 weeks worth just for wiggle room). ...


1

I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw "java.io.IOException: I/O Exception: Connection reset by peer: socket write error" on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok ...


1

The max text repl size options exists to limit how much LOB data can be added to a replicated column. If you turn off the LOB size limit and a user inserts a large amount of LOB data, latency will increase and replication will come to a crawl. The max text repl size option can be used to prevent this.


1

The InnoDB buffer pool stores copies of anything that is stored in database pages on disk. That includes: Data (which is really the clustered primary index for each table) Secondary indexes Blobs/Text/Varchar that won't fit on data pages Undo pages Change buffer pages etc. There's no way you can exclude one of these types of pages. They all occupy the ...


1

The setup is pretty strait forward. As the TechNet article below states you need to make sure the Windows Share Name is the same across all nodes. http://technet.microsoft.com/en-us/library/cc645886.aspx


1

You most likely have codepage issues here, where some parts of the process is interpreting the binary data as text and performing some sort of conversion instead of leaving it well alone. This may not be in your database, it could also be in the server-side code that is performing the inserts/updates and selects, or it could even by your browser that is ...


1

You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...). To get a total size in bytes of column for the entire table SUM it up like this: SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes FROM my_table If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual ...



Only top voted, non community-wiki answers of a minimum length are eligible