使用SQL FileStream的内存泄漏 [英] Memory leak using SQL FileStream

问题描述

我有一个使用SQL FILESTREAM存储图像的应用程序.我插入了很多张图片(每天有几百万张图片).

I have an application that uses a SQL FILESTREAM to store images. I insert a LOT of images (several millions images per days).

过一会儿，机器停止响应并且似乎内存不足...查看PC的内存使用情况，我们看不到任何进程占用大量内存(SQL或我们的应用程序都没有).我们试图杀死我们的进程，但是它没有还原我们的机器...然后我们杀死了SQL服务，并且它没有还原到系统.作为最后的选择，我们甚至杀死了所有进程(系统进程除外)，并且内存仍然很高(我们正在任务管理器的性能"选项卡中查找).此时只有重新启动才能执行此工作.我们已经在Win7，WinXP，Win2K3服务器上尝试了相同的结果.

After a while, the machine stops responding and seem to be out of memory... Looking at the memory usage of the PC, we don't see any process taking a lot of memory (neither SQL or our application). We tried to kill our process and it didn't restore our machine... We then kill the SQL services and it didn't not restore to system. As a last resort, we even killed all processes (except the system ones) and the memory still remained high (we are looking in the task manager's performance tab). Only a reboot does the job at that point. We have tried on Win7, WinXP, Win2K3 server with always the same results.

不幸的是，这不是一次性的交易，它每次都会发生.

Unfortunately, this isn't a one-shot deal, it happens every time.

以前有人见过这种行为吗?使用SQL FILESTREAMS，我们在做错什么吗?

Has anybody seen that kind of behaviour before? Are we doing something wrong using the SQL FILESTREAMS?

推荐答案

您说每天插入很多图像.您还对图像做什么?您会更新它们吗，很多读物?

You say you insert a lot of images per day. What else do you do with the images? Do you update them, many reads?

您的文件系统是否针对FILESTREAM进行了优化?

Is your file system optimized for FILESTREAMs?

您如何读出图像?

如果进行了大量更新，请记住，SQL Server不会修改文件流对象，而是创建一个新对象，并将旧对象标记为由垃圾收集器删除.在某些时候，GC将触发并开始清理旧的混乱情况. FILESTREAM的问题在于它不会大量记录到事务日志中，因此GC可能会严重延迟.如果这是问题所在，则可以通过更频繁地强制GC保持响应性来解决.可以使用 CHECKPOINT 语句来完成.

If you do a lot of updates, remember that SQL Server will not modify the filestream object but create a new one and mark the old for deletion by the garbage collector. At some time the GC will trigger and start cleaning up the old mess. The problem with FILESTREAM is that it doesn't log a lot to the transaction log and thus the GC can be seriously delayed. If this is the problem it might be solved by forcing GC more often to maintain responsiveness. This can be done using the CHECKPOINT statement.

更新:您不应将FILESTREAM用于较小的文件(小于1 MB).数百万个小文件将导致文件系统和主文件表出现问题.请改用varbinary.另请参见设计和实现FILESTREAM存储

UPDATE: You shouldn't use FILESTREAM for small files (less than 1 MB). Millions of small files will cause problems for the filesystem and the Master File Table. Use varbinary in stead. See also Designing and implementing FILESTREAM storage

更新2:如果您仍然坚持使用FILESTREAM进行存储(不应存储大量小文件)，则至少必须相应地配置文件系统.

UPDATE 2: If you still insist on using the FILESTREAM for storage (you shouldn't for large amounts of small files), you must at least configure the file system accordingly.

针对大量小文件优化文件系统(使用这些技巧，并确保在应用之前了解它们的作用)

Optimize the file system for large amount of small files (use these as tips and make sure you understand what they do before you apply)

• 更改主文件表 注册表中保留最大容量(FSUTIL.exe行为设置为mftzone 4)
• 禁用8.3文件名(fsutil.exe行为设置为disable8dot3 1)
• 禁用上次访问更新(fsutil.exe行为设置disablelastaccess 1)
• 重新启动并创建一个新分区
• 使用 块大小将适合大多数 文件(2k或4k，具体取决于您 图片文件).

• Change the Master File Table reservation to maximum in registry (FSUTIL.exe behavior set mftzone 4)
• Disable 8.3 file names (fsutil.exe behavior set disable8dot3 1)
• Disable last access update(fsutil.exe behavior set disablelastaccess 1)
• Reboot and create a new partition
• Format the storage volumes using a block size that will fit most of the files (2k or 4k depending on you image files).

这篇关于使用SQL FileStream的内存泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！