0
  • Files as BLOB in MySQL
  • Connection as root to MySQL from PHP
  • set_time_limit(0);ignore_user_abort(FALSE); at start of PHP Script
  • Using mysql_unbuffered_query from PHP
  • Getting each row and sending it to browser zipped (on-the-fly to save RAM memory and space on disk)
  • Some clients with slow connections (600Kb/s - 5Mb/s)
  • Getting rows, between it can pass more that 1 minute (content is send with flush())
  • SET GLOBAL wait_timeout=28800 and SET GLOBAL interactive_timeout=28800 before query execution
  • MySQL is closing the connection exactly in 60 seconds (When the download of file to client is more that 1 minute)
  • Query status in SHOW PROCESSLIST is "Writing to net" from start until it close.

What settings Can I adjust for prevent to MySQL closes the connection on inactivity on the middle of a mysql_unbuffered_query? Saving RAM Memory and disk.

Jose Nobile
  • 2,915
  • 2
  • 21
  • 28
  • Surely performing compression "on-the-fly" will use *more* RAM and disk space than performing it in advance? Furthermore, why store files in a *relational* database management system when you have a highly optimised database for file storage in your *filesystem*? – eggyal Sep 19 '13 at 01:03
  • @eggyal "on-the-fly" (file by file from MySQL -> PHP -> Browser) don't use any space on disk, the max memory use is the more big file (is fixed to < 8MB). About store files in MySQL as BLOB and not in the filesystem, it do not depend of filesystem (no problems about file perms, eg). But this is a topic that I would not want discuss here. – Jose Nobile Sep 19 '13 at 16:20
  • MySQL uses space on disk. If the data was pre-compressed, it would use less; likewise the RAM involved in fetching and then compressing a file will be greater. Permissions are enforced at your application layer, not in the storage layer. – eggyal Sep 19 '13 at 18:48
  • MySQL uses space on disk as filesystem. Data is not pre-compressed. No space disk is used in zip process. – Jose Nobile Sep 20 '13 at 20:51

0 Answers0