步骤5:测试新的表结构下的系统性能 本章节主要介绍测试新的表结构下的系统性能的最佳实践。 重新创建了具有存储方式、压缩级别、分布方式和分布列的测试数据集后,重新测试系统性能。 1.记录各表的存储使用情况。 使用pgsizepretty函数查询每张表使用的磁盘空间,并将结果记录到基准表中。 SELECT TNAME, PGSIZEPRETTY(PGRELATIONSIZE(tname)) FROM (VALUES('storesales'),('datedim'),('store'),('item'),('timedim'),('promotion'),('customerdemographics'),('customeraddress'),('householddemographics'),('customer'),('incomeband')) AS names1(tname); tname pgsizepretty + storesales 14 GB datedim 27 MB store 4352 kB item 259 MB timedim 14 MB promotion 3200 kB customerdemographics 11 MB customeraddress 27 MB householddemographics 1280 kB customer 111 MB incomeband 896 kB (11 rows) 2.测试查询性能,并将性能数据录入基准表中。 再次运行如下三个查询,并记录每个查询的耗费时间。 timing on SELECT FROM (SELECT COUNT() FROM storesales ,householddemographics ,timedim, store WHERE sssoldtimesk timedim.ttimesk AND sshdemosk householddemographics.hddemosk AND ssstoresk sstoresk AND timedim.thour 8 AND timedim.tminute > 30 AND householddemographics.hddepcount 5 AND store.sstorename 'ese' ORDER BY COUNT() ) LIMIT 100; SELECT FROM (SELECT ibrandid brandid, ibrand brand, imanufactid, imanufact, SUM(ssextsalesprice) extprice FROM datedim, storesales, item,customer,customeraddress,store WHERE ddatesk sssolddatesk AND ssitemsk iitemsk AND imanagerid8 AND dmoy11 AND dyear1999 AND sscustomersk ccustomersk AND ccurrentaddrsk caaddresssk AND substr(cazip,1,5) <> substr(szip,1,5) AND ssstoresk sstoresk GROUP BY ibrand ,ibrandid ,imanufactid ,imanufact ORDER BY extprice desc ,ibrand ,ibrandid ,imanufactid ,imanufact ) LIMIT 100; SELECT FROM (SELECT sstorename, sstoreid, SUM(CASE WHEN (ddayname'Sunday') THEN sssalesprice ELSE null END) sunsales, SUM(CASE WHEN (ddayname'Monday') THEN sssalesprice ELSE null END) monsales, SUM(CASE WHEN (ddayname'Tuesday') THEN sssalesprice ELSE null END) tuesales, SUM(CASE WHEN (ddayname'Wednesday') THEN sssalesprice ELSE null END) wedsales, SUM(CASE WHEN (ddayname'Thursday') THEN sssalesprice ELSE null END) thusales, SUM(CASE WHEN (ddayname'Friday') THEN sssalesprice ELSE null END) frisales, SUM(CASE WHEN (ddayname'Saturday') THEN sssalesprice ELSE null END) satsales FROM datedim, storesales, store WHERE ddatesk sssolddatesk AND sstoresk ssstoresk AND sgmtoffset 5 AND dyear 2000 GROUP BY sstorename, sstoreid ORDER BY sstorename, sstoreid,sunsales,monsales,tuesales,wedsales,thusales,frisales,satsales ) LIMIT 100; 下面的基准表显示了本次实践中所用集群的验证结果。您的结果可能会因多方面的原因而有所变化,但规律性应该相差不大。考虑到操作系统缓存的影响,相同表结构的同一查询在每次执行时耗时会有不同属正常现象,建议多测试几次,取一组平均值。 基准 优化前 优化后 加载时间(11张表) 341584ms 257241ms 占用存储 StoreSales 42GB 14GB DateDim 11MB 27MB Store 232kB 4352kB Item 110MB 259MB TimeDim 11MB 14MB Promotion 256kB 3200kB CustomerDemographics 171MB 11MB CustomerAddress 170MB 27MB HouseholdDemographics 504kB 1280kB Customer 441MB 111MB IncomeBand 88kB 896kB 总存储空间 42GB 15GB 查询执行时间 查询1 14552.05ms 1783.353ms 查询2 27952.36ms 14247.803ms 查询3 17721.15ms 11441.659ms 总执行时间 60225.56ms 27472.815ms 3.如果对表设计后的性能还有更高期望,可以运行EXPLAIN PERFORMANCE以查看执行计划进行调优。 关于执行计划的更详细介绍及查询优化请参考《开发指南》中的“SQL执行计划介绍”及“优化查询性能概述” 。
来自: