Re: Major Performance Degradation after replacing Hard Drive
Thanks all for the suggestions. I *think* I've finally figured it out. At the end of the day I think it was because the new drives were formatted with ext3. I recreated them in ext2 and performance is back to where it was on those queries.. I hadn't realised that ext3 would give that much of a degradation with journaling! This page gives a rough indication of the time spent in various queries on one of my runs. http://stats.free-dc.org/stats.php?page=statsrun&proj=fah You can see after 12pm on sunday 20th when I installed the drives as ext3 and then 8pm tonight (PST) where I put them to ext2. Users RankRAC and rankinteams was dramatically dfferent. Phil On Mon, Jul 21, 2008 at 12:39 PM, Wm Mussatto <[EMAIL PROTECTED]> wrote: > On Mon, July 21, 2008 09:14, Brent Baisley wrote: > > Copying 5GB files shows you what kind of performance you would get for > > working with say video, or anything with large contiguous files. > > Database access tends to be random, so you want a drive with faster > > random access, not streaming speed. Try copying thousands of small > > files and compare the speeds. > > > > One odd thing to check is if the "old" drive supports command queueing > > and the new one does not. I assume that are both SATA drives. All SCSI > > drives support command queueing and it can make a huge difference > > depending on access patterns. > Also that its turned ON. > > Brent > > > > On Mon, Jul 21, 2008 at 8:42 AM, Phil <[EMAIL PROTECTED]> wrote: > >> Nothing else running and no queries go against that table, it's > >> effectively > >> created just for this, so I would expect the table lock. > >> > >> Show (full) processlist has nothing but this running.. > >> > >> Confirmed the faster disks by copying 5Gb files between two of the same > >> type > >> of disk (I installed two of them). 2xfaster than previous disks. > >> > >> my.cnf > >> > >> [mysqld] > >> datadir=/var/lib/mysql > >> socket=/var/lib/mysql/mysql.sock > >> user=mysql > >> # Default to using old password format for compatibility with mysql 3.x > >> # clients (those using the mysqlclient10 compatibility package). > >> old_passwords=1 > >> key_buffer_size=3072M > >> max_allowed_packet=16M > >> thread_stack=128K > >> thread_cache_size=64 > >> thread_concurrency=8 > >> sort_buffer_size=32M > >> join_buffer_size=3M > >> read_buffer_size=16M > >> query_cache_size=64M > >> query_cache_limit=8M > >> table_cache=300 > >> max_connections=500 > >> max_heap_table_size=1024M > >> tmp_table_size=1024M > >> myisam_sort_buffer_size=128M > >> wait_timeout=3000 > >> > >> set-variable=long_query_time=6 > >> log-slow-queries=/var/log/mysql-slow-queries.log > >> > >> 8Gb Ram on this machine which is an intel quad core. > >> > >> Anything else I'm missing? It's *possible* a colleague had changed the > >> my.cnf in the last few weeks and mysqld hadn't been restarted, but I > >> don't > >> see anything obvious in there and he can't remember. > >> > >> :( > >> > >> > >> > >> On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> > wrote: > >> > >>> Is there any other job running while the update is happening. Because, > >>> myisam does a table level lock. Please check the "show full > >>> processlist". > >>> > >>> Also run mysqladmin -uroot -pxxx status. This would write lock > >>> information > >>> into the machine.err log file. Check in this file also if there is any > >>> locking happening. > >>> > >>> R u sure, this disk is a FASTER disk then the earlier one. > >>> > >>> > >>> > >>> > >>> On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > Hi All, > > > Given a fairly simple table as follows > > CREATE TABLE `common_userx2` ( > `t_proj` char(6) default NULL, > `t_id` int(11) NOT NULL default '0', > `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL > default '', > `t_country` varchar(50) NOT NULL default '', > `t_cpid` varchar(50) NOT NULL default '', > `t_url` varchar(50) default NULL, > `t_create_date` int(11) default NULL, > `t_create_time` bigint(20) NOT NULL, > `t_has_profile` char(1) NOT NULL, > `t_team0` int(11) default NULL, > `t_metric1` double(20,6) NOT NULL default '0.00', > `t_metric2` double NOT NULL default '0', > `t_metric3` double NOT NULL default '0', > `t_metric4` double default NULL, > `t_active` char(1) NOT NULL default '', > `t_rev_metric1` double(20,6) NOT NULL default '100.00', > `t_projrank0` int(11) default NULL, > `t_rev_metric2` double(20,6) NOT NULL default '100.00', > `t_racrank0` int(11) default NULL, > `t_teamrank0` int(11) default NULL, > `t_countryrank0` int(11) default NULL, > `t_createdaterank0` int(11) default NULL, > PRIMARY KEY (`t_id`), > KEY `prank` (`t_rev_metric1`,`t_id`), > KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), > KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), > KE
Re: Major Performance Degradation after replacing Hard Drive
On Mon, July 21, 2008 09:14, Brent Baisley wrote: > Copying 5GB files shows you what kind of performance you would get for > working with say video, or anything with large contiguous files. > Database access tends to be random, so you want a drive with faster > random access, not streaming speed. Try copying thousands of small > files and compare the speeds. > > One odd thing to check is if the "old" drive supports command queueing > and the new one does not. I assume that are both SATA drives. All SCSI > drives support command queueing and it can make a huge difference > depending on access patterns. Also that its turned ON. > Brent > > On Mon, Jul 21, 2008 at 8:42 AM, Phil <[EMAIL PROTECTED]> wrote: >> Nothing else running and no queries go against that table, it's >> effectively >> created just for this, so I would expect the table lock. >> >> Show (full) processlist has nothing but this running.. >> >> Confirmed the faster disks by copying 5Gb files between two of the same >> type >> of disk (I installed two of them). 2xfaster than previous disks. >> >> my.cnf >> >> [mysqld] >> datadir=/var/lib/mysql >> socket=/var/lib/mysql/mysql.sock >> user=mysql >> # Default to using old password format for compatibility with mysql 3.x >> # clients (those using the mysqlclient10 compatibility package). >> old_passwords=1 >> key_buffer_size=3072M >> max_allowed_packet=16M >> thread_stack=128K >> thread_cache_size=64 >> thread_concurrency=8 >> sort_buffer_size=32M >> join_buffer_size=3M >> read_buffer_size=16M >> query_cache_size=64M >> query_cache_limit=8M >> table_cache=300 >> max_connections=500 >> max_heap_table_size=1024M >> tmp_table_size=1024M >> myisam_sort_buffer_size=128M >> wait_timeout=3000 >> >> set-variable=long_query_time=6 >> log-slow-queries=/var/log/mysql-slow-queries.log >> >> 8Gb Ram on this machine which is an intel quad core. >> >> Anything else I'm missing? It's *possible* a colleague had changed the >> my.cnf in the last few weeks and mysqld hadn't been restarted, but I >> don't >> see anything obvious in there and he can't remember. >> >> :( >> >> >> >> On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >>> Is there any other job running while the update is happening. Because, >>> myisam does a table level lock. Please check the "show full >>> processlist". >>> >>> Also run mysqladmin -uroot -pxxx status. This would write lock >>> information >>> into the machine.err log file. Check in this file also if there is any >>> locking happening. >>> >>> R u sure, this disk is a FASTER disk then the earlier one. >>> >>> >>> >>> >>> On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: Hi All, Given a fairly simple table as follows CREATE TABLE `common_userx2` ( `t_proj` char(6) default NULL, `t_id` int(11) NOT NULL default '0', `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `t_country` varchar(50) NOT NULL default '', `t_cpid` varchar(50) NOT NULL default '', `t_url` varchar(50) default NULL, `t_create_date` int(11) default NULL, `t_create_time` bigint(20) NOT NULL, `t_has_profile` char(1) NOT NULL, `t_team0` int(11) default NULL, `t_metric1` double(20,6) NOT NULL default '0.00', `t_metric2` double NOT NULL default '0', `t_metric3` double NOT NULL default '0', `t_metric4` double default NULL, `t_active` char(1) NOT NULL default '', `t_rev_metric1` double(20,6) NOT NULL default '100.00', `t_projrank0` int(11) default NULL, `t_rev_metric2` double(20,6) NOT NULL default '100.00', `t_racrank0` int(11) default NULL, `t_teamrank0` int(11) default NULL, `t_countryrank0` int(11) default NULL, `t_createdaterank0` int(11) default NULL, PRIMARY KEY (`t_id`), KEY `prank` (`t_rev_metric1`,`t_id`), KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), KEY `racrank` (`t_rev_metric2`,`t_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a ranking update statement as follows set @rank = 0; update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by t_rev_metric1,t_id; For the largest case this has close to 1M rows. For weeks it was taking around 10seconds to do this. Yesterday I replaced the main data drive in the machine with a faster SATA Raptor drive. No problems occurred, but since then (and the subsequent reboot of the machine) this particular query is taking 45 minutes! I can't, for the life of me figure out why performance would be degraded so much. At first I thought perhaps it might be just disk/mysql caching but the performance has not increased any in subsequent runs. Any advice on where to look ? >
Re: Major Performance Degradation after replacing Hard Drive
At 11:14 AM 7/21/2008, you wrote: Copying 5GB files shows you what kind of performance you would get for working with say video, or anything with large contiguous files. Database access tends to be random, so you want a drive with faster random access, not streaming speed. Try copying thousands of small files and compare the speeds. One odd thing to check is if the "old" drive supports command queueing and the new one does not. I assume that are both SATA drives. All SCSI drives support command queueing and it can make a huge difference depending on access patterns. Brent There is an excellent HD benchmarking program called HD Tach and is available at http://www.simplisoftware.com/Public/index.php?request=HdTach. They have a large list of benchmarked drives so you can see how well your drive compares to those listed. Compare performance for random access, or sequential reads/writes. It is a must have if you need the fastest drives possible. They have a free version or a registered version. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Major Performance Degradation after replacing Hard Drive
Copying 5GB files shows you what kind of performance you would get for working with say video, or anything with large contiguous files. Database access tends to be random, so you want a drive with faster random access, not streaming speed. Try copying thousands of small files and compare the speeds. One odd thing to check is if the "old" drive supports command queueing and the new one does not. I assume that are both SATA drives. All SCSI drives support command queueing and it can make a huge difference depending on access patterns. Brent On Mon, Jul 21, 2008 at 8:42 AM, Phil <[EMAIL PROTECTED]> wrote: > Nothing else running and no queries go against that table, it's effectively > created just for this, so I would expect the table lock. > > Show (full) processlist has nothing but this running.. > > Confirmed the faster disks by copying 5Gb files between two of the same type > of disk (I installed two of them). 2xfaster than previous disks. > > my.cnf > > [mysqld] > datadir=/var/lib/mysql > socket=/var/lib/mysql/mysql.sock > user=mysql > # Default to using old password format for compatibility with mysql 3.x > # clients (those using the mysqlclient10 compatibility package). > old_passwords=1 > key_buffer_size=3072M > max_allowed_packet=16M > thread_stack=128K > thread_cache_size=64 > thread_concurrency=8 > sort_buffer_size=32M > join_buffer_size=3M > read_buffer_size=16M > query_cache_size=64M > query_cache_limit=8M > table_cache=300 > max_connections=500 > max_heap_table_size=1024M > tmp_table_size=1024M > myisam_sort_buffer_size=128M > wait_timeout=3000 > > set-variable=long_query_time=6 > log-slow-queries=/var/log/mysql-slow-queries.log > > 8Gb Ram on this machine which is an intel quad core. > > Anything else I'm missing? It's *possible* a colleague had changed the > my.cnf in the last few weeks and mysqld hadn't been restarted, but I don't > see anything obvious in there and he can't remember. > > :( > > > > On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > >> Is there any other job running while the update is happening. Because, >> myisam does a table level lock. Please check the "show full processlist". >> >> Also run mysqladmin -uroot -pxxx status. This would write lock information >> into the machine.err log file. Check in this file also if there is any >> locking happening. >> >> R u sure, this disk is a FASTER disk then the earlier one. >> >> >> >> >> On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: >>> >>> Hi All, >>> >>> >>> Given a fairly simple table as follows >>> >>> CREATE TABLE `common_userx2` ( >>> `t_proj` char(6) default NULL, >>> `t_id` int(11) NOT NULL default '0', >>> `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL >>> default '', >>> `t_country` varchar(50) NOT NULL default '', >>> `t_cpid` varchar(50) NOT NULL default '', >>> `t_url` varchar(50) default NULL, >>> `t_create_date` int(11) default NULL, >>> `t_create_time` bigint(20) NOT NULL, >>> `t_has_profile` char(1) NOT NULL, >>> `t_team0` int(11) default NULL, >>> `t_metric1` double(20,6) NOT NULL default '0.00', >>> `t_metric2` double NOT NULL default '0', >>> `t_metric3` double NOT NULL default '0', >>> `t_metric4` double default NULL, >>> `t_active` char(1) NOT NULL default '', >>> `t_rev_metric1` double(20,6) NOT NULL default '100.00', >>> `t_projrank0` int(11) default NULL, >>> `t_rev_metric2` double(20,6) NOT NULL default '100.00', >>> `t_racrank0` int(11) default NULL, >>> `t_teamrank0` int(11) default NULL, >>> `t_countryrank0` int(11) default NULL, >>> `t_createdaterank0` int(11) default NULL, >>> PRIMARY KEY (`t_id`), >>> KEY `prank` (`t_rev_metric1`,`t_id`), >>> KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), >>> KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), >>> KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), >>> KEY `racrank` (`t_rev_metric2`,`t_id`) >>> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 >>> >>> I have a ranking update statement as follows >>> >>> set @rank = 0; >>> update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by >>> t_rev_metric1,t_id; >>> >>> For the largest case this has close to 1M rows. >>> >>> For weeks it was taking around 10seconds to do this. Yesterday I replaced >>> the main data drive in the machine with a faster SATA Raptor drive. No >>> problems occurred, but since then (and the subsequent reboot of the >>> machine) >>> this particular query is taking 45 minutes! >>> >>> I can't, for the life of me figure out why performance would be degraded >>> so >>> much. At first I thought perhaps it might be just disk/mysql caching but >>> the >>> performance has not increased any in subsequent runs. >>> >>> Any advice on where to look ? >>> >>> Phil >>> >>> -- >>> Help build our city at http://free-dc.myminicity.com ! >>> >> >> > > > -- > Help build our city at http://free-dc.myminicity.com ! > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Major Performance Degradation after replacing Hard Drive
Possibly.. top - 07:52:58 up 18:04, 3 users, load average: 4.98, 4.09, 3.20 Tasks: 165 total, 3 running, 162 sleeping, 0 stopped, 0 zombie Cpu0 : 0.0% us, 0.0% sy, 100.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu1 : 0.0% us, 0.3% sy, 0.0% ni, 0.0% id, 96.3% wa, 0.7% hi, 2.7% si Cpu2 : 0.0% us, 0.3% sy, 99.7% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 0.0% id, 100.0% wa, 0.0% hi, 0.0% si Mem: 8168104k total, 7694556k used, 473548k free,31040k buffers Swap: 2008084k total, 160k used, 2007924k free, 6044284k cached Phil On Mon, Jul 21, 2008 at 9:41 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > when you run this update, what is the IO WAIT from the top command. > > regards > anandkl > > > On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > > > Nothing else running and no queries go against that table, it's > effectively > > created just for this, so I would expect the table lock. > > > > Show (full) processlist has nothing but this running.. > > > > Confirmed the faster disks by copying 5Gb files between two of the same > > type > > of disk (I installed two of them). 2xfaster than previous disks. > > > > my.cnf > > > > [mysqld] > > datadir=/var/lib/mysql > > socket=/var/lib/mysql/mysql.sock > > user=mysql > > # Default to using old password format for compatibility with mysql 3.x > > # clients (those using the mysqlclient10 compatibility package). > > old_passwords=1 > > key_buffer_size=3072M > > max_allowed_packet=16M > > thread_stack=128K > > thread_cache_size=64 > > thread_concurrency=8 > > sort_buffer_size=32M > > join_buffer_size=3M > > read_buffer_size=16M > > query_cache_size=64M > > query_cache_limit=8M > > table_cache=300 > > max_connections=500 > > max_heap_table_size=1024M > > tmp_table_size=1024M > > myisam_sort_buffer_size=128M > > wait_timeout=3000 > > > > set-variable=long_query_time=6 > > log-slow-queries=/var/log/mysql-slow-queries.log > > > > 8Gb Ram on this machine which is an intel quad core. > > > > Anything else I'm missing? It's *possible* a colleague had changed the > > my.cnf in the last few weeks and mysqld hadn't been restarted, but I > don't > > see anything obvious in there and he can't remember. > > > > :( > > > > > > > > On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > > > > > Is there any other job running while the update is happening. Because, > > > myisam does a table level lock. Please check the "show full > processlist". > > > > > > Also run mysqladmin -uroot -pxxx status. This would write lock > > information > > > into the machine.err log file. Check in this file also if there is any > > > locking happening. > > > > > > R u sure, this disk is a FASTER disk then the earlier one. > > > > > > > > > > > > > > > On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > >> > > >> Hi All, > > >> > > >> > > >> Given a fairly simple table as follows > > >> > > >> CREATE TABLE `common_userx2` ( > > >> `t_proj` char(6) default NULL, > > >> `t_id` int(11) NOT NULL default '0', > > >> `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL > > >> default '', > > >> `t_country` varchar(50) NOT NULL default '', > > >> `t_cpid` varchar(50) NOT NULL default '', > > >> `t_url` varchar(50) default NULL, > > >> `t_create_date` int(11) default NULL, > > >> `t_create_time` bigint(20) NOT NULL, > > >> `t_has_profile` char(1) NOT NULL, > > >> `t_team0` int(11) default NULL, > > >> `t_metric1` double(20,6) NOT NULL default '0.00', > > >> `t_metric2` double NOT NULL default '0', > > >> `t_metric3` double NOT NULL default '0', > > >> `t_metric4` double default NULL, > > >> `t_active` char(1) NOT NULL default '', > > >> `t_rev_metric1` double(20,6) NOT NULL default '100.00', > > >> `t_projrank0` int(11) default NULL, > > >> `t_rev_metric2` double(20,6) NOT NULL default '100.00', > > >> `t_racrank0` int(11) default NULL, > > >> `t_teamrank0` int(11) default NULL, > > >> `t_countryrank0` int(11) default NULL, > > >> `t_createdaterank0` int(11) default NULL, > > >> PRIMARY KEY (`t_id`), > > >> KEY `prank` (`t_rev_metric1`,`t_id`), > > >> KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), > > >> KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), > > >> KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), > > >> KEY `racrank` (`t_rev_metric2`,`t_id`) > > >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > >> > > >> I have a ranking update statement as follows > > >> > > >> set @rank = 0; > > >> update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by > > >> t_rev_metric1,t_id; > > >> > > >> For the largest case this has close to 1M rows. > > >> > > >> For weeks it was taking around 10seconds to do this. Yesterday I > > replaced > > >> the main data drive in the machine with a faster SATA Raptor drive. No > > >> problems occurred, but since then (and the subsequent reboot of the > > >> machine) > > >> this particular query is taking 45 minutes!
Re: Major Performance Degradation after replacing Hard Drive
when you run this update, what is the IO WAIT from the top command. regards anandkl On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > Nothing else running and no queries go against that table, it's effectively > created just for this, so I would expect the table lock. > > Show (full) processlist has nothing but this running.. > > Confirmed the faster disks by copying 5Gb files between two of the same > type > of disk (I installed two of them). 2xfaster than previous disks. > > my.cnf > > [mysqld] > datadir=/var/lib/mysql > socket=/var/lib/mysql/mysql.sock > user=mysql > # Default to using old password format for compatibility with mysql 3.x > # clients (those using the mysqlclient10 compatibility package). > old_passwords=1 > key_buffer_size=3072M > max_allowed_packet=16M > thread_stack=128K > thread_cache_size=64 > thread_concurrency=8 > sort_buffer_size=32M > join_buffer_size=3M > read_buffer_size=16M > query_cache_size=64M > query_cache_limit=8M > table_cache=300 > max_connections=500 > max_heap_table_size=1024M > tmp_table_size=1024M > myisam_sort_buffer_size=128M > wait_timeout=3000 > > set-variable=long_query_time=6 > log-slow-queries=/var/log/mysql-slow-queries.log > > 8Gb Ram on this machine which is an intel quad core. > > Anything else I'm missing? It's *possible* a colleague had changed the > my.cnf in the last few weeks and mysqld hadn't been restarted, but I don't > see anything obvious in there and he can't remember. > > :( > > > > On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > > > Is there any other job running while the update is happening. Because, > > myisam does a table level lock. Please check the "show full processlist". > > > > Also run mysqladmin -uroot -pxxx status. This would write lock > information > > into the machine.err log file. Check in this file also if there is any > > locking happening. > > > > R u sure, this disk is a FASTER disk then the earlier one. > > > > > > > > > > On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > >> > >> Hi All, > >> > >> > >> Given a fairly simple table as follows > >> > >> CREATE TABLE `common_userx2` ( > >> `t_proj` char(6) default NULL, > >> `t_id` int(11) NOT NULL default '0', > >> `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL > >> default '', > >> `t_country` varchar(50) NOT NULL default '', > >> `t_cpid` varchar(50) NOT NULL default '', > >> `t_url` varchar(50) default NULL, > >> `t_create_date` int(11) default NULL, > >> `t_create_time` bigint(20) NOT NULL, > >> `t_has_profile` char(1) NOT NULL, > >> `t_team0` int(11) default NULL, > >> `t_metric1` double(20,6) NOT NULL default '0.00', > >> `t_metric2` double NOT NULL default '0', > >> `t_metric3` double NOT NULL default '0', > >> `t_metric4` double default NULL, > >> `t_active` char(1) NOT NULL default '', > >> `t_rev_metric1` double(20,6) NOT NULL default '100.00', > >> `t_projrank0` int(11) default NULL, > >> `t_rev_metric2` double(20,6) NOT NULL default '100.00', > >> `t_racrank0` int(11) default NULL, > >> `t_teamrank0` int(11) default NULL, > >> `t_countryrank0` int(11) default NULL, > >> `t_createdaterank0` int(11) default NULL, > >> PRIMARY KEY (`t_id`), > >> KEY `prank` (`t_rev_metric1`,`t_id`), > >> KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), > >> KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), > >> KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), > >> KEY `racrank` (`t_rev_metric2`,`t_id`) > >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > >> > >> I have a ranking update statement as follows > >> > >> set @rank = 0; > >> update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by > >> t_rev_metric1,t_id; > >> > >> For the largest case this has close to 1M rows. > >> > >> For weeks it was taking around 10seconds to do this. Yesterday I > replaced > >> the main data drive in the machine with a faster SATA Raptor drive. No > >> problems occurred, but since then (and the subsequent reboot of the > >> machine) > >> this particular query is taking 45 minutes! > >> > >> I can't, for the life of me figure out why performance would be degraded > >> so > >> much. At first I thought perhaps it might be just disk/mysql caching but > >> the > >> performance has not increased any in subsequent runs. > >> > >> Any advice on where to look ? > >> > >> Phil > >> > >> -- > >> Help build our city at http://free-dc.myminicity.com ! > >> > > > > > > > -- > Help build our city at http://free-dc.myminicity.com ! >
Re: Major Performance Degradation after replacing Hard Drive
Nothing else running and no queries go against that table, it's effectively created just for this, so I would expect the table lock. Show (full) processlist has nothing but this running.. Confirmed the faster disks by copying 5Gb files between two of the same type of disk (I installed two of them). 2xfaster than previous disks. my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer_size=3072M max_allowed_packet=16M thread_stack=128K thread_cache_size=64 thread_concurrency=8 sort_buffer_size=32M join_buffer_size=3M read_buffer_size=16M query_cache_size=64M query_cache_limit=8M table_cache=300 max_connections=500 max_heap_table_size=1024M tmp_table_size=1024M myisam_sort_buffer_size=128M wait_timeout=3000 set-variable=long_query_time=6 log-slow-queries=/var/log/mysql-slow-queries.log 8Gb Ram on this machine which is an intel quad core. Anything else I'm missing? It's *possible* a colleague had changed the my.cnf in the last few weeks and mysqld hadn't been restarted, but I don't see anything obvious in there and he can't remember. :( On Mon, Jul 21, 2008 at 7:08 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > Is there any other job running while the update is happening. Because, > myisam does a table level lock. Please check the "show full processlist". > > Also run mysqladmin -uroot -pxxx status. This would write lock information > into the machine.err log file. Check in this file also if there is any > locking happening. > > R u sure, this disk is a FASTER disk then the earlier one. > > > > > On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: >> >> Hi All, >> >> >> Given a fairly simple table as follows >> >> CREATE TABLE `common_userx2` ( >> `t_proj` char(6) default NULL, >> `t_id` int(11) NOT NULL default '0', >> `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL >> default '', >> `t_country` varchar(50) NOT NULL default '', >> `t_cpid` varchar(50) NOT NULL default '', >> `t_url` varchar(50) default NULL, >> `t_create_date` int(11) default NULL, >> `t_create_time` bigint(20) NOT NULL, >> `t_has_profile` char(1) NOT NULL, >> `t_team0` int(11) default NULL, >> `t_metric1` double(20,6) NOT NULL default '0.00', >> `t_metric2` double NOT NULL default '0', >> `t_metric3` double NOT NULL default '0', >> `t_metric4` double default NULL, >> `t_active` char(1) NOT NULL default '', >> `t_rev_metric1` double(20,6) NOT NULL default '100.00', >> `t_projrank0` int(11) default NULL, >> `t_rev_metric2` double(20,6) NOT NULL default '100.00', >> `t_racrank0` int(11) default NULL, >> `t_teamrank0` int(11) default NULL, >> `t_countryrank0` int(11) default NULL, >> `t_createdaterank0` int(11) default NULL, >> PRIMARY KEY (`t_id`), >> KEY `prank` (`t_rev_metric1`,`t_id`), >> KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), >> KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), >> KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), >> KEY `racrank` (`t_rev_metric2`,`t_id`) >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 >> >> I have a ranking update statement as follows >> >> set @rank = 0; >> update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by >> t_rev_metric1,t_id; >> >> For the largest case this has close to 1M rows. >> >> For weeks it was taking around 10seconds to do this. Yesterday I replaced >> the main data drive in the machine with a faster SATA Raptor drive. No >> problems occurred, but since then (and the subsequent reboot of the >> machine) >> this particular query is taking 45 minutes! >> >> I can't, for the life of me figure out why performance would be degraded >> so >> much. At first I thought perhaps it might be just disk/mysql caching but >> the >> performance has not increased any in subsequent runs. >> >> Any advice on where to look ? >> >> Phil >> >> -- >> Help build our city at http://free-dc.myminicity.com ! >> > > -- Help build our city at http://free-dc.myminicity.com !
Re: Major Performance Degradation after replacing Hard Drive
Is there any other job running while the update is happening. Because, myisam does a table level lock. Please check the "show full processlist". Also run mysqladmin -uroot -pxxx status. This would write lock information into the machine.err log file. Check in this file also if there is any locking happening. R u sure, this disk is a FASTER disk then the earlier one. On 7/21/08, Phil <[EMAIL PROTECTED]> wrote: > > Hi All, > > > Given a fairly simple table as follows > > CREATE TABLE `common_userx2` ( > `t_proj` char(6) default NULL, > `t_id` int(11) NOT NULL default '0', > `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL > default '', > `t_country` varchar(50) NOT NULL default '', > `t_cpid` varchar(50) NOT NULL default '', > `t_url` varchar(50) default NULL, > `t_create_date` int(11) default NULL, > `t_create_time` bigint(20) NOT NULL, > `t_has_profile` char(1) NOT NULL, > `t_team0` int(11) default NULL, > `t_metric1` double(20,6) NOT NULL default '0.00', > `t_metric2` double NOT NULL default '0', > `t_metric3` double NOT NULL default '0', > `t_metric4` double default NULL, > `t_active` char(1) NOT NULL default '', > `t_rev_metric1` double(20,6) NOT NULL default '100.00', > `t_projrank0` int(11) default NULL, > `t_rev_metric2` double(20,6) NOT NULL default '100.00', > `t_racrank0` int(11) default NULL, > `t_teamrank0` int(11) default NULL, > `t_countryrank0` int(11) default NULL, > `t_createdaterank0` int(11) default NULL, > PRIMARY KEY (`t_id`), > KEY `prank` (`t_rev_metric1`,`t_id`), > KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), > KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), > KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), > KEY `racrank` (`t_rev_metric2`,`t_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > I have a ranking update statement as follows > > set @rank = 0; > update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by > t_rev_metric1,t_id; > > For the largest case this has close to 1M rows. > > For weeks it was taking around 10seconds to do this. Yesterday I replaced > the main data drive in the machine with a faster SATA Raptor drive. No > problems occurred, but since then (and the subsequent reboot of the > machine) > this particular query is taking 45 minutes! > > I can't, for the life of me figure out why performance would be degraded so > much. At first I thought perhaps it might be just disk/mysql caching but > the > performance has not increased any in subsequent runs. > > Any advice on where to look ? > > Phil > > -- > Help build our city at http://free-dc.myminicity.com ! >
Major Performance Degradation after replacing Hard Drive
Hi All, Given a fairly simple table as follows CREATE TABLE `common_userx2` ( `t_proj` char(6) default NULL, `t_id` int(11) NOT NULL default '0', `t_nick` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `t_country` varchar(50) NOT NULL default '', `t_cpid` varchar(50) NOT NULL default '', `t_url` varchar(50) default NULL, `t_create_date` int(11) default NULL, `t_create_time` bigint(20) NOT NULL, `t_has_profile` char(1) NOT NULL, `t_team0` int(11) default NULL, `t_metric1` double(20,6) NOT NULL default '0.00', `t_metric2` double NOT NULL default '0', `t_metric3` double NOT NULL default '0', `t_metric4` double default NULL, `t_active` char(1) NOT NULL default '', `t_rev_metric1` double(20,6) NOT NULL default '100.00', `t_projrank0` int(11) default NULL, `t_rev_metric2` double(20,6) NOT NULL default '100.00', `t_racrank0` int(11) default NULL, `t_teamrank0` int(11) default NULL, `t_countryrank0` int(11) default NULL, `t_createdaterank0` int(11) default NULL, PRIMARY KEY (`t_id`), KEY `prank` (`t_rev_metric1`,`t_id`), KEY `trank` (`t_team0`,`t_rev_metric1`,`t_id`), KEY `countryrank` (`t_country`,`t_rev_metric1`,`t_id`), KEY `createdaterank` (`t_create_date`,`t_rev_metric1`,`t_id`), KEY `racrank` (`t_rev_metric2`,`t_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a ranking update statement as follows set @rank = 0; update common_userx2 set projrank0 = @rank:[EMAIL PROTECTED] order by t_rev_metric1,t_id; For the largest case this has close to 1M rows. For weeks it was taking around 10seconds to do this. Yesterday I replaced the main data drive in the machine with a faster SATA Raptor drive. No problems occurred, but since then (and the subsequent reboot of the machine) this particular query is taking 45 minutes! I can't, for the life of me figure out why performance would be degraded so much. At first I thought perhaps it might be just disk/mysql caching but the performance has not increased any in subsequent runs. Any advice on where to look ? Phil -- Help build our city at http://free-dc.myminicity.com !
