> LONGBLOBs are just streams of bytes. Intel or not (endianness) should not > matter when you fetch and store the data using database methods. > BLOBs are good for storing binary objects such as attachments or emails that > you want to keep in their original encoding. Everything else should be stored > as (unicode) text. Storing binary data BASE64 encoded is not optimal when > the database supports BLOBs.
No argument that it's not optimal, but at least it's the *same* representation regardless of database engine or platform. And base64 encoding guarantees that the same input string is interpreted identically on all platforms and all databases. I have a few sites that deal in full 32 bit ISO 10646 character sets, which add a whole another dimension to the annoyance of dealing with binary blobs. Use of base64 also allows databases on non-ASCII platforms to deliver and store the data in a portable form (for example, z/OS DB/2 makes a really nice OTRS back end if you have it, and DFSMShsm knows what to do with USS files, so automatic recall from tape works like a champ. I don't have to worry about attachment file sizes at all. 8-)). > The difficulties only appeared because OTRS > treats Postgres and the other databases differently from MySQL, as if they > would not support binary data, for no apparent reason. I think we're arguing for the same point, but from different starting positions. I agree that there shouldn't be a difference between how MySQL and the other databases store the data. Given that the parsing is (and should be) done in the application logic, the overhead should be fairly minimal, and distributed. > > > Are you storing attachments in the database? If so, you might consider > > changing that. > > Thanks for mentioning that, I'll consider it. Found the script > ArticleStorageSwitch.pl which should make this possible. However, it may > also have disadvantages. You need to backup database and filesystem > separately, and consistency between the two is not automatically > guaranteed through foreign keys. Also, there are some other BASE64 > encoded columns besides those for storing the attachments. Yes, you're correct, but the vast majority of the performance impact you're commenting on is going to be dealing with the attachment blobs. The other columns are tiny in comparison. I also think you'll find that your backup load is much smaller with attachments outside the database -- every time you change the database, the whole file has to get dumped (unless you have a fairly smart backup system and are doing table-level backups from inside the database). Having the attachments outside the database will significantly reduce the amount of data backed up, in that change detection is much more effective with external files. Most databases are really not optimized for use as opaque object stores. Keep in mind that the attachments are referenced only when opened. It's just opening an opaque object reference, so I don't see what consistency guarantees (other than the correct file gets opened when clicked on) you need. The object exists, or it doesn't. If it doesn't, you know what to do, and it's operationally a lot easier to manage whether a file exists or not. It also allows you to use much more intelligent object stores (such as Ceph or DFSMShsm on z/OS) with corresponding improvements in storage performance and compliance auditing capabilities. --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
