Ok, so some beta users of the site I'm developing have complained of poor performance and occasional timeouts on dynamic pages.I had occasionally noticed some slow response, so I decided to look into it a bit closer today.
A quick overview of the system.
RedHat Enterprise Edition 3.0
Apache 2.0.46
Mysql 3.23.58
Perl 5.8.0
mod_perl 1.99
perl-DBD-MySQL 2.1021
Dual 2.4GHz Xeon, 4GB RAM, 10K SCSI RAID 5, big pipe
So...
I put some simple timing traps in the cgi scripts ... just used time() so 1 second resolution.
I notice that it's often the first queries I do in a day that are slow. The site is basically idle right now, so I suspect that maybe the database connection pool maintained by perl-DBD has gotten stale, and the slowness is the DB connections being re-established. So I mess around with the site awhile to make sure everything is fresh.
Now I run the first query ... it has to search a character string, and the first time I run the query it takes 172 seconds to return 86 records. Now these aren't big records, and I just do some simple formatting on them. Each record returned also kicks off another simple query.
Now the strange part ... I run the query again, and it only takes 9 seconds. Run it again ... 1 second.
Ok, so it seems like something is getting cached. I run a bunch of other queries through it, and clear out the browser cache. Run it again ... 19 seconds. Run it again ... 1 second!!
Where is this getting cached? I don't think it's the table indexes in MySQL because other queries that would use the same indexes aren't faster on the first run through. It shouldn't be the queries themselves because I tried changing the capitalization and such on the queries and that didn't slow it down as if it were the same query. Besides, MySQL 3.23 doesn't do query caching AFAIK (4.0 can).
Any other ideas??
A quick overview of the system.
RedHat Enterprise Edition 3.0
Apache 2.0.46
Mysql 3.23.58
Perl 5.8.0
mod_perl 1.99
perl-DBD-MySQL 2.1021
Dual 2.4GHz Xeon, 4GB RAM, 10K SCSI RAID 5, big pipe
So...
I put some simple timing traps in the cgi scripts ... just used time() so 1 second resolution.
I notice that it's often the first queries I do in a day that are slow. The site is basically idle right now, so I suspect that maybe the database connection pool maintained by perl-DBD has gotten stale, and the slowness is the DB connections being re-established. So I mess around with the site awhile to make sure everything is fresh.
Now I run the first query ... it has to search a character string, and the first time I run the query it takes 172 seconds to return 86 records. Now these aren't big records, and I just do some simple formatting on them. Each record returned also kicks off another simple query.
Now the strange part ... I run the query again, and it only takes 9 seconds. Run it again ... 1 second.
Ok, so it seems like something is getting cached. I run a bunch of other queries through it, and clear out the browser cache. Run it again ... 19 seconds. Run it again ... 1 second!!
Where is this getting cached? I don't think it's the table indexes in MySQL because other queries that would use the same indexes aren't faster on the first run through. It shouldn't be the queries themselves because I tried changing the capitalization and such on the queries and that didn't slow it down as if it were the same query. Besides, MySQL 3.23 doesn't do query caching AFAIK (4.0 can).
Any other ideas??
