MySQL Forums
Forum List  »  Performance

Re: Upgrade to MySQL 5.5.12 (fedora), now queries are Extremely slow?
Posted by: Vector Thorn
Date: June 06, 2011 11:27PM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.