[phpMyAdmin] please vote or suggest features
Hi, for all you people out there loving phpMyAdmin ;-) please visit http://hackontest.org and vote for or suggest your favorite feature you would like to see in phpMyAdmin and that can be implemented within 24 hours by a team of three Thank you very much! -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why people don't use engine named BDB?
Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
ANN: Advanced Data Generator 2.3.0 released!
Dear ladies and gentlemen, Upscene Productions is happy to announce the next version of their test data generator tool: Advanced Data Generator 2 (v2.3.0) A fast test-data generator tool that comes with a library of real-life data, can generate data to your database, SQL script or CSV files, many filling options, presets and much more. Feature highlights: * Ability to generate more flexible and real-life master/detail data * Ability to use (legacy) data from CSV files * Ability to generate proper multi-column referential links * New ways of generating data for BLOBs, CLOBs and more * Automatic detection of table order by using Foreign Key Constraints * Support for custom SQL statements after data generation * Generate data to your database, SQL scripts or CSV files This product comes in four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on: http://www.upscene.com/purchase.htm#adg More information available here: http://www.upscene.com/news/20080721.htm With regards, Martijn Tonies Upscene Productions - Database Tools for Developers http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 !
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 !
memory usage
Hi All, I have setup slave db. The machine configuration details of this slave is same as master. OS=redhat 8 cpu 16GB RAM key_buffer_size=3000M innodb_buffer_pool_size=1M. But when i do top, in the master db Cpu(s): 0.5%us, 0.3%sy, 0.0%ni, 87.2%id, 11.9%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16436956k total, 16350252k used,86704k free, 9188k buffers Swap: 16386292k total,37232k used, 16349060k free, 2358944k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 28706 mysql 15 0 14.3g 13g 4688 S6 84.1 540:21.55 mysqld On slave db Cpu(s): 0.7%us, 0.2%sy, 0.0%ni, 87.3%id, 11.8%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 16436956k total, 16351536k used,85420k free,16400k buffers Swap: 16386292k total, 164k used, 16386128k free, 4289520k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 14042 mysql 18 0 14.0g 11g 4652 S7 72.5 265:08.62 mysqld 435 root 10 -5 000 S0 0.0 3:29.07 kswapd0 As you can see the RES in master is 13g, but on slave its 11G any specific reason for this. This is causing some of the sql's on the slave to be slower than master for the same select statement on both master and slave. regards anandkl
Restore information
Hi All, Is there a simple way of checking when the backup db server performed its last restore and whether it was successful or not. I need to ensure that the dump and restore of the production box has run successfully every night until proper backup/DRP procedures are in place. I'm not familiar with this sort of thing and searching has provided little assistance. Thanks Warren -- Open Source Developer Business Data Solutions Email: [EMAIL PROTECTED] Gmail: wwindvogel MSN: wwindvogel Skype: wwindvogel Cell: 27 73 264 6700 Tel: 27 21 487 2177 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore information
Is my assumption correct that you dump your main production db and restore it to a second server? And this restore is what you want to verify? Olaf On 7/21/08 8:34 AM, Warren Windvogel [EMAIL PROTECTED] wrote: Hi All, Is there a simple way of checking when the backup db server performed its last restore and whether it was successful or not. I need to ensure that the dump and restore of the production box has run successfully every night until proper backup/DRP procedures are in place. I'm not familiar with this sort of thing and searching has provided little assistance. Thanks Warren -- Open Source Developer Business Data Solutions Email: [EMAIL PROTECTED] Gmail: wwindvogel MSN: wwindvogel Skype: wwindvogel Cell: 27 73 264 6700 Tel: 27 21 487 2177 - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore information
Hi Warren, R u using any tool/script for backup/restore procedure, if yes, then log files generated by them should let u know, if they were successfull or failed. regards anandkl On 7/21/08, Warren Windvogel [EMAIL PROTECTED] wrote: Hi All, Is there a simple way of checking when the backup db server performed its last restore and whether it was successful or not. I need to ensure that the dump and restore of the production box has run successfully every night until proper backup/DRP procedures are in place. I'm not familiar with this sort of thing and searching has provided little assistance. Thanks Warren -- Open Source Developer Business Data Solutions Email: [EMAIL PROTECTED] Gmail: wwindvogel MSN: wwindvogel Skype: wwindvogel Cell: 27 73 264 6700 Tel: 27 21 487 2177 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore information
Olaf Stein wrote: Is my assumption correct that you dump your main production db and restore it to a second server? And this restore is what you want to verify? That is correct. Warren -- 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
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: Restore information
If you do the dump/restore by hand or shell script than the first indicator is if the process finishes without errors. Then you could calculate checksums of your tables on both boxes and compare them, this will obviously only work if the production db has not changed since the dump. Are you doing this by hand or are you suing any tools provided by mysql or a third party Olaf On 7/21/08 8:41 AM, Warren Windvogel [EMAIL PROTECTED] wrote: Olaf Stein wrote: Is my assumption correct that you dump your main production db and restore it to a second server? And this restore is what you want to verify? That is correct. Warren - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why people don't use engine named BDB?
On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway. -- --David.
Re: Why people don't use engine named BDB?
Its mainly because it was purchased by Oracle. BDB provided transaction support. Innodb has been the defacto choice for a ACID transactions, but Innodb was also purchased by Oracle in its attempt to kill MySQL after its failed attempt to purchase MySQL. That's why MySQL has been working on their own storage engine as well as the pluggable storage system. Curtis David Giragosian wrote: On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway.
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: Why people don't use engine named BDB?
No, its mainly because BDB wasn't very good. Its transactional, but not MVCC. Take a look at a contemporary article when the acquisition was made : http://www.computerworld.com/databasetopics/data/software/story/0,10801,108705,00.html Curtis Maurand wrote: Its mainly because it was purchased by Oracle. BDB provided transaction support. Innodb has been the defacto choice for a ACID transactions, but Innodb was also purchased by Oracle in its attempt to kill MySQL after its failed attempt to purchase MySQL. That's why MySQL has been working on their own storage engine as well as the pluggable storage system. Curtis David Giragosian wrote: On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway. -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850/ Tel (888) 829-3631/ Fax -- 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
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! 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
Re: Insert ... select ... On Duplicate Update Question
At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. Write code to do it. There is no way around specifying the columns. - Perrin -- 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:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server statistics
On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html As far as I have understood that, there is only one log that can be turned on or off. When turned on, it logs every single query sent to the server. I hope it also logs some other data, like the username and maybe the client address (local/remote). But this has one major drawback: To enable or disable the log (which, I can imagine, takes quite a bit performance) the whole server must be stopped and restarted. This isn't really an option for a production database server, just to know what's going on from time to time. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- 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
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: Insert ... select ... On Duplicate Update Question
At 11:00 AM 7/21/2008, Perrin Harkins wrote: On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. Write code to do it. There is no way around specifying the columns. - Perrin Perrin, Ok thanks. I'll do that. 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
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 ? 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:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
So just use REPLACE instead of INSERT... http://dev.mysql.com/doc/refman/5.0/en/replace.html On Mon, Jul 21, 2008 at 11:44 AM, mos [EMAIL PROTECTED] wrote: At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos [EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Help build our city at http://free-dc.myminicity.com !
Re: Insert ... select ... On Duplicate Update Question
At 12:16 PM 7/21/2008, you wrote: So just use REPLACE instead of INSERT... Sure, but a Replace will delete the existing row and insert the new one which means also maintaining the indexes. This will take much longer than just updating the existing row. Now if there were only a couple of rows then a Replace will work fine (but it would also execute Delete/Insert triggers if I had any). But I have 50 million rows and will need to update maybe 1/2% of those, all of those deletes and inserts will slow things down. Now logically I thought this should work: insert into Table2 select * from table1 on duplicate key update; I thought if MySQL found a duplicate key on the insert, it would automatically update the existing row that it found with the results from table1 if I left out the column expressions in the update clause. But apparently it doesn't work that way. It looks like I have to re-specify each of the column names in Table1 in the Update clause as a column assignment. I thought this was totally necessary because MySQL knew the column assignments for the original insert, why couldn't it pick up where it left off and use the existing row (specified by the duplicate key value it found). Either that or just force me to specify the key value assignment in the Update clause and not the whole column list which could be 100 columns. Mike http://dev.mysql.com/doc/refman/5.0/en/replace.htmlhttp://dev.mysql.com/doc/refman/5.0/en/replace.html On Mon, Jul 21, 2008 at 11:44 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: At 08:23 PM 7/20/2008, Perrin Harkins wrote: On Sun, Jul 20, 2008 at 12:12 AM, mos mailto:[EMAIL PROTECTED][EMAIL PROTECTED] wrote: Is there a way to get Insert ... select ... On Duplicate Update to update the row with the duplicate key? That's what it does. Why can't it do this? What makes you think it can't? - Perrin Perrin, I can't specify all of the columns in a Set statement in the OnDuplicate clause because I don't know what the column names are and there could be 100 columns. I'd like to use something like: insert into Table2 select * from table1 on duplicate key update; but this gives me a syntax error. Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 So it is looking for an Update expression. I'd like it to update all the columns in the Select statement to the row with the matching key. After all, this is what the statement was trying to do in the first place. I don't see why I have to explicitly specify all of the value assignments in the On Duplicate phrase over again. Mike MySQL 5.0.24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert ... select ... On Duplicate Update Question
On Mon, Jul 21, 2008 at 2:43 PM, mos [EMAIL PROTECTED] wrote: I thought if MySQL found a duplicate key on the insert, it would automatically update the existing row that it found with the results from table1 if I left out the column expressions in the update clause. But apparently it doesn't work that way. It probably could, but it doesn't. With the availability of the information schema, it's easy to look up the columns in a table, so doing this from a program is relatively simple. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spaces in sourced file names?
In the CLI, is there any way to source a file whose name has whitespace in it (particularly spaces)? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Re: Spaces in sourced file names?
Ls -la file\ name.file \ Will escape the space for ya me believes --Original Message-- From: Jerry Schwartz To: mysql@lists.mysql.com Sent: Jul 21, 2008 13:48 Subject: Spaces in sourced file names? In the CLI, is there any way to source a file whose name has whitespace in it (particularly spaces)? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com Sent via BlackBerry from T-Mobile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL server statistics
With 5.1 you have more control over general query log and the slow query log - enable or disable at runtime, output to file or DB table. As of MySQL 5.1.6, the server can write general query and slow query entries to log tables, log files, or both. For details, see Section 5.2.1, Selecting General Query and Slow Query Log Output Destinations. As of MySQL 5.1.12, additional runtime control of the general query and slow query logs is available: You can enable or disable logging, or change the name of the log file. See Section 5.2.3, The General Query Log, and Section 5.2.5, The Slow Query Log. See http://dev.mysql.com/doc/refman/5.1/en/log-files.html If you can either use non-GA or wait until 5.1 is GA. Or - looking at your original question it may be able to narrow down the source of the queries if you can graph your data more often - maybe every 10-15 seconds instead of a longer interval to help you profile which applications are hammering your db server. Ben Wiechman -Original Message- From: Yves Goergen [mailto:[EMAIL PROTECTED] Sent: Monday, July 21, 2008 11:19 AM To: Rob Wultsch Cc: mysql@lists.mysql.com Subject: Re: MySQL server statistics On 20.07.2008 23:49 CE(S)T, Rob Wultsch wrote: On Sun, Jul 20, 2008 at 1:33 PM, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've installed MySQL server 5.0 and have written a small statistics script that regularly checks the number of connections and queries to the server, which I can then view in a diagram. But sometimes it just says that at a time, unusually many connections or queries have been made to the server. I cannot see what causes them. Neither the user nor the actual queries. At work I got in touch with the Oracle Enterprise Manager recently. I haven't looked at it too closely yet, but I think it could give useful information about each session, what it does and more importantly what it did. I have no idea what to search for to get this information from the MySQL server. So I had to ask here first. Is there any method to get those statistics? I don't mean the SHOW PROCESSES list, it only contains a snapshot of the very moment when MySQL got to execute my command. I mean information about recent activity, like 15 minutes, 2 hours or so. Check out the general query log: http://dev.mysql.com/doc/refman/5.0/en/query-log.html As far as I have understood that, there is only one log that can be turned on or off. When turned on, it logs every single query sent to the server. I hope it also logs some other data, like the username and maybe the client address (local/remote). But this has one major drawback: To enable or disable the log (which, I can imagine, takes quite a bit performance) the whole server must be stopped and restarted. This isn't really an option for a production database server, just to know what's going on from time to time. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for user 'debian-sys-maint'@'localhost'
OK. This is driving me Nutz 8-p Any time I try to restart mysql, I get the error, Access denied for user 'debian-sys-maint'@'localhost' My understanding is that the password for the debian-sys-maint user is found in /etc/mysql/debian.cnf So, I edit that, and note the password. I then execute the following in MySQL (with the correct password, of course): GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'LongPasswordHere' WITH GRANT OPTION To test it out, I try a mysql -u debian-sys-maint -p, type in the password and get the Access denied error again. What's going on? Why can't I get this to work? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user 'debian-sys-maint'@'localhost'
Do you login via sock or network ? Didja change the passwd for localhost and % ? --Original Message-- From: Jesse To: MySQL List Sent: Jul 21, 2008 17:35 Subject: Access denied for user 'debian-sys-maint'@'localhost' OK. This is driving me Nutz 8-p Any time I try to restart mysql, I get the error, Access denied for user 'debian-sys-maint'@'localhost' My understanding is that the password for the debian-sys-maint user is found in /etc/mysql/debian.cnf So, I edit that, and note the password. I then execute the following in MySQL (with the correct password, of course): GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'LongPasswordHere' WITH GRANT OPTION To test it out, I try a mysql -u debian-sys-maint -p, type in the password and get the Access denied error again. What's going on? Why can't I get this to work? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Sent via BlackBerry from T-Mobile
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=statsrunproj=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`), 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
