本文的内容已经过时，6.7.0以上的Photo Station已经没有严重的性能问题了。
我折腾过不少家用NAS方案，包括最早的WD My Book World Edition，后来的Joggler，再到后来的Raspberry Pi，这些方案除了性能存在一些问题以外，最大的缺点就是易用性存在问题，不但非“专业”人士用起来存在困难，就连我自己也对土法泡制的照片管理功能感到不满。直到两年前入了群晖（Synology）的家用NAS DS214play，事情才变得安逸起来。
平心而论，Synology的系统虽然功能强大，体验也还不错，但细节上做得其实真是挺糙的。最近就发现了它的照片管理软件PhotoStation出现了严重的性能问题，在照片库里只存了8万余张照片的情况下，打开首页要花费的时间已经超过了20秒，每打开一个文件夹都需要等待10秒以上，几乎不可用了。
以下分析以DSM 6.0为例，PhotoStation版本为6.4-3166。PhotoStation的安装路径为/volume1/@appstore/PhotoStation。
Synology的DSM系统，后台使用PostgreSQL数据库，前端是PHP页面。简单推理一下就可以知道，PhotoStation的性能瓶颈主要是在对照片索引数据库的访问上。性能调优的第一步就是先要找到哪些SQL查询占用了太多的时间。打开PostgreSQL记录SQL查询的开关，并查看所有SQL执行情况：
$ sudo su postgres
$ vi ~/postgresql.conf
log_statement = 'all'
$ psql photo postgres
# SELECT pg_reload_conf();
# \q
$ exit
$ sudo tail -f /var/log/postgresql.log
通过查看SQL执行记录，很容易发现几个明显的慢查询：
1.
SELECT COUNT(*) as total FROM photo_image;
SELECT COUNT(*) as total FROM video;
2.
SELECT COUNT(logid), MAX(logid) FROM photo_log;
3.
SELECT * FROM (
SELECT path AS filename, timetaken AS takendate,
create_time AS createdate, 'photo' AS type
FROM photo_image
WHERE path LIKE '/volume1/photo/%'
AND path NOT LIKE '/volume1/photo/%/%' AND disabled = 'f'
UNION
SELECT path AS filename, mdate AS takendate,
date AS createdate, 'video' AS type
FROM video
WHERE path LIKE '/volume1/photo/%'
AND path NOT LIKE '/volume1/photo/%/%'
AND disabled = 'f'
) AS totalCount;
4.
SELECT path, resolutionx, resolutiony, version FROM (
SELECT path, resolutionx, resolutiony, version,
create_time, privilege_shareid, disabled
FROM photo_image WHERE privilege_shareid IN (
SELECT shareid FROM photo_share WHERE ref_shareid = (
SELECT shareid FROM photo_share WHERE sharename = '2016'))
AND disabled = 'f'
UNION ALL
SELECT path, resolutionx, resolutiony, 0 AS version,
date AS create_time, privilege_shareid, disabled
FROM video WHERE privilege_shareid IN (
SELECT shareid FROM photo_share WHERE ref_shareid = (
SELECT shareid FROM photo_share WHERE sharename = '2016'))
AND disabled = 'f') temp
ORDER BY create_time DESC LIMIT 1;
优化的思路很简单，由于PhotoStation在正常情况下访问数据库所需要的读性能是远远大于写性能的，所以就通过牺牲写性能来逐一击破上面这些慢查询：
1. 程序的目的就是想知道系统中有多少张照片和多少个视频（而且其实并不需要精确知道，差不多就行），可惜对于PostgreSQL来说，由于它采用MVCC来解决并发问题，SELECT COUNT(*)是一个需要进行全表扫描的慢操作。解决方案就是用另外用一张表来存这两个表的总记录条数，并在原表上添加触发器来更新记录数。
CREATE TABLE photo_count (table_oid Oid PRIMARY KEY, count int);
ALTER TABLE photo_count OWNER TO "PhotoStation";
CREATE FUNCTION count_increment() RETURNS TRIGGER AS $_$
BEGIN
UPDATE photo_count SET count = count + 1 WHERE table_oid = TG_RELID;
RETURN NEW;
END $_$ LANGUAGE 'plpgsql';
CREATE FUNCTION count_decrement() RETURNS TRIGGER AS $_$
BEGIN
UPDATE photo_count SET count = count - 1 WHERE table_oid = TG_RELID;
RETURN NEW;
END $_$ LANGUAGE 'plpgsql';
CREATE TRIGGER photo_image_increment_trig AFTER INSERT ON photo_image
FOR EACH ROW EXECUTE PROCEDURE count_increment();
CREATE TRIGGER photo_image_decrement_trig AFTER DELETE ON photo_image
FOR EACH ROW EXECUTE PROCEDURE count_decrement();
CREATE TRIGGER video_increment_trig AFTER INSERT ON video
FOR EACH ROW EXECUTE PROCEDURE count_increment();
CREATE TRIGGER video_decrement_trig AFTER DELETE ON video
FOR EACH ROW EXECUTE PROCEDURE count_decrement();
INSERT INTO photo_count VALUES
('photo_image'::regclass, (SELECT COUNT(*) FROM photo_count));
INSERT INTO photo_count VALUES
('video'::regclass, (SELECT COUNT(*) FROM video));
然后在PHP程序中修改需要统计表数记录数的逻辑，在这里可以看到似乎同一个Session中只会查一次，但即使就是这一次，也已经慢得让人不开心了：
diff --git a/photo/include/file.php b/photo/include/file.php
index 541c5cb..7caa5de 100755
--- a/photo/include/file.php
+++ b/photo/include/file.php
@@ -536,8 +536,11 @@ class File {
if ($key && isset($_SESSION[SYNOPHOTO_ADMIN_USER][$key])) {
return $_SESSION[SYNOPHOTO_ADMIN_USER][$key];
}
- $query = "SELECT count(*) as total FROM $table";
-
+ if ('photo_image' == $table || 'video' == $table) {
+ $query = "SELECT count as total FROM photo_count where table_oid='$table'::regclass";
+ } else {
+ $query = "SELECT count(*) as total FROM $table";
+ }
$result = PHOTO_DB_Query($query);
if (!$result) {
// db query fail, won't update session value
2. 其实可以用跟前一个问题类似的方法去解决。但是这个其实只是一个没太多用处的操作日志表，所以我用更为简单粗暴的方法去解决这个问题：减少在数据库中保留日志的条数。直接修改PHP程序：
diff --git a/photo/include/log.php b/photo/include/log.php
index 1c982af..56385db 100644
--- a/photo/include/log.php
+++ b/photo/include/log.php
@@ -1,8 +1,8 @@
<?php
class PhotoLog {
- const LIMIT = 100000;
- const PURGECOUNT = 10000;
+ const LIMIT = 1000;
+ const PURGECOUNT = 100;
public static $SupportFormat = array("html", "csv");
public static function Debug($msg)
3. 第三个问题主要体现在对照片路径的处理上，为了选出位于某个路径下（不含子目录）的照片，程序采用了path LIKE ‘/path/%’ AND path NOT LIKE ‘/path/%/%’这样的查询条件。其实PostgreSQL在一定程度上是可以利用path字段上的索引来很好的优化这个查询的，但是实际运行中发现（通过在PostgreSQL的客户端中用explain和explain analyze分析查询）在某些情况下索引会失效，造成非常差的查询性能。解决方案还是用写性能来换读性能，先在表上加一个dirname字段并建立索引，按path算好文件所在的目录名写入dirname，然后把查询条件改为对dirname的查询，避免使用通配符和LIKE运算即可：
ALTER TABLE photo_image ADD COLUMN dirname TEXT NOT NULL DEFAULT '';
UPDATE photo_image
SET dirname = LEFT(path,LENGTH(path)-STRPOS(REVERSE(path),'/')+1);
ALTER TABLE video ADD COLUMN dirname TEXT NOT NULL DEFAULT '';
UPDATE video SET dirname = LEFT(path,LENGTH(path)-STRPOS(REVERSE(path),'/')+1);
CREATE INDEX dirname_index ON photo_image USING btree(dirname);
CREATE INDEX dirname_index ON video USING btree(dirname);
CREATE OR REPLACE FUNCTION set_dirname()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
NEW.dirname := LEFT(NEW.path,LENGTH(NEW.path)-STRPOS(REVERSE(NEW.path),'/')+1);
RETURN NEW;
END $function$
CREATE TRIGGER set_dirname_trigger
BEFORE INSERT OR UPDATE ON photo_image
FOR EACH ROW
EXECUTE PROCEDURE set_dirname();
CREATE TRIGGER set_dirname_trigger
BEFORE INSERT OR UPDATE ON video
FOR EACH ROW
EXECUTE PROCEDURE set_dirname();
同时修改PHP程序：
diff --git a/photo/include/photo/synophoto_csPhotoDB.php b/photo/include/photo/synophoto_csPhotoDB.php
index ac8f932..43e58ee 100755
--- a/photo/include/photo/synophoto_csPhotoDB.php
+++ b/photo/include/photo/synophoto_csPhotoDB.php
@@ -1607,10 +1607,8 @@ class csSYNOPhotoDB {
} else {
$albumRealPath = self::EscapeLikeParam(SYNOPHOTO_SERVICE_REAL_DIR_PATH."{$albumName}/");
}
- $cond = "path LIKE ? {$this->escapeStr} AND path NOT LIKE ? {$this->escapeStr} AND disabled = 'f' ";
- array_push($pathSqlParam, "{$albumRealPath}%");
- array_push($pathSqlParam, "{$albumRealPath}%/%");
-
+ $cond = "dirname = ? {$this->escapeStr} AND disabled = 'f' ";
+ array_push($pathSqlParam, "{$albumRealPath}");
if (!$removePhoto) {
$photoQuery = "SELECT path as filename, timetaken as takendate, create_time as createdate, 'photo' as type
FROM photo_image WHERE $cond";
$sqlParam = array_merge($sqlParam, $pathSqlParam);
4. 这个查询只是为了查询一个目录及其所有子目录中最新一个照片或视频，用其缩略图来作为目录的封面图片。群晖的工程师自己也知道这个查询很慢，所以还在程序中加了个逻辑，当照片视频数量大于200000时，放弃按日期排序，直接随机选一张。然而，这个查询实际上是可以简单优化的，明明不需要把所有的照片视频UNION到一起后再找出最新的一个，可以直接分别找出最新的照片和最新的视频，然后再到这两个中去取一个相对更新的就可以了。直接修改PHP代码实现：
diff --git a/photo/include/photo/synophoto_csPhotoAlbum.php b/photo/include/photo/synophoto_csPhotoAlbum.php
index ca128f0..f0e57e7 100755
--- a/photo/include/photo/synophoto_csPhotoAlbum.php
+++ b/photo/include/photo/synophoto_csPhotoAlbum.php
@@ -145,9 +145,11 @@ class csSYNOPhotoAlbum {
$cond .= " AND disabled = 'f'";
$table = "(" .
- "SELECT path, resolutionx, resolutiony, version, create_time, privilege_shareid, disabled FROM pho
to_image WHERE $cond " .
+ "(SELECT path, resolutionx, resolutiony, version, create_time, privilege_shareid, disabled FRO
M photo_image WHERE $cond " .
+ "ORDER BY create_time DESC LIMIT 1)" .
"UNION ALL " .
- "SELECT path, resolutionx, resolutiony, 0 as version, date as create_time, privilege_shareid, disa
bled FROM video WHERE $cond " .
+ "(SELECT path, resolutionx, resolutiony, 0 as version, date as create_time, privilege_shareid,
disabled FROM video WHERE $cond " .
+ "ORDER BY create_time DESC LIMIT 1)" .
") temp";
// this may cost lots of time, so it won't sort by create_time if the total count exceeds the threshold (200,000)
做完以上优化，我的PhotoStation已经基本可以做到点进文件夹秒开了，至少我自己已经比较满意了。声明一下，其实我并不太懂数据库相关理论和技术，以上“优化”只能说是在我自己的实验中起到了优化的效果，也许其中一些并不太科学，希望这篇文章能起到抛砖引玉的作用。
重新安装PhotoStation或升级DSM系统会造成我们对程序所作的修改丢失，所以在修改完成后，务必做好备份。
另外，适时对PostgreSQL数据库进行VACUUM操作似乎可以起到提高访问性能的目的，尤其是在做过大量照片更新后。
