Re: Replication - LINUX to WIN
Hi Vikram, There are a couple of potential problems with Linux to Windows replication, but they are easily avoidable. 1) Table names: There are case-sensitivity differences between Linux and Windows: Linux is case-sensitive as regards table names, whereas Windows is not. Given that you are replicating from a Linux master to a Windows slave, you shouldn't hit too many problems with this, but if you ever reverse the order for some reason there could be problems. 2) MySQL versions: try to make sure that you always use the same version of MySQL on client and slave (this is generally good replication advice in all cases). There have been some bugs in replication that cause problems when the master and slave are on different versions. On Thu, 2010-03-18 at 14:03 +0530, Vikram A wrote: Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Ian Simpson System Administrator MyJobGroup -- 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 Storage Engine
Hi Neil, As Krishna said, Innodb is generally always superior to MyISAM unless you need full-text search (and even if you do there are alternatives to MyISAMs full-text search, which I've often found to be lacking in performance). A poorly optimised query will be slow whether it is called from a procedure or executed directly by the page. I'd advise analysing your joined queries carefully, using the EXPLAIN tool in MySQL, and add appropriate indexes to your tables. Also, if you are developing in PHP, you may wish to look at the memcache module, which can cache function results in memory in PHP. This can often be helpful for storing results for very frequently called queries. On Thu, 2010-03-18 at 09:18 +, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication - LINUX to WIN
Hi Vikram, As far as I know it's just table names: I think it's related to the fact that the Windows file system is case-insensitive, while Linux filesystems generally are not. On Thu, 2010-03-18 at 15:18 +0530, Vikram A wrote: Hello Ian Simpson, Thank you for reply. We defined table names, fields in lower cases except the KEYS . I hope the Key will not taken into the account[all in upper case]. Regarding the version we will keep same version as you said. Thank you --- On Thu, 18/3/10, Ian Simpson i...@it.myjobgroup.co.uk wrote: From: Ian Simpson i...@it.myjobgroup.co.uk Subject: Re: Replication - LINUX to WIN To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Date: Thursday, 18 March, 2010, 9:33 AM Hi Vikram, There are a couple of potential problems with Linux to Windows replication, but they are easily avoidable. 1) Table names: There are case-sensitivity differences between Linux and Windows: Linux is case-sensitive as regards table names, whereas Windows is not. Given that you are replicating from a Linux master to a Windows slave, you shouldn't hit too many problems with this, but if you ever reverse the order for some reason there could be problems. 2) MySQL versions: try to make sure that you always use the same version of MySQL on client and slave (this is generally good replication advice in all cases). There have been some bugs in replication that cause problems when the master and slave are on different versions. On Thu, 2010-03-18 at 14:03 +0530, Vikram A wrote: Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Ian Simpson System Administrator MyJobGroup __ The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Question about DELETE
Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all the rows it needs to delete. On Thu, 2010-03-18 at 10:03 -0400, Price, Randall wrote: Thanks for your responses on this. However, I suspect that the indexes are being rebuilt over and over during the mass delete operation. If I delete a small number of records (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 5) it may only take a minute or so. If I delete a large number of records (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 500) it may take upwards of an hour or more. So what would cause this increased slowness the more records you delete, unless the indexing is happening multiple times? Thanks, -Randall Price From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, March 18, 2010 6:48 AM To: Ananda Kumar Cc: Price, Randall; [MySQL] Subject: Re: Question about DELETE Given that OP is talking about a single delete statement, I'm gonna be very surprised if he manages to squeeze an intermediate commit in there :-) For a single-statement delete on a single table, the indexes will be rebuilt only once. I'm not entirely sure what happens to cascaded deletes, though. On Thu, Mar 18, 2010 at 6:05 AM, Ananda Kumar anan...@gmail.commailto:anan...@gmail.com wrote: Hi, It depends how frequently ur doing a commit. If you have written a plsql, with loop and if you commit after each row is deleted, then it get update for each row. Else if you commit at the end the loop, it commits only once for all the rows deleted. regards anandkl On Thu, Mar 18, 2010 at 1:21 AM, Price, Randall randall.pr...@vt.edumailto:randall.pr...@vt.eduwrote: Hello, I have a simple question about deleting records from INNODB tables. I have a master table with a few child tables linked via Foreign Key constraints. Each table has several indexes as well. My question is: if I delete many records in a single delete statement (i.e., DELETE FROM table WHERE id = 1 AND id = 5 ... AND ID = 100) how many times are the foreign keys/indexes updated? Once for the entire DELETE operation or one time for each record that is deleted? Thanks, Randall Price -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Remove 'debian-sys-maint' Account?
Hi Carlos, The debian-sys-maint account is used for two things that I am aware of: 1) the /etc/init.d/mysql stop command uses it to perform a controlled shutdown of MySQL. 2) on execution of /etc/init.d/mysql start, it uses this account to run a CHECK TABLE on all of your tables The first function is quite handy, the second is downright annoying, especially if you have any large tables, as these will take a long time to check. There is a way of stopping it from performing the table check without disabling the maintenance account, but I can't remember it off-hand. Thanks On Tue, 2010-03-02 at 13:15 -0500, Carlos Williams wrote: I am using Debian 'Squeeze' / Testing on with MySQL 5.1.41-3 installed. It is a fresh install and I was checking all the system accounts and noticed that Debian has a 'debian-sys-maint' account on 'localhost'. Has anyone ever removed this account? Do I need it or can I safely remove this account? I don't understand why it's there. I don't want to break MySQL even though there is no data or databases on this machine but I would like to keep this as clean as possible. Thanks for any input. -Carlos -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simply join that confuses me.
I think this should work... SELECT b.Name AS Boss name, ad.Name AS Admin name, as.Name AS Assistant name FROM Projects AS pr LEFT JOIN People AS b ON b.ID = pr.Boss_ID LEFT JOIN People AS ad ON ad.ID = pr.Admin_ID LEFT JOIN People AS as ON as.ID = pr.Assistant_ID WHERE pr.Project_ID = 5 I went with LEFT JOIN rather than INNER in case there might be roles that weren't always filled on a particular project. On Tue, 2009-09-29 at 22:11 -0700, Brian Dunning wrote: I have a table of projects with several columns for the IDs of some people in various roles, and then a table of the people. How can I get all the people for a given project, but keep their roles straight? Projects -- Project_ID Boss_ID Admin_ID Assistant_ID People ID Name Can I do something like this: SELECT * from Projects where Project_ID = 5, Name from People where Projects.Boss_ID = People.ID as Boss_Name, Name from People where Projects.Admin_ID = People.ID as Admin_Name, Name from People where Projects.Assistant_ID = People.ID as Assistant_Name I know that syntax isn't right but I'm not sure where to go. -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Prevent execution of queries without a WHERE clause
Thanks John, that's done the trick On Mon, 2009-09-07 at 18:24 +0100, John Daisley wrote: Add the option 'safe-updates' to the mysql section of your 'my.cnf' / 'my.ini' file and restart the mysqld service. [mysql] Safe-updates Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Ian Simpson i...@it.myjobgroup.co.uk Sent: Monday, September 07, 2009 5:14 PM To: mysql@lists.mysql.com Subject: Prevent execution of queries without a WHERE clause Hi all, I vaguely recall finding mention a MySQL command or start-up option that blocked any update or delete query that didn't have a WHERE component, to prevent statements accidentally affecting too many rows (like those of a certain absent-minded web developer who might work for the same company as me...). I now can't find any reference to it, other than a vague mention of using safe mode in the comments in the mysql docs; it doesn't explain if that is starting with --safe-mode, or using the mysqld_safe script. Both of these modes seem remarkably poorly documented, making me unwilling to experiment with them without advice, in case one of them disables networking or something similar. Hoping someone can help with this. Thanks -- Ian Simpson System Administrator MyJobGroup -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Prevent execution of queries without a WHERE clause
Hi Darren, Once I knew the name of the command I could Google it and find all the relevant documentation, so I've put additional directives in place to override the limits on SELECTS. Thanks for the thought, though. On Tue, 2009-09-08 at 15:32 +0100, Darren Cassar wrote: Hi, You can check this feature out on section 2.9 on the mysql 5.0 certification guide (page 44). this feature has some side effects other than requesting a where with deletes and updates i.e. Quoting for whoever doesn't have the MySQL certification study guide 1.UPDATE and DELETE statments are allowed only if then include a WHERE clause that spedifically identifies which records to update or delete by means of a key,value or if they include a LIMIT clause. 2. Output from single-table SELECT statments is restricted to no more than 1,000 rows unless the statment include a LIMIT clause 3. Multiple-table SELECT statments are allowed only if MySQL will examine no more than 1,000,000 rows to process the query. The --i-am-a-dummy option is a synonym for --safe-updates. :) Gluck Darren On Tue, Sep 8, 2009 at 10:25 AM, Ian Simpson i...@it.myjobgroup.co.uk wrote: Thanks John, that's done the trick On Mon, 2009-09-07 at 18:24 +0100, John Daisley wrote: Add the option 'safe-updates' to the mysql section of your 'my.cnf' / 'my.ini' file and restart the mysqld service. [mysql] Safe-updates Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: Ian Simpson i...@it.myjobgroup.co.uk Sent: Monday, September 07, 2009 5:14 PM To: mysql@lists.mysql.com Subject: Prevent execution of queries without a WHERE clause Hi all, I vaguely recall finding mention a MySQL command or start-up option that blocked any update or delete query that didn't have a WHERE component, to prevent statements accidentally affecting too many rows (like those of a certain absent-minded web developer who might work for the same company as me...). I now can't find any reference to it, other than a vague mention of using safe mode in the comments in the mysql docs; it doesn't explain if that is starting with --safe-mode, or using the mysqld_safe script. Both of these modes seem remarkably poorly documented, making me unwilling to experiment with them without advice, in case one of them disables networking or something similar. Hoping someone can help with this. Thanks -- Ian Simpson System Administrator MyJobGroup -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Prevent execution of queries without a WHERE clause
Hi all, I vaguely recall finding mention a MySQL command or start-up option that blocked any update or delete query that didn't have a WHERE component, to prevent statements accidentally affecting too many rows (like those of a certain absent-minded web developer who might work for the same company as me...). I now can't find any reference to it, other than a vague mention of using safe mode in the comments in the mysql docs; it doesn't explain if that is starting with --safe-mode, or using the mysqld_safe script. Both of these modes seem remarkably poorly documented, making me unwilling to experiment with them without advice, in case one of them disables networking or something similar. Hoping someone can help with this. Thanks -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: grant user create privilege
Did you flush privileges after creating the user? On Thu, 2009-05-07 at 08:54 -0500, Jim Lyons wrote: It's hard to believe this to be the case since I assume you've created other databases in this instance, but the error on create database, which is essentially a mkdir in Unix, makes me wonder if you don't have a file permissions error on the datadir directory. On Wed, May 6, 2009 at 9:14 AM, John Clement john.clem...@readingroom.comwrote: I'm clearly doing something wrong. All I want is to grant a user rights to create databases: grant create on *.* to 'user'@'localhost' identified by 'pass'; doesn't do the trick, nor does grant super on *.* to 'user'@'localhost' identified by 'pass'; The user in question was originally created using grant all privileges on their_db.* to 'user'@'localhost' identified by 'pass'; If I try logging in as this user though the following happens: mysql create database testdb; ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'testdb' Can someone point out the error of my ways? Many thanks, jc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MYSQLDUMP ERROR
MySQL dump calls LOCK TABLES before dumping the data (presumably to prevent data modification halfway through the process). LOCK TABLES has its own privilege (conveniently given the same name) which your user account will have to be given in order to run a mysqldump. On Fri, 2009-05-01 at 15:49 +0530, Krishna Chandra Prajapati wrote: Hi lists, I have given select privileges to database tables. when i am taking mysqldump remotely it's giving error. [prajap...@beta2 prajapati]$ mysqldump --verbose -h 152.20.1.115 -u dip dip states -pdip state.sql -- Connecting to 152.20.1.115... mysqldump: Got error: 1044: Access denied for user 'dip'@'152.20.1.%' to database 'dip' when doing LOCK TABLES Thanks, Krishna Chandra Prajapati -- Ian Simpson System Administrator MyJobGroup -- 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] Re: REPOST: ON DUPLICATE failure
The reporting of two rows thing is to do with how MySQL handles INSERT ... ON DUPLICATE KEY UPDATE ... statements; it will report 1 row if it inserts, and 2 rows if it finds a duplicate key and has to update as well. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Just after the first code box. On Wed, 2009-01-21 at 21:44 -0700, Ashley M. Kirchner wrote: Michael Dykman wrote: It worked fine as you wrote it on my v5.0.45, although it reported 2 rows affected on each subsequent run of the insert statement. I thought this odd as I only ran the same statement repeatedly leaving me with one row ever, but the value updated just fine. I noticed that too. It reports 2 rows, but there's only 1. That was my first indication that something's awry. But then when I tried it on 5.1.30, it did the same thing, 2 rows. But at least it DID update on 5.1.30, but not on 5.0.37. -- Ian Simpson System Administrator MyJobGroup -- 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 Use Cascade Delete Properly
If you want deletes to be blocked, then you shouldn't be using ON DELETE CASCADE; the default behaviour is ON DELETE CONSTRAIN, which seems to be what you want; it will refuse to delete any rows that are depended on by rows in other tables. On Thu, 2009-01-01 at 17:07 -0500, Lola J. Lee Beno wrote: I'm trying to understand how to use cascade delete properly but not sure if I have this backwards or not. Here's an example: I have two tables: mysql describe adsource; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | adsource_id | varchar(35) | NO | PRI | NULL| | | company_id | varchar(35) | YES | MUL | NULL| | | location| varchar(50) | YES | | NULL| | | url | varchar(200) | YES | | NULL| | +-+--+--+-+-+---+ mysql describe jobposts; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | jobpost_id | varchar(35) | NO | PRI | NULL| | | company_id | varchar(35) | NO | MUL | NULL| | | details | text | YES | | NULL| | | job_title| varchar(50) | YES | | NULL| | | postdate | date | YES | | NULL| | | salary | decimal(5,2) | YES | | NULL| | | deadlinedate | date | YES | | NULL| | | adsource_id | varchar(35) | YES | MUL | NULL| | +--+--+--+-+-+---+ For jobposts; I have adsourcefk referencing adsource.adsource_id with cascade delete set. For adsource, I have companyfk referencing company.company_id with cascade delete set. Now, say I have three jobposts records that have one referenced adsource record. If I delete one jobposts record, there now remains two jobposts records. If I delete the adsource record, the two jobposts records get deleted. I don't want this happening. What I want to have happen is: if I try to delete an adsource record and there are jobposts records containing that id as foreign key, I want the delete to NOT happen. I can't use triggers because for some reason I can't get the triggers working properly. Is there any way I can do this on the database side without having to write code in the application code I'm working on? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ No greater injury can be done to any youth than to let him feel that because he belongs to this or that race he will be advanced in life regardless of his own merits or efforts. - Booker T. Washington -- Ian Simpson Award Winning System Administrator MyJobGroup -- 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 Use Cascade Delete Properly
That looks ok to me; I seem to have misremembered the RESTRICT keyword as CONSTRAIN, since it's been a while since I've had to use it. Bear in mind that I don't think MySQL has a check to make sure that the same foreign key does not already exist; I have seen a table that had the same foreign key three times. You should be ok as you are specifying a name for the constraint, and it checks for unique names. On Fri, 2009-01-02 at 06:03 -0500, Lola J. Lee Beno wrote: Ian Simpson wrote: If you want deletes to be blocked, then you shouldn't be using ON DELETE CASCADE; the default behaviour is ON DELETE CONSTRAIN, which seems to be what you want; it will refuse to delete any rows that are depended on by rows in other tables. Wouldn't that be: ALTER TABLE `jobsearchtwodb`.`jobposts` ADD CONSTRAINT `adsourcefk` FOREIGN KEY `adsourcefk` (`adsource_id`) REFERENCES `adsource` (`adsource_id`) ON DELETE RESTRICT ON UPDATE NO ACTION; ON DELETE RESTRICT having the behavior like ON DELETE CONSTRAIN? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ No greater injury can be done to any youth than to let him feel that because he belongs to this or that race he will be advanced in life regardless of his own merits or efforts. - Booker T. Washington -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'ODBC'@'localhost' (using password: YES)'
Hi Varuna, The problem looks like it is with the command you issued. The correct syntax is: mysqladmin -uroot -p shutdown then supply root password. Without giving the -uroot argument, it doesn't know that you are trying to log in as the root user, which is why it is telling you that access is denied for [EMAIL PROTECTED], rather than [EMAIL PROTECTED] Thanks On Sat, 2008-10-04 at 13:56 +0530, Varuna Seneviratna wrote: I am using WinXP.I am wanted to shutdown MySQL service from the command line and ran the command mysqladmin -p root shutdown next the root password was asked for,I entered the correct password, when I entered the password the below displayed error was the result mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'ODBC'@'localhost' (using password: YES)' How can I correct this? Is this because of a firewall preventing access to port 3306?If a firewall is preventing access how was it able to ask for the password? I have Nortan Internet Security trial version running and the windows firewall is disabled.If this is caused by a firewall barrier please tell me how to open the port in Nortan Internet Security. Varuna -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Databasing on busy webserver
Hi Ben, It sounds like what you're looking for is an ENUM value: http://dev.mysql.com/doc/refman/5.0/en/enum.html Bear in mind when using this data-type that if you do want to add a new value (such as a new state/country), you will have to perform an ALTER TABLE statement, which can take some time to execute over a table storing a lot of data. Using the relational method means you'd just have to add one row to a table, which is significantly faster. Thanks On Mon, 2008-09-22 at 17:12 -0400, Ben A.H. wrote: Hello, We are setting up a relatively common web application which collects user information... Right off the bat our system will have over 200,000 USER RECORDS so having an efficient database lookup scheme is critical. I am a programmer/developer with some education in databasing but my forte is programming hence this odd (I think) question... Obviously we'll have the standard USER table a bunch of supporting tables. For items like STATE/PROVINCE standard database logic would dictate I setup: USER name email .etc... StateID (foreign key) STATE StateID StateName But I often wonder if there's any benefit in having a State table... For one, new States/Provinces are not that likely, removal of these entities is also unlikely (except maybe Quebec :-)) so the chances of having to make alternations to the State table near nil. It raises the question of whether or not a State Table is even necessary. Using a traditional SQL State table, I'd have to do an SQL query to populate User State/Province options every time the New User Registration form is shown - isn't this needless overhead?! Would my webforms not load faster if State/Province information was hard-coded as options for a combobox? Is this something people have experimented with? There are various other fields that I believe could be handled like this for a cumulative performance boost. For example: country, state/province, gender, industry, occupation, ethnicity, language are all options that aren't going to change that often. Then again, when we do have to display the users choice; for example if user.countryID=3 we'd have to have a way to turn that number 3 into the word Canada when we display the users profile... I'd probably do this via XML lookup. Has anyone experimented with the benefits/tradeoffs of such a scheme? As I've said, we are dealing with at least 200,000 user records, probably 300,000-400,000 in the next year. The User table contains at least 50 attributes, 15 of which are Foreign Keys that link to tables that will likely never change (the users choices for countryID, stateID, OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID, industryID, occupationID)... ThanX in advance Ben -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
Hi David, Try Select company, state, sales, sum(sales) / sales as percent From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: I get: Error Code : 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network Engineer Safe Data, Inc. (910) 285-7200[EMAIL PROTECTED] -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 10:37 AM To: David Ruggles Cc: mysql Subject: Re: How do I (can I) use aggregate functions inside a select yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles [EMAIL PROTECTED] wrote: I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- Ian Simpson System Administrator MyJobGroup -- 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'
Hi Jesse, If you're specifying the password in plain text, you shouldn't put the PASSWORD directive in there; you only use PASSWORD if you're using the hashed password that MySQL will actually store. GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'LongPasswordHere' WITH GRANT OPTION or GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'HexadecimalString' WITH GRANT OPTION Also, you will need to execute FLUSH PRIVILEGES once you're done, since MySQL normally only checks the privilege tables on start-up. On Mon, 2008-07-21 at 20:35 -0400, Jesse wrote: 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 -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: delete query question
If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
RE: delete query question
Oh well ;) It looks like you can use joins in a delete statement, and delete the joined rows, which will delete from the individual tables. So something like: delete table1, table2 from table1 inner join table2 on table1.ID = table2.ticket where... should do it I modified the above code from http://dev.mysql.com/doc/refman/5.0/en/delete.html just search in the page for 'join' and you'll find the relevant section On Tue, 2008-07-08 at 11:35 -0400, Jeff Mckeon wrote: -Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2008 11:27 AM To: Jeff Mckeon Cc: mysql@lists.mysql.com Subject: Re: delete query question If the tables are InnoDB, you could temporarily set up a foreign key relationship between the two, with the 'ON DELETE CASCADE' option. Nope, MyISAM... On Tue, 2008-07-08 at 11:14 -0400, Jeff Mckeon wrote: I think this is possible but I'm having a total brain fart as to how to construct the query.. Table2.ticket = table1.ID Table2 is a many to 1 relationship to table1 I need to delete all records from table1 where created unix_timestamp(date_sub(now(), interval 3 month)) And all rows from table2 where Table2.ticket = Table1.ID (of the deleted rows..) Can't this be done in one query? Or two? Thanks, Jeff -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Do I need to use GROUP BY to do this?
Off the top of my head I can't think of a way of getting the output in the format that you want. If you use a query like: SELECT SUBSTRING(sales_date,1,10), sales_type, COUNT(sales_id) FROM sales_activity GROUP BY SUBSTRING(sales_date,1,10), sales_type; You'll get output like: Datetypenumber 2008-06-15 1 4 2008-06-15 2 2 2008-06-16 1 2 2008-06-17 1 2 which is the data that you want in a different output format. Thanks On Mon, 2008-06-23 at 18:16 -0700, Grant Giddens wrote: Ian, Thanks for the help, this query worked perfectly. Can you also help me with one more query? Say my sales_type=1 for a sale, and sales_type=2 for a return. I'd like to do 1 query to get a count of the sales and returns for each day. Here was my test data again: INSERT INTO `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1); INSERT INTO `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1); The result of the query should be: date salesreturns -- -- 2008-06-1542 2008-06-1620 2008-06-1720 Thanks, Grant --- On Wed, 6/18/08, Ian Simpson [EMAIL PROTECTED] wrote: From: Ian Simpson [EMAIL PROTECTED] Subject: Re: Do I need to use GROUP BY to do this? To: Grant Giddens [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Date: Wednesday, June 18, 2008, 11:02 AM I happen to have worked on a similar query this morning, so it's in my mind :) SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id) FROM sales_activity WHERE sales_type = 1 GROUP BY SUBSTRING(sales_date,1,10); should do the trick. On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote: Hi, nbsp; I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: -- -- Table structure for table `sales_activity` -- CREATE TABLE `sales_activity` ( nbsp; `sales_id` int(11) NOT NULL auto_increment, nbsp; `sales_date` datetime NOT NULL default '-00-00 00:00:00', nbsp; `sales_type` tinyint(4) NOT NULL default '0', nbsp; PRIMARY KEYnbsp; (`sales_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `sales_activity` -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Reset of Status Parameters
I tend to use the 'mytop' program, which shows the average queries/second for the entire lifetime and for the last 5 seconds, as well as showing a bunch of other statistics and a list of running queries. It's a handy little monitoring tool. On Fri, 2008-06-20 at 12:17 +0530, Venu Madhav Padakanti wrote: I am using MySQL version 5.0.22, I am interested in knowing the current performance on the MySQL. With the status command we can get the queries per second but it will average since the beginning of time when SQL was up and running and not the current rate? Is there any way to reset that parameter so that the data can reflect current without restarting the MySQL Thanks in advance ..venu -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Do I need to use GROUP BY to do this?
I happen to have worked on a similar query this morning, so it's in my mind :) SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id) FROM sales_activity WHERE sales_type = 1 GROUP BY SUBSTRING(sales_date,1,10); should do the trick. On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote: Hi, nbsp; I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: -- -- Table structure for table `sales_activity` -- CREATE TABLE `sales_activity` ( nbsp; `sales_id` int(11) NOT NULL auto_increment, nbsp; `sales_date` datetime NOT NULL default '-00-00 00:00:00', nbsp; `sales_type` tinyint(4) NOT NULL default '0', nbsp; PRIMARY KEYnbsp; (`sales_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `sales_activity` -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Very slow inserts into InnoDB tables
Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Very slow inserts into InnoDB tables
Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Very slow inserts into InnoDB tables
Hi guys, thanks for pitching in. The inserts are from replication; we're not using transactions on the master (yet), and I don't think there's a way of telling MySQL to batch incoming replication statements if they're not already in a transaction. Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify
RE: Very slow inserts into InnoDB tables
Hi Guys, Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. Looks like I'll be having more words with my hosting company about this... Thanks for all your help -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very simple two column tables (inserts into MyISAM tables run fine). Meanwhile, the original server can happily process around 1000 inserts/sec into an identical table. The MySQL configuration of the two databases is identical, except for the tablespace file size (the new server has a larger tablespace defined), and the InnoDB logs (again, new server has larger logs). Can anybody suggest an area of investigation as to the cause? Thanks, -- Ian Simpson
RE: Very slow inserts into InnoDB tables
Hi Jerry, It could be a kernel issue; however, currently I'm suspecting that the drive in the new server simply doesn't have the same bandwidth capability. The iostat results I'm getting (although I'm not an expert in reading them, having only learned of it about 3 hours ago) suggest that the older server is handling roughly the same data quantities, but just using a much lower percentage of the drive's bandwidth. I can't seem to find a tool which reports on exactly how much write bandwidth a drive has; everything seems to focus on reading speed. Thanks, -- Ian Simpson On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote: Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL PROTECTED] wrote: Hi list, Have a bit of a mystery here that I hope somebody can help with. I've just got a new server that I'm using as a dedicated MySQL server. In terms of hardware it's pretty much identical, if not slightly superior to an existing server already in production use. It's having a real struggle processing INSERT statements to InnoDB tables; it's maxing out at around 100 inserts per second, even with very
RE: Very slow inserts into InnoDB tables
That's pretty much what I've been doing to get that the drive is running at 100% bandwidth. What I'd like is something that just gives the bandwidth of the device in terms of Mb/s: you can probably work it out using that iostat command, seeing how much it wrote and what percentage of the bandwidth it's using, and then doing a calculation with those numbers to get the 100% value, but I don't know if that's valid, since there are generally a number of other operations going on at the same time. Thanks -- Ian Simpson On Fri, 2008-06-13 at 08:48 -0700, Wm Mussatto wrote: On Fri, June 13, 2008 08:26, Ian Simpson wrote: Hi Jerry, It could be a kernel issue; however, currently I'm suspecting that the drive in the new server simply doesn't have the same bandwidth capability. The iostat results I'm getting (although I'm not an expert in reading them, having only learned of it about 3 hours ago) suggest that the older server is handling roughly the same data quantities, but just using a much lower percentage of the drive's bandwidth. I can't seem to find a tool which reports on exactly how much write bandwidth a drive has; everything seems to focus on reading speed. Thanks,  -- Ian Simpson Try something like: iostat -xk /dev/sda /dev/sdb /dev/sdc 10 where the /dev/... are the drives you want to examine and '10' is the redisplay rate. last column is %util. Hope this helps. On Fri, 2008-06-13 at 11:18 -0400, Jerry Schwartz wrote: Having delved a little more into the capabilities of iostat, I've discovered that the drive bandwidth seems to be maxed out while MySQL is running, which I'd peg as the primary candidate for the problem. [JS] That suggests even more strongly that there is a difference in the kernel configuration. More physical I/O would drive the traffic up, by definition. Either MySQL is causing this, or the system file system is causing it. Looks like I'll be having more words with my hosting company about this... Thanks for all your help ? -- Ian Simpson On Fri, 2008-06-13 at 10:40 -0400, Jerry Schwartz wrote: Disk usage: the older server (the one that's running fine) is running more transactions per second, but has lower blocks written and read per second than the new server: [JS] That, to me, suggests that the difference might be in the way the systems themselves are configured. Unfortunately, I don't know how Linux handles file system buffering. The working server (which in addition to replicating is also handling a bunch of read queries) Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 88.47 782.20 998.77 9046888130 11551757459 The new server, which is just trying to handle replication Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 77.83 1367.55 2914.72 358474084 764029986 Thanks, ? -- Ian Simpson On Fri, 2008-06-13 at 19:15 +0530, Alex Arul Lurthu wrote: also how often do you issue a commit. batching the inserts inside a transaction might help. On Fri, Jun 13, 2008 at 6:53 PM, Ananda Kumar [EMAIL PROTECTED] wrote: check for iostat to see if the disk is heavly used. On 6/13/08, Ian Simpson [EMAIL PROTECTED] wrote: Hi Alex, Configurations are identical, other than the differences I initially mentioned. I've diffed both the configuration files and the output of SHOW VARIABLES on both servers. I've contacted my hosting provider to ask about the RAID settings. Variable_name: innodb_flush_log_at_trx_commit Value: 1 Variable_name: sync_binlog Value: 0 Variable_name: innodb_locks_unsafe_for_binlog Value: OFF Thanks -- Ian Simpson On Fri, 2008-06-13 at 17:43 +0530, Alex Arul Lurthu wrote: Please check if the my.cnf configurations to be the same. What are your configuration parameters in terms of innodh flush log trx commit , bin logging, sync binlog and innodb unsafe for binlog ? If the systems have raid, check if the BBWC is enabled on the new host and WB is enabled. On Fri, Jun 13, 2008 at 5:02 PM, Ian Simpson [EMAIL
Re: Starting a 2nd MySQL instance on UNIX
Mark, When you try to log-in to the new instance, are you specifying the new port number to the client? If you don't give it the new port number, then it will connect to the default port, which is presumably your 4.0.20 instance. Mark-E wrote: I have a Solaris box where MySQL 4.0.20 instance is running (to support Bugzilla 2.22). I have loaded mysql5.0 on the same box (for Bugzilla 3.0.3) and created a new mysql50 user that I want to use to run this instance with. I tried to start the instance on another port by running the following command... ./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.0/my.cnf --socket=/tmp/mysql50/mysql.sock --port=3307 --basedir=/usr/local/mysql-5.0 --datadir=/usr/local/mysql-5.0/data --pid-file=/usr/local/mysql-5.0/mysql50.pid --user=mysql50 The instance appears to start but the message Starting the instance comes up and I never get back to the system prompt. it just sits there. If I open another terminal window and do a ps -ef | grep mysql, I can see the new processes running. There is nothing in the error log. I ran the mysql_install_db.sh script to create the mysql database however, I cannot log in. I thought that it creates a root user with no password. I tired logging in as root with no password bu no luck. If i use the mysql 4.0.20 root user password, I get into the 4.0.20 instance even though the mysql50 user does not have mysql 4.0.20 in it's path. when I run mysql at the prompt, how would I differentiate between the 2 instances? So at this point I am stuck. If anyone out can help guide me on what I need to do to ge tthe instance up and running properly, I would appreciate it. I am rather new to MySQL and I have read through the docs but things are still not very clear. Thanks! Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
