Home > HowTo, SQL > Statpress is SLOW

Statpress is SLOW

November 9th, 2009 Leave a comment Go to comments

Sorry all my website was brought down by my webhost.
I had a message a week ago that I did not recieve by email
saying my website was using to much CPU and they would disable it in a week,
so they did! And just now I got down to check what happened.

The probelm:
Queries in the DB of StatPress was taking way too long, my DB was only about 40mb.
But some simple queries would take 6 seconds to run.
Results that the table

The solution:

Fix the wp_statpress table, i was able to do it view phpMyAdmin (hope your ISP allows some kind of
connection to mysql DB).

These are the two main queries i have issues with after turning on my mysql logging

mysql> SELECT count(DISTINCT(ip)) as pageview FROM wp_statpress WHERE spider=” and feed=”;
+———-+
| pageview |
+———-+
| 3552 |
+———-+
1 row in set (1.84 sec)

mysql> SELECT date FROM wp_statpress ORDER BY date LIMIT 1;
+———-+
| date
+———-+
| 20081012 |
+———-+
1 row in set (4.54 sec)

i can’t be waiting ~6-7 seconds just for statpress to fire up, meanwhile my whole site gives the appearance of being hanged/frozen for a few seconds. this is before we even consider displaying any statpress information on the page, just purely having activated the statpress plugin.

i notice some strange things, we seem to query the “date” column a lot but column is not an index?

secondly, i haven’t been a sysadmin for a long time, so my mysql isnt the best, but it seems we are storing things like the date, which is formatted YYYMMDD and therefore is only ever going to occupy 8 chars which are all integers, in a TEXT column type? likewise with IPS, we know an IP can only use 4 x 3 + 3 = 15 characters, so why use a text column type when a char(15) would be way more efficient?? (in fact, the PROPER way i would think would be to convert the IP to it’s numeric format, and store that so it’s a int)

to me the table design makes no sense whatsoever. it seems like every column type has been made text with no real consideration for it’s usage.

mysql> desc wp_statpress;
+————–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+———+—————-+
| id | mediumint(9) | | PRI | NULL | auto_increment |
| date | text | YES | | NULL | |
| time | text | YES | | NULL | |
| ip | text | YES | | NULL | |
| urlrequested | text | YES | | NULL | |
| agent | text | YES | | NULL | |
| referrer | text | YES | | NULL | |
| search | text | YES | | NULL | |
| nation | text | YES | | NULL | |
| os | text | YES | | NULL | |
| browser | text | YES | | NULL | |
| searchengine | text | YES | | NULL | |
| spider | text | YES | | NULL | |
| feed | text | YES | | NULL | |
| user | text | YES | | NULL | |
| timestamp | text | YES | | NULL | |
+————–+————–+——+—–+———+—————-+
16 rows in set (0.00 sec)

mysql>

so consistently returning ~4 seconds for that latter query

1 row in set (4.19 sec)
1 row in set (3.95 sec)
1 row in set (4.24 sec)
1 row in set (4.41 sec)

ok. now, lets make some changes.

mysql> alter table wp_statpress modify date int(8) unsigned not null;
Query OK, 347943 rows affected (3.25 sec)
Records: 347943 Duplicates: 0 Warnings: 0

and re-issue the same query

mysql> select date from wp_statpress order by date limit 1;
+———-+
| date |
+———-+
| 20081012 |
+———-+
1 row in set (0.65 sec)

just a slight improvement !!!

ok lets make some more changes

mysql> alter table wp_statpress add key (date);
Query OK, 347943 rows affected (4.13 sec)
Records: 347943 Duplicates: 0 Warnings: 0

mysql> select date from wp_statpress order by date limit 1;
+———-+
| date |
+———-+
| 20081012 |
+———-+
1 row in set (0.00 sec)

so from 4.5 seconds, down to 0.00 seconds, all by setting the column to something more appropriate and indexing it.

lets have a crack at the other one

mysql> SELECT count(DISTINCT(ip)) as pageview FROM wp_statpress WHERE spider=” and feed=”;
+———-+
| pageview |
+———-+
| 3552 |
+———-+
1 row in set (1.72 sec)

mysql> alter table wp_statpress modify ip char(15) not null; Query OK, 347943 rows affected (4.39 sec) Records: 347943 Duplicates: 0 Warnings: 0

ok lets key it

mysql> alter table wp_statpress add key (ip);
Query OK, 347943 rows affected (5.97 sec)
Records: 347943 Duplicates: 0 Warnings: 0

and repeat the query a few times

mysql> SELECT count(DISTINCT(ip)) as pageview FROM wp_statpress WHERE spider=” and feed=”;
+———-+
| pageview |
+———-+
| 3552 |
+———-+
1 row in set (0.76 sec)

significantly better.
now i’m looking over some of the other tables – do i really need text for “spider” and “feed” aswell?

mysql> select spider from wp_statpress group by spider;
+——————–+
| spider |
+——————–+
| |
| AideRSS |
| Ask.com/Teoma |
| Baiduspider |
| BlogPulse |
| Gigablast |
| Google |
| Google Feedfetcher |
| Heritrix |
| Indy Library |
| Java |
| LarBin |
| Moreover |
| MSN |
| Naver |
| picsearch |
| relevantNOISE |
| Twiceler |
| Yahoo FeedSeeker |
| Yahoo! |
+——————–+
20 rows in set (2.57 sec)

certainly doesn’t look like it. given TEXT is 3x slower than VARCHAR, i fail to see any jusfitication for using this column type.
what about feed?

mysql> select feed from wp_statpress group by feed;
+————-+
| feed |
+————-+
| |
| COMMENT RSS |
| RSS2 |
+————-+
3 rows in set (2.45 sec)

again? TEXT? i dont get it..

mysql> alter table wp_statpress modify spider varchar(16);
Query OK, 347943 rows affected, 28 warnings (5.98 sec)
Records: 347943 Duplicates: 0 Warnings: 28

mysql> alter table wp_statpress modify feed varchar(16);
Query OK, 347943 rows affected (5.97 sec)
Records: 347943 Duplicates: 0 Warnings: 0

and do the same query as before

mysql> SELECT count(DISTINCT(ip)) as pageview FROM wp_statpress WHERE spider=” and feed=”;
+———-+
| pageview |
+———-+
| 3552 |
+———-+
1 row in set (0.64 sec)

marginally better again. so from original query 1.7 seconds down to .6 seconds

thanks to:
http://forum.irisco.it/topic.php?id=311

Member: bearmeister
ref:

  1. December 1st, 2009 at 18:39 | #1

    This is a great post! StatPress is an absolutely horrid plugin to use, in it’s default state. I’m a MySQL DBA for a major world-wide internet hosting company and I see people using StatPress on their WP blogs and it kills the performance of the server, and their site. I have to make the changes you mention day in and day out to keep people’s websites from killing the server, if it hasn’t already. As you mentioned, all data fields are by default TEXT data type, which is BAD and makes it more difficult to index.

    By changing a few data types and adding a couple indexes, I’ve taken queries from this plugin from examining 140k plus rows, down to examining less than 1800 rows, a 78% decrease in rows examined. I recommend “Google Analytics for WordPress” or “WordPress.com Stats” (the latter requires an API key from wordpress.com, the former is obviously used in conjunction with Google Analytics, which is a great stat tracking software).

  2. December 1st, 2009 at 19:09 | #2

    After poking around a bit with the plugin itself (I’m hoping to improve on it further and will let you know if/when I do), I retract my previous statement that this is a horrid plugin. The plugin itself is nice and has some great features and incredibly useful data. However, the way it goes about getting and storing that data is what is bad. The database design itself is bad and causes major problems, if left unchecked. However, if repaired, it can make this plugin much better and your site won’t suffer performance because of it. If I get around to fixing this plugin, I’ll be sure to post here.

  3. December 1st, 2009 at 19:14 | #3

    My apologies Felipe. You can delete my second comment, and this one. I did not find a way to contact you on your site so I’m contacting you here, since your comments are moderated. As I mentioned, I’m a MySQL DBA and have been surfing around sites mentioning StatPress and the problems people have had with it and trying to make them realize that StatPress is not a good plugin to use, in it’s default state. Making the changes you made obviously make it run better. However, I made some comments on some other sites that I didn’t make here, and my second comment was in response to those comments that I didn’t make here. Please delete this comment and my previous comment. Thanks.

    • December 3rd, 2009 at 14:07 | #4

      I dont like deleting your opinions, I believe they are very valid.
      As you say, statpress is a nice plugin but it does have a major problem
      on the way the DB is created causing a terrible perfomance problem.
      My site was turned off after my provided had a CPU problem because of
      StatPress fault. I hope it never happen again, and maybe next release it fixes this.
      cheers,
      Felipe

  1. November 11th, 2009 at 19:02 | #1
  2. June 28th, 2017 at 13:09 | #2

VAMOVE *

*