WP-ShortStat是数据库空间杀手  

很奇怪数据库空间为什么一直在快速增加，今天备份数据库的时候发现数据库已经到35M了，把备份文件看了下，发现wp_ss_stats表几乎占据了整个数据库的使用量，删除这个表的数据后，数据库空间下降到只有1M多一点，呵呵，果断的停用了这个插件，还是使用外部的访问统计好了。我想WP-ShortStat可能只适用与每天只有几个点击量的Blog？我的这个Blog的访问量也不是很大，每天的点击数最多300（Google Analytics统计数据），三个多月的数据量就有35M，要是一天的点击量上千，那还不是早暴了？

这是找到的鸟文资料,o(︶︿︶)o 唉

In this article, I present a simple method for dramatically decreasing the size of your WordPress database by partially emptying old data from the WP-ShortStat table via the following SQL command:

DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT n

That is the point of this entire article, which dips into just about everything one might need to know before employing such strategy. If you are familiar with SQL and understand the purpose and functionality of this command, feel free to grab, gulp and go. Otherwise, read on for the full story..

A little context, please..

Many WordPress users enjoy the convenient statistics provided by one of theexcellent ShortStat plugins. WP-ShortStat keeps track of many essential types of data: recent referrers, search queries, referring domains, keywords, locations, browsers, and many more. Over time, the copious amount of statistical data collected by WP-ShortStat increasingly inflates the size of your WordPress database.

 

For example, before installing WP-ShortStat, my WP database was around 8 megabytes in size. After installing ShortStat and using it for several months, the size of my database ballooned to well over 30 megabytes! WP-ShortStat uses these data sets to calculate cumulative totals, such as total hits, daily visits, referral counts, and various others.

Fortunately, I supplement the functionality of ShortStat with Mint, which provides all the statistical data I will ever need — without bloating the size of my WordPress database (Note: the Mint database is maintained independently of the WP database). Thus, my statistical strategy involves using Mint to record permanent, long-term data, while simultaneously using WP-ShortStat to track daily hits, referrals, and visitors ¹. At the end of the day, the ShortStat data is completely expendable and fails to justify its behemoth table size.

Nevertheless, even without running a secondary statistical package to supplement WP-ShortStat, many users would gladly trade their running tally of cumulative statistical data for a more lightweight and agile WP database. If this sounds like you, or if you really don’t care about all this long-winded nonsense, here is a simple way to reduce the overall size of your WordPress database by cleaning up your WP-ShortStat table.

Getting on with it, then..

Thanks to an informative discussion with Mark from c77studios.com, a single SQLcommand was forged to quickly and efficiently remove old data from the WP-ShortStat database table:

DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT n

As discussed in the original conversation, this command is a generalized SQL query that is independent of table data such as ID. Execution of this command will effectively delete the oldest n number of records from the WP-ShortStat table,wp_ss_stats. Further, the generalized syntax of this command enables us to automate the procedure at specified intervals via cron job, PHP, etc.

I use this snippet of SQL to shrink my WordPress database every month or so (kind of like shaving). Rather than try to explain the overall effect of this command, here are a few screenshots showing my WP-ShortStat panel and its associated wp_ss_statstable at various points throughout the process ¹:

I began with this hellishly sized table, which was over 180,000 rows thick and over 45,000 Kilobytes in size:

Two chop the table size in half, I divided the total number of records by two and entered the following query:

After executing the query, the oldest 97,257 records were removed from the table, effectively reducing its size by roughly half:

Within the WordPress Admin area, here is a portion of the WP-ShortStat panel beforedeleting any data:

After executing the query, the cumulative totals have been decreased by roughly half (as expected), while the most recent data remains intact:

Conclusion

As you can see, this simple query serves as an effective tool at reducing the the overall size of your WordPress database. Especially if you are fronting from a limited hosting plan, this trick may enable you to run one of the WP-ShortStat plugins without exceeding any limits. Keep in mind that, depending on how many records you decide to dump (based on the value of n), the amount of data that remains for various daily totals may also be affected. If you are unsure as to which value to use for n, determine the total number of entries via the cardinality value and then try using a relatively small n value; run the query, examine the results, and then try a slightly larger number. If you remember to backup your wp_ss_stats table before beginning, it is totally safe to experiment with different values for n until you determine the optimum value. If you happen to delete too much data from your table, simply restore the backup and try again. Either way, I wouldn’t stress too much — the table will eventually be refilled with more data 😉

分享到：