Re: mysql RAID
--- On Wed, 10/3/10, John G. Heim jh...@math.wisc.edu wrote: Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. As dan already stated, the write penalty of raid 5 doesn't really make it a good fit for databases, go with raid 10. Like you for reasons beyond my control I've been stuck with dell hardware, just beware of dell raid controllers. The perc6 isn't too bad, however I still swap them out for more capable controllers. Pretty much everything before the perc6 is complete junk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to modify the application to implement the separation of write/read
Hi all, I want to test the mode of Master/Slave for mysql. Also I want to separate the write and read of sql requests, that is, I want to make Slave server of mysql to handle read requests and make Master server to handle write requests. I want to know how to modify the code of my application. Some people suggest me to use mysql-proxy to access mysql server. I checked the version of mysql-proxy, I found the latest version is just 0.8.0. Does someone have met this problem? How to implement the separation of read and write? Please give me some advice, thanks, Peter
Re: How to modify the application to implement the separation of write/read
2010/3/11 Peter Chen peter.c...@aicent.com: [...] Does someone have met this problem? How to implement the separation of read and write? please, write us some more details about your application! For example, if you use java with the Connector/j jdbc driver, you can enjoy a really nice feature already implemented in the driver: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to modify the application to implement the separation of write/read
Hi Mattia, I use java with the Connector/L jdbc driver, I checked the webpage of http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html, it suggests to use ReplicationDriver to replace common driver. I use Hibernate to access Mysql, the connection poll is c3p0-0.9.0, can I just need to modify the configuration of JDBC URL, like jdbc:mysql:replication://master,slave1,slave2,slave3/test to implement the separation of write/read? After configuring JDBC URL like this, can I make the query SQL requests to access the Slave server, the update/insert/delete SQL requests to access the Master server, and if the Master server is shotdown, the driver will failover to the Slave server? I am not sure whether do I need to modify something else? Like my application code. Expect your reply. Thanks, Peter -Original Message- From: Mattia Merzi [mailto:mattia.me...@gmail.com] Sent: 2010年3月11日 19:01 To: Peter Chen Cc: mysql@lists.mysql.com Subject: Re: How to modify the application to implement the separation of write/read 2010/3/11 Peter Chen peter.c...@aicent.com: [...] Does someone have met this problem? How to implement the separation of read and write? please, write us some more details about your application! For example, if you use java with the Connector/j jdbc driver, you can enjoy a really nice feature already implemented in the driver: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-replication-connection.html Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to modify the application to implement the separation of write/read
2010/3/11 Peter Chen peter.c...@aicent.com: [...] I am not sure whether do I need to modify something else? Like my application code. as stated on the webpage you just posted here: --- An application signals that it wants a transaction to be read-only by calling Connection.setReadOnly(true), this replication-aware connection will use one of the slave connections, which are load-balanced per-vm using a round-robin scheme [...] --- So, I think you just need to set the readonly flag to true on the connection objects via the setReadOnly() method. I can't tell you how to handle this using the hibernate stuffs, you should probably: - post this question on an hibernate-related mailing list - post this question on the Connector/J mailing list (there should be one, I think! :) - wait until someone else answers to this mail - try it by yourself (my suggestion). Finally, if you look at the user comments on the very same page, you will see an entry explicitly dedicated to Hibernate, suggesting a way to configure the persistence.xml file. Regarding the automatic failover, I suggest you to investigate the autoReconnect and failOverReadOnly flags of the Connector/J: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html Greetings, and have a nice coding! Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql proxy in production?
Hiya I work for a pretty large hosting company, and we have some clients that you could call in demand clients (Well here where I live anyway :) ). We already making use of heartbeat for high availability etc. But the one area that we have not tackled is load balancing. I just read the following, which makes use of mysql proxy. http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html I would like to ask, does anyone make use of mysqlproxy in production, and if so, are you using it under heavy load. How do you find how it performance under load. If anyone can share their failures, successors or even just thoughts and opinions on mysql-proxy (even SQL load balancing in general), I would be most grateful. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Particular value or NULL
My query isn't slow enough to tell the difference, I'm only working with a few hundred records. This was intellectual curiosity. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Wednesday, March 10, 2010 2:10 PM To: mysql@lists.mysql.com Subject: Re: Particular value or NULL Did you gave a try on using coalesce in your query? Jerry Schwartz jschwa...@the-infoshop.com escreveu na mensagem news:02a201cac083$e07a2330$a16e69...@com... -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Wednesday, March 10, 2010 10:55 AM To: mysql@lists.mysql.com Subject: Re: Particular value or NULL Maybe: WHERE coalesce(x, 17) = 17 [JS] Interesting suggestion, but us-gii select benchmark(1000,(7=7 or null is null)); +---+ | benchmark(1000,(7=7 or null is null)) | +---+ | 0 | +---+ 1 row in set (0.34 sec) us-gii select benchmark(1,coalesce(null,7)); +---+ | benchmark(1,coalesce(null,7)) | +---+ | 0 | +---+ 1 row in set (2.61 sec) It looks like COALESCE() is slower. Of course this isn't anything like a real test. Among other things, I have no idea how well or poorly the optimizer, query cache, etc. handle it. I don't know how much magic there is in the BENCHMARK() function, either. I would hope that the server would know that the expression needs to be evaluated over and over again from scratch. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Jerry Schwartz jschwa...@the-infoshop.com escreveu na mensagem news:023301cac069$366afa00$a340ee...@com... Is there a better construct for the WHERE clause in a LEFT JOIN than WHERE (x = 17 OR x IS NULL) ? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql proxy in production?
Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Thanks, Krishna On Thu, Mar 11, 2010 at 7:56 PM, Brent Clark brentgclarkl...@gmail.comwrote: Hiya I work for a pretty large hosting company, and we have some clients that you could call in demand clients (Well here where I live anyway :) ). We already making use of heartbeat for high availability etc. But the one area that we have not tackled is load balancing. I just read the following, which makes use of mysql proxy. http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html I would like to ask, does anyone make use of mysqlproxy in production, and if so, are you using it under heavy load. How do you find how it performance under load. If anyone can share their failures, successors or even just thoughts and opinions on mysql-proxy (even SQL load balancing in general), I would be most grateful. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Very slow delete for Master / Child tables with millions of rows
I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than X days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `UpdateGUID` varchar(36) NOT NULL default '', `SusClientId` varchar(36) NOT NULL default '', `UpdateState` varchar(256) NOT NULL default '', `LastTimeChanged` datetime default NULL, PRIMARY KEY (`ID`), KEY `IX_UpdateState` (`UpdateState`), KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`), KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`), KEY `FK_tblwsusclientinfo_2` (`SusClientId`), CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusevents` ( `EventGUID` varchar(36) NOT NULL default '', `EventCreationDate` datetime NOT NULL default '-00-00 00:00:00', `EventMessage` text NOT NULL, `EventId` varchar(256) NOT NULL default '', `EventSource` varchar(45) NOT NULL default '', `EventErrorCode` varchar(45) default NULL, `EventIsError` tinyint(1) NOT NULL default '0', `EventAssociatedUpdate` varchar(36) default NULL, `EventAssociatedComputer` varchar(36) default NULL, `EventAssociatedWSUSServer` varchar(36) NOT NULL default '', PRIMARY KEY (`EventGUID`), KEY `IX_EventId` (`EventId`), KEY `IX_EventCreationDate` (`EventCreationDate`), KEY `FK_tblwsusevents_1` (`EventAssociatedComputer`), CONSTRAINT `FK_tblwsusevents_1` FOREIGN KEY (`EventAssociatedComputer`) REFERENCES `tblwsusclients`
Re: mysql proxy in production?
Bah, just spilled coffee on my keyboard. I never was a big fan of mysqlproxy, but this is almost inconceivable. The next genius to suggest it for our environment had better have asbestos underwear. Thanks for the link, Krishna. On Thu, Mar 11, 2010 at 3:52 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Thanks, Krishna On Thu, Mar 11, 2010 at 7:56 PM, Brent Clark brentgclarkl...@gmail.com wrote: Hiya I work for a pretty large hosting company, and we have some clients that you could call in demand clients (Well here where I live anyway :) ). We already making use of heartbeat for high availability etc. But the one area that we have not tackled is load balancing. I just read the following, which makes use of mysql proxy. http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html I would like to ask, does anyone make use of mysqlproxy in production, and if so, are you using it under heavy load. How do you find how it performance under load. If anyone can share their failures, successors or even just thoughts and opinions on mysql-proxy (even SQL load balancing in general), I would be most grateful. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Very slow delete for Master / Child tables with millions of rows
If you really have to loop through the entire set deleting record by record, I'm not surprised it's slow. Could you change your application to loop through the records doing stuff without deleting (maybe even do stuff en masse), and afterwards do a mass delete ? I also have a nagging suspicion (unfounded by any actual experience, though) that it might be faster to cut the explicit relations and do the child deletes as a separate single-block execute, too. Easy enough to test, I suppose. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: mysql proxy in production?
On 11/03/2010 16:52, Krishna Chandra Prajapati wrote: Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Well thats disappointing. sigh So what are we supposed to use for loadbalancing mysql. Thank you for your reply. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql proxy in production?
On Mar 11, 2010, at 9:49 AM, Brent Clark wrote: On 11/03/2010 16:52, Krishna Chandra Prajapati wrote: Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Well thats disappointing. sigh So what are we supposed to use for loadbalancing mysql. Thank you for your reply. Kind Regards Brent Clark Brent, Some clients (namely the JDBC driver, and the R-O-R adapter) have load balancing built in. -Mark -- Mark Matthews Principal Software Developer - Enterprise Tools Oracle http://www.mysql.com/products/enterprise/monitor.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Very slow delete for Master / Child tables with millions of rows
Hi Randall, How much memory is allocated to innodb_buffer_pool_size. Please send your mysql configuration file (my.cnf) Thanks, Krishna On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall randall.pr...@vt.eduwrote: I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than X days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `UpdateGUID` varchar(36) NOT NULL default '', `SusClientId` varchar(36) NOT NULL default '', `UpdateState` varchar(256) NOT NULL default '', `LastTimeChanged` datetime default NULL, PRIMARY KEY (`ID`), KEY `IX_UpdateState` (`UpdateState`), KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`), KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`), KEY `FK_tblwsusclientinfo_2` (`SusClientId`), CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES `tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusevents` ( `EventGUID` varchar(36) NOT NULL default '', `EventCreationDate` datetime NOT NULL default '-00-00 00:00:00', `EventMessage` text NOT NULL, `EventId` varchar(256) NOT NULL default '', `EventSource` varchar(45) NOT NULL default '', `EventErrorCode` varchar(45) default NULL, `EventIsError` tinyint(1) NOT NULL default '0', `EventAssociatedUpdate` varchar(36) default NULL, `EventAssociatedComputer` varchar(36) default NULL, `EventAssociatedWSUSServer` varchar(36) NOT NULL default '', PRIMARY KEY (`EventGUID`), KEY
Error Removing Anonymous Accounts
I installed MySQL 5 today and started by setting up root passwords for all three root accounts: mysql select User, Password, Host, Create_priv from user; +--+---+---+-+ | User | Password | Host | Create_priv | +--+---+---+-+ | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | localhost | Y | | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | ghost | Y | | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | 127.0.0.1 | Y | | | | localhost | N | | | | ghost | N | +--+---+---+-+ Now my question is when I follow this 'Anonymous Account Removal' guide: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html I get the following error: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' Any help is greatly appreciated! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Error Removing Anonymous Accounts
DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Carlos Mennens [mailto:carlosw...@gmail.com] Sent: Thursday, March 11, 2010 11:21 AM To: MySQL Subject: Error Removing Anonymous Accounts I installed MySQL 5 today and started by setting up root passwords for all three root accounts: mysql select User, Password, Host, Create_priv from user; +--+---+---+-+ | User | Password | Host | Create_priv | +--+---+---+-+ | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | localhost | Y | | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | ghost | Y | | root | *3B657EF5C969760D3CF4E42931FDCE507F26D2A1 | 127.0.0.1 | Y | | | | localhost | N | | | | ghost | N | +--+---+---+-+ Now my question is when I follow this 'Anonymous Account Removal' guide: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html I get the following error: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' Any help is greatly appreciated! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
Re: Error Removing Anonymous Accounts
On Thu, Mar 11, 2010 at 11:29 AM, Rolando Edwards redwa...@logicworks.net wrote: DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; That worked and I thank you however I am wondering why the MySQL guide was incorrect? Perhaps it's right but I did something wrong. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Very slow delete for Master / Child tables with millions of rows
Here is my.ini file - and I am using all Innodb [client] port=3306 [mysql] default-character-set=latin1 [mysqld] port=3306 skip-name-resolve basedir=W:/Applications/MySQL/MySQL Server 5.0/ datadir=W:/Applications/MySQL/MySQL Server 5.0/Data/ default-character-set=latin1 default-storage-engine=INNODB sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION max_connections=100 query_cache_size=50M table_cache=512M tmp_table_size=103M thread_cache_size=8 #*** MyISAM Specific options myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=205M key_buffer_size=512M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=2M #*** INNODB Specific options *** innodb_additional_mem_pool_size=7M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3498K innodb_buffer_pool_size=512M innodb_log_file_size=170M innodb_thread_concurrency=10 query_cache_type=1 long_query_time=2 log-slow-queries=Slow.log innodb_file_per_table innodb_lock_wait_timeout=500 From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, March 11, 2010 11:02 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Very slow delete for Master / Child tables with millions of rows Hi Randall, How much memory is allocated to innodb_buffer_pool_size. Please send your mysql configuration file (my.cnf) Thanks, Krishna On Thu, Mar 11, 2010 at 8:57 PM, Price, Randall randall.pr...@vt.edumailto:randall.pr...@vt.edu wrote: I am experiencing very slow deletes when I delete a record from a master table and have cascading deletes on two detail tables. I have an application that looks for records in the master table that are older than X days and delete them. The cascasing deletes then handles deleting all the child records in the other tables. However, this process is very slow. Depending on how many records are found to delete, this process takes anywhere from 30-40 minutes to several hours. Due to the nature of my application, I must loop through the records to delete, do some stuff for each record, then delete it. I suspect at this point, each tables' indexes need to be rebuilt. There are several indexes and the ones for the tables with 4,000,000+ rows probably takes a while. My question is: What is the best way to handle deleting master/detail records in this scenario? I have a brief diagram of my tables and the CREATE TABLE statements follow. Thanks, Randall Price +---+ | tblwsusclientinfo | +-++---+ | tblwsusclients || ID| +-+| UpdateGUID| | SusClientId |-oo| SusClientId | | ... | || ... | +-+ |+---+ ( ~ 3,000 rows) | (~ 4,000,000 rows ) | | |+-+ || tblwsusevents | |+-| || EventGUID | || ... | +--oo| EventAssociatedComputer | | ... | +-| (~ 4,300,000 rows ) CREATE TABLE `tblwsusclients` ( `SusClientId` varchar(36) NOT NULL default '', `DNSName` varchar(256) NOT NULL default '', `ServerGUID` varchar(36) NOT NULL default '', `IPAddress` varchar(15) NOT NULL default '', `LastReportTime` datetime NOT NULL default '-00-00 00:00:00', `LastSyncTime` datetime NOT NULL default '-00-00 00:00:00', `DetectionResult` varchar(256) default NULL, `ResponsiblePerson` varchar(16) default NULL, `TargetGroup` varchar(45) default NULL, `Affiliation` varchar(45) default NULL, `AddedDate` datetime default NULL, `IsActive` tinyint(1) default NULL, `UnRegisteredDate` datetime default NULL, `SCVersion` double default NULL, `BiosName` varchar(256) default NULL, `BiosVersion` varchar(45) default NULL, `OSVersion` varchar(45) default NULL, `SPVersion` varchar(45) default NULL, `Make` varchar(256) default NULL, `Model` varchar(256) default NULL, `ProcArchitecture` varchar(45) default NULL, `OSLongName` varchar(256) default NULL, `TimedOutDate` datetime default NULL, PRIMARY KEY (`SusClientId`), KEY `FK_tblwsusclients_1` (`ServerGUID`), KEY `IX_DNSName` (`DNSName`), KEY `IX_IsActive` (`IsActive`), CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES `tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblwsusclientinfo` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `UpdateGUID` varchar(36) NOT NULL default '', `SusClientId` varchar(36) NOT NULL default '', `UpdateState`
Re: mysql proxy in production?
Has MySQL Proxy been improved since that article was released? I ask because although I have no personal experience with it I do know of a big UK based online payment processing company who use MySQL Proxy with MySQL 5.1 in production very successfully. On Thu, Mar 11, 2010 at 3:34 PM, Johan De Meersman vegiv...@tuxera.bewrote: Bah, just spilled coffee on my keyboard. I never was a big fan of mysqlproxy, but this is almost inconceivable. The next genius to suggest it for our environment had better have asbestos underwear. Thanks for the link, Krishna. On Thu, Mar 11, 2010 at 3:52 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Brent You can visit the below link. http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ Thanks, Krishna On Thu, Mar 11, 2010 at 7:56 PM, Brent Clark brentgclarkl...@gmail.com wrote: Hiya I work for a pretty large hosting company, and we have some clients that you could call in demand clients (Well here where I live anyway :) ). We already making use of heartbeat for high availability etc. But the one area that we have not tackled is load balancing. I just read the following, which makes use of mysql proxy. http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html I would like to ask, does anyone make use of mysqlproxy in production, and if so, are you using it under heavy load. How do you find how it performance under load. If anyone can share their failures, successors or even just thoughts and opinions on mysql-proxy (even SQL load balancing in general), I would be most grateful. Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Particular value or NULL
Hi Jerry, all! Jerry Schwartz wrote: [[...]] Maybe: WHERE coalesce(x, 17) = 17 [JS] Interesting suggestion, but us-gii select benchmark(1000,(7=7 or null is null)); [[...]] 1 row in set (0.34 sec) us-gii select benchmark(1,coalesce(null,7)); [[...]] 1 row in set (2.61 sec) It looks like COALESCE() is slower. Of course this isn't anything like a real test. [[...]] Both your statements use constants only, so they can be evaluated during statement analysis and need not access any data. I don't think the time relation you get here can be applied to a statement really getting column values from a table and using them in a predicate. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Properly ALTER Column Data?
I know this is very basic for most on the list but I need some MySQL help. I am logged into my database and I successfully changed a 'field type' from INT to VARCHAR. Now I need to modify the actual data I inserted into those specific fields. I checked the manual and could not really gather exactly how to format my command. There was just so many options and information on the page... My table data is as follows and I would like to 'ALTER' the data in all three 'Serial' fields: mysql select Model, Serial, GFE, EOL from sun; +--++--++ | Model| Serial | GFE | EOL| +--++--++ | Ultra 24 | 941| 8402 | 2010-10-16 | | 7310 | 934|9314 | 2012-08-27 | | J4400| 926|7623 | 2012-08-27 | +--++--++ 3 rows in set (0.00 sec) Can someone please help me understand how I am to properly use and change the data in MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Error Removing Anonymous Accounts
DROP USER ''@'localhost'; If you use the correct GRANT/REVOKE and CREATE/DROP commands it's not necessary to update the tables and run FLUSH PRIVILEGES. IMO manipulating those tables directly is a bad habit. -Original Message- From: Carlos Mennens [mailto:carlosw...@gmail.com] Sent: Thursday, March 11, 2010 8:32 AM To: MySQL Subject: Re: Error Removing Anonymous Accounts On Thu, Mar 11, 2010 at 11:29 AM, Rolando Edwards redwa...@logicworks.net wrote: DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; That worked and I thank you however I am wondering why the MySQL guide was incorrect? Perhaps it's right but I did something wrong. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
RE: Properly ALTER Column Data?
Please read the tutorial, let us know if you have questions on the information in it: http://dev.mysql.com/doc/refman/5.0/en/tutorial.html -Original Message- From: Carlos Mennens [mailto:carlosw...@gmail.com] Sent: Thursday, March 11, 2010 12:44 PM To: MySQL Subject: Properly ALTER Column Data? I know this is very basic for most on the list but I need some MySQL help. I am logged into my database and I successfully changed a 'field type' from INT to VARCHAR. Now I need to modify the actual data I inserted into those specific fields. I checked the manual and could not really gather exactly how to format my command. There was just so many options and information on the page... My table data is as follows and I would like to 'ALTER' the data in all three 'Serial' fields: mysql select Model, Serial, GFE, EOL from sun; +--++--++ | Model| Serial | GFE | EOL| +--++--++ | Ultra 24 | 941| 8402 | 2010-10-16 | | 7310 | 934|9314 | 2012-08-27 | | J4400| 926|7623 | 2012-08-27 | +--++--++ 3 rows in set (0.00 sec) Can someone please help me understand how I am to properly use and change the data in MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
RE: Merging deltas from one table to another?
Not sure if this well help, but SQLYog (the best mysql GUI EVER) has an option that will take two databases and create a schema difference (ALTER statements, etc.) as well as I think data maybe? -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Wednesday, March 10, 2010 10:43 PM To: Don Read Cc: John Oliver; mysql@lists.mysql.com Subject: Re: Merging deltas from one table to another? I think mysqldump does not provide this option. You can extract the data into a file and use load data infile 'file_name replace into table_name or replace into table_name select * from old_table_name; For the above to work, there need to be primary key or unique index. If primary key or unique index is not present, it would just insert the data. Extrating data into a file will take lot of time if the data set is too huge. regards anandkl On Thu, Mar 11, 2010 at 3:32 AM, Don Read don_r...@att.net wrote: On Mon, 8 Mar 2010 14:14:09 -0800 John Oliver said: OK, a Drupal site I deal with has two copies... a production site and a test site. After new changes are developed, they're put on the test site. Once the test site is deemed to be OK, that entire site and database will be copied over to the production site. The issue is, while that work is taking place, changes are still happening on the production site, like new user registrations and some form input. What I'm thinking is, we can dump the affected tables and then import them into the test site, which started life as a mirror copy of the production site. If I were to: mysqldump -h db_server -h user -pPASSWORD database table_1 table_2 /tmp/db.sql Get the table layout on the production box: mysqldump --add-drop-table ... On the production side, and then: mysql -h test_db_server -h user -pPASSWORD database /tmp/db.sql ... -- Don Readdon_r...@att.net It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Properly ALTER Column Data?
On Thu, Mar 11, 2010 at 3:57 PM, Gavin Towey gto...@ffn.com wrote: Please read the tutorial, let us know if you have questions on the information in it: http://dev.mysql.com/doc/refman/5.0/en/tutorial.html Thank you very much for the link... I did: UPDATE sun SET serial = '0123456789' WHERE Model = 'Ultra 24'; Worked fine! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to modify the application to implement the separation of write/read
c3pO really? as in Starwars?? ;-) /me rolls eyes. -Original Message- From: Peter Chen [mailto:peter.c...@aicent.com] Sent: Thursday, March 11, 2010 3:25 AM To: Mattia Merzi Cc: mysql@lists.mysql.com I use Hibernate to access Mysql, the connection poll is c3p0-0.9.0, can I just need to modify the configuration of
RE: Very slow delete for Master / Child tables with millions of rows
This isn't surprising, especially if you have foreign keys or indexes, as each DELETE will cascade and require a rebuild of the indexes (just as an INSERT does). Make sure that for each DELETE you are using LIMIT 1; if it's in a loop (and you're not deleting via PK, but it's a good habit to get into and won't hurt if using a PK). A trick I've done, is create another column called delete_me or something, then you loop through and set a flag to 1 for all records you want deleted. Then at the end (or during the night via crontab or something) you simply: DELETE FROM foo WHERE delete_me = 1; (you may need to adjust other code to ignore any records that are set for deletion in SELECTs) Depending on your schema you might also be able to do something like this, if you want to manage the foreign key deletes yourself. SET FOREIGN_KEY_CHECKS=0; DELETE FROM foo WHERE delete_me = 1; DELETE FROM bar WHERE delete_me = 1; SET FOREIGN_KEY_CHECKS=1; I thought there was a way to turn of indexes as well in a similar way. http://dev.mysql.com/doc/refman/5.0/en/delete.html Also look at the LOW_PRIORITY if you're using MYISAM tables and QUICK. Try the trick of INSERTing into a NEW table and using RENAME instead of DELETE all together. Store the ID's you want to delete in another table (maybe even a HEAP/MEMORY one), then: DELETE FROM LargeTable USING LargeTable INNER JOIN TemporarySmallTable ON LargeTable.ID = TemporarySmallTable.ID; Another idea I just had that may work is to use a VIEW as your SELECT table (where 'delete_me 1') then all your code points at the VIEW, and you can delete from the real main table whenever you like, or just keep it for archival purposes. Consider OPTIMIZE TABLE to reclaim unused space and reduce file sizes when done too. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, March 11, 2010 7:43 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Very slow delete for Master / Child tables with millions of rows If you really have to loop through the entire set deleting record by record, I'm not surprised it's slow. Could you change your application to loop through the records doing stuff without deleting (maybe even do stuff en masse), and afterwards do a mass delete ? I also have a nagging suspicion (unfounded by any actual experience, though) that it might be faster to cut the explicit relations and do the child deletes as a separate single-block execute, too. Easy enough to test, I suppose. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Cygwin and DBD::mysql
it seem the Cygwin Perl can't see a client libraries needed to build
DBD::Mysql. Innovative lad that I am I figured I'd just compile my own
from 5.1.44 (current download). Nice thought.
What happens is the build fails as follows:
./configure --prefix=/usr/local/mysql --without-server
[ much configurage ]
make
[ much makage ]
vi.c: In function ‘get_alias_text’:
vi.c:918: error: expected declaration specifiers before ‘__weak_reference’
vi.c:923: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:953: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:998: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before
‘{’ token
vi.c:1054: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’
before ‘{’ token
vi.c:1103: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’
before ‘{’ token
vi.c:918: error: parameter name omitted
vi.c:1124: error: expected ‘{’ at end of input
make[2]: *** [vi.o] Error 1
make[2]: Leaving directory
`/usr/local/src/mysql-5.1.44/cmd-line-utils/libedit'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/local/src/mysql-5.1.44/cmd-line-utils'
make: *** [all-recursive] Error 1
Any ideas on how I can get the libraries and headers to build DBD::mysql?
Thanks in advance
Bruce Ferrell
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql proxy in production?
Take a look at haProxy. It can be combined with some scripts to loadbalance mysql. http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/ We use it at Open Query for a similar case. Cheers, Walter Heck Engineer @ Open Query http://openquery.com | http://openquery.com/blog | On Fri, Mar 12, 2010 at 02:09, John Daisley daisleyj...@googlemail.com wrote: Has MySQL Proxy been improved since that article was released? I ask because although I have no personal experience with it I do know of a big UK based online payment processing company who use MySQL Proxy with MySQL 5.1 in production very successfully. On Thu, Mar 11, 2010 at 3:34 PM, Johan De Meersman vegiv...@tuxera.bewrote: Bah, just spilled coffee on my keyboard. I never was a big fan of mysqlproxy, but this is almost inconceivable. The next genius to suggest it for our environment had better have asbestos underwear. Thanks for the link, Krishna. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
