I would love to see an implementation with 1 row for large data that works well. The main issues I had were that mysql has a default max packet size limit (I think it used to be like 16MB (mysql 3.23) - 1GB Mysql 4 - Not sure v5. Alot of people don't have control over those settings in their environment. If you try to do a single insert/query larger than that, the query would fail. Also it was causing the webserver to buffer a large amount of data.
Here's some numbers to tickle your fancy.. I downlaoded a file from the ftp gateway to a storage implementation I've done (production). FTP CLIENT <--> NETWORK FRONTEND VLAN (100FDX) <--> FTP SERVER (JAVA) FRONTEND <--> NETWORK BACKEND VLAN (100FDX) <--> MYSQL STORAGE NODE I grabbed an approximately 230MB file from a different box (on the same frontend vlan) in 40 seconds, at a speed of about 5800K/sec .. Also I logged into the storage node to see how mysql was doing: 5179 mysql 15 0 12528 4644 2108 S 10.8 0.5 0:04 mysqld It was bouncing between 9% and 19% during the transfer and the machine is a 2 X P3/933. There's very little chance a real internet user can download at that type of speed, so a single mysql storage node can handle a large amount of concurrent users/activity. [EMAIL PROTECTED]:~$ ftp ftp.dbfs Connected to ftp.dbfs 220 DBFS. Name (ftp.dbfs): XXXXXX 331 User name okay, need password for XXXXXX Password: 230 User logged in, proceed Remote system type is UNIX. ftp> bin 200 Command TYPE okay ftp> cd "XXXXXXXXXX/filez/apps" 200 Directory changed to /XXXXXXXXXXx/filez/apps ftp> get Visio2K3.exe local: Visio2K3.exe remote: Visio2K3.exe 200 Command PORT okay 150 File status okay; about to open data connection 226 Closing data connection 235075910 bytes received in 39.9 secs (5.8e+03 Kbytes/sec) ftp> Rules for testing: - it must be a double jump (as in real world implemenation) - you must push the data between the webserver and mysql server over a tcp/ip network - you must download the data from the webserver from another box via tcp/ip (no loopback/local socket connections - does not clearly represent a real scalable solution) Looking forward to some numbers. On Wed, 25 Apr 2007, Kevin Waterson wrote: > This one time, at band camp, [EMAIL PROTECTED] wrote: > > > > > I don't feel the implementation direction this article takes is good. It > > uses single row binary storage, which anyone who has had to deal with > > large files knows is a definate issue. > > According to your method of storing binary data in BLOB fields, rather than > LONGBLOBs you would have over 11,000 rows for a single 700MB iso. > > I am not sure how MySQL handles blobs internally but I would assume it is > like most other databases and adds some sort of delimiter to the data and > has to seek to it to distinguish columns. This means a 700Mb file stored > in 64k chunks would need over 11,000 file seeks per row read. > > LONGBLOBs are the way forward here I feel. > A single table containing the relevant metadata along with the binary data. > There is no need for any normalization as this is a one-to-one relationship. > > having over 11,000 rows for a single file.... I dunno, I would like to see > some benchmarks on this. > > Kevin > > > -- > "Democracy is two wolves and a lamb voting on what to have for lunch. > Liberty is a well-armed lamb contesting the vote." > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
