WordPress.org

1. jackthur
   Member
   Posted 11 months ago #

   Bluehost.com have twice suspended my hosting of a wordpress site because of slow mysql queries that look like they're being caused by the podpress stats function.

   Here is an example from the query log:

   # Wed Jun 22 00:01:04 2011
   # Query_time: 1.187042 Lock_time: 0.000108 Rows_sent: 3 Rows_examined: 541514
   use jackthur_wrdp2;
   SELECT method, COUNT(DISTINCT id) as downloads FROM wp_podpress_stats WHERE postID='196' AND media='bikeshow_20080630.mp3' GROUP BY method ORDER BY method ASC

   # Wed Jun 22 00:04:59 2011
   # Query_time: 1.192960 Lock_time: 0.000115 Rows_sent: 3 Rows_examined: 546121
   use jackthur_wrdp2;
   SELECT method, COUNT(DISTINCT id) as downloads FROM wp_podpress_stats WHERE postID='1081' AND media='bikeshow_20100607.mp3' GROUP BY method ORDER BY method ASC

   # Wed Jun 22 00:05:04 2011
   # Query_time: 1.298816 Lock_time: 0.000098 Rows_sent: 3 Rows_examined: 546033
   use jackthur_wrdp2;
   SELECT method, COUNT(DISTINCT id) as downloads FROM wp_podpress_stats WHERE postID='1004' AND media='bikeshow_20100118.mp3' GROUP BY method ORDER BY method ASC

   Any ideas on what is going wrong here and how I can prevent it once I've got my account unsuspended?

   I'm using Podpress ver 8.8.10.7 and WordPress ver 2.8.4

   thanks!

2. jackthur
   Member
   Posted 11 months ago #

   I've now had the site unsuspended and have upgraded the plugin to Version 8.8.10.8.

   I have set the stats method to WP Permalinks, Counts Only and disabled any third party stats.

   It looks to me as though wp_podpress_stats is an enormous db of each and every single download event. I'm not sure I need this much information, and so perhaps I can remove this.

   All I really want is a running total of the number of times each podcast has been downloaded.

   thanks again

3. jackthur
   Member
   Posted 11 months ago #

   And here's the size of the relevant db table:

   Table Name Creation Time Update Time Row Count Avg Row Length Data Size Index Size DB Engine
   wp_podpress_stats 2010-01-23 17:25:32 2011-06-22 06:28:40 538532 0.16 KB 84.69 MB 5296.00 KB MyISAM

4. ntm
   Member
   Posted 11 months ago #

   Yes, it is correct that if you have used Full or Full+ as the logging method podPress has logged every click on the player or a download link. Every click results in a new line in this.
   Depending on how popular your podcast is this table might get pretty big and the db queries slow.
   That is why it is not very recommendable to display the download numbers at each post.

   But you have already done the right thing. The logging method Counts Only stores the download numbers in the table wp_podpress_statcounts and it counts only the download and no further information. it uses only one row per media files and is the choice for all who have such a popular podcast as you.
   Furthermore Counts Only counts always regardless whether you choose Counts Only or one of the Full methods.

   You may clear the wp_podpress_stats table. It contains the data which gets collected with the Full and Full+ logging method. Clearing this table will do no harm. It sets back the results of Full/Full+ to zero but will not disturb the rest of the plugin.

   This is an relative old issue of the Full/Full+ logging method and it is definitely planned to do something about it and to update the statics feature.
   But until than the solution to problems like the one you have encountered is to switch to Counts Only and to not display the statistic number at the end of each podPress section in the posts.

   Regards,
   Tim

5. jackthur
   Member
   Posted 11 months ago #

   Brilliant. Very helpful and many thanks for your clear and very rapid reply.
   Jack

Reply

You must log in to post.