Thanks.
I forgot about disabling profiling. I was testing before. Never turned it back off. But it was on before the switch so...
Same exact server, just different OS VERSION (from fedora 14 to 15) and different MySQL version (as stated, 5.1 to 5.5).
On the old install i had 128M query cache size, i read your article about memory and switched it off on the new install; but it made no noticeable difference.
The cpu is x86_64 with 6 cores, and 4 gigs of memory.
i increased the innodb_buffer_pool (or whatever that option was) to 128M, with no noticeable difference.
Almost ALL of my tables are innodb.
I've already given up on 5.5 out of desperation, as i was about to launch my online business off this, and went back to Fedora 14 (with Query Browser, yeah! MySQL Workbench sucks for stored procedure management!), and it's fast as lightning again.
As far as a SP, i'll past one that was causing problems. Oh, while i'm thinking of it, i also noticed that the problem was MUCH worse in routines that created temporary tables. Not only that, but i repeatedly did a "show global status" while a page was taking 3 minutes to load, and it LOOKED like the routine was creating the tables OVER AND OVER for some reason; but this could have been from others hitting the same pages.
Here's one of them, and i know that i should be using an index on it, but that's not the point; as it's VERY fast in 5.1 (0.002), but can take a few minutes in 5.5:
DELIMITER $$
DROP FUNCTION IF EXISTS `ionisis`.`User_UAMS_Connection_Stats`$$
CREATE DEFINER=`ionisis`@`localhost` FUNCTION `User_UAMS_Connection_Stats`(sp_user bigint(20) unsigned,sp_visitor bigint(20) unsigned) RETURNS char(32) CHARSET utf8
READS SQL DATA
DETERMINISTIC
BEGIN
drop temporary table if exists t1;
drop temporary table if exists t2;
create temporary table if not exists t1 (eid bigint unsigned);
create temporary table if not exists t2 (eid bigint unsigned);
insert into t1 (eid) select pid1 from UAMS_Connection where pid2=sp_user and not pid1=sp_visitor and approved=1;
insert into t1 (eid) select pid2 from UAMS_Connection where pid1=sp_user and not pid2=sp_visitor and approved=1;
insert into t2 (eid) select eid from t1;
select count(eid) into @connections_total from t1;
select count(pid1) into @connections_mutual from UAMS_Connection where
(pid1=sp_visitor and pid2 in (select eid from t1)) or (pid2=sp_visitor and pid1 in (select eid from t2));
if (sp_user = sp_visitor) then
select true,true,3 into @is_self,@is_friend,@connections_status;
else
select false into @is_self;
if exists (select pid1 from UAMS_Connection where (pid1=sp_user and pid2=sp_visitor) or (pid1=sp_visitor and pid2=sp_user)) then
select true,2 into @is_friend,@connections_status;
else
select false into @is_friend;
if (@connections_mutual > 0) then
select 1 into @connections_status;
else
select 0 into @connections_status;
end if;
end if;
end if;
if exists (select pid1 from UAMS_Block where (pid1=sp_user and pid2=sp_visitor) or (pid1=sp_visitor and pid2=sp_user)) then
select true into @is_blocked;
else
select false into @is_blocked;
end if;
drop temporary table if exists t1;
drop temporary table if exists t2;
return concat(@connections_status,',',@is_self,',',@is_friend,',',@is_blocked,',',@connections_mutual,',',@connections_total);
END$$
DELIMITER ;
premium domain names that i'm selling