Re: Innodb buffer pool size filling up
That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
RE: Innodb buffer pool size filling up
The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. The total database size is about 750Mb. Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: Innodb buffer pool size filling up
The Innodb Buffer Pull usually follow a growth over time that resembles an horizontal asintot ( http://www.maecla.it/bibliotecaMatematica/go_file/MONE_BESA/grafico.gif) This to leverage all its size! So should not be a problem! Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. The total database size is about 750Mb. Regards Machiel *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* 01 December 2009 01:12 PM *To:* machiel.richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- Claudio
Re: error creating table
Hi Jim, Only difference is auto increment in the field. You cannot have two auto increment in a single table also auto increment field must be the key. On 11/30/09, Jim Lyons jlyons4...@gmail.com wrote: I created dummy tables for Roles and Users specifying the primary keys as 'serial' and then tried the below syntax. It failed. Then I redefined the primary keys in the parent tables to be the exact same type as the foreign keys in UserRole and it worked. So, check the datatype of all your keys and make sure they match. On Mon, Nov 30, 2009 at 11:50 AM, Sharique uddin Ahmed Farooqui saf...@gmail.com wrote: Hi, I'm developing a CMS, in which I have 3 tables user, roles and userRoles. Here is the code for userRoles table. on this I'm getting error creating table (error code 1005), both userid and roleid are pkey (int, auto increment) CREATE TABLE IF NOT EXISTS `mydb`.`UserRole` ( `roleid` INT(10) UNSIGNED NOT NULL , `userid` INT(10) UNSIGNED NOT NULL , PRIMARY KEY (`roleid`, `userid`) , INDEX `fk_userid` (`userid` ASC) , INDEX `fk_roleid` (`roleid` ASC) , CONSTRAINT `fk_userid` FOREIGN KEY (`userid` ) REFERENCES `mydb`.`Users` (`userid` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid` ) REFERENCES `mydb`.`Roles` (`roleid` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci; -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Edit Stored Procedures
I'm trying to edit one of my stored procedures using MySQL Query Browser and this is what I get back as a listing when I select Edit All Procedures: DROP PROCEDURE IF EXISTS `kwbo4418_2008bookworminventory`.`UpdateStock` $$ /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$ (null) $$ /*!50003 SET SESSION sql_mo...@temp_sql_mode */ $$ I used to edit it in this fashion just fine, and I know the procedure is there on the server, as we run it many times a day successfully. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How can I improve this query?
select distinct a.PartNumber as 'Part Number', ( select count(1) from Transactions b where b.PartNumber = a.PartNumber) as 'No. Sold' from listings a order by a.PartNumber It currently takes 30 seconds to run. Transactions has 1200 records and listings has 7000. Multiple listings can have the same part number, as can transactions. We'd like to know how many transactions there are for each part number, including those part numbers for which there are listings but no transactions. Given the and zero transactions requirement, I can't figure out how to do this query with a join. -- 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 can I improve this query?
On 12/1/09 2:21 PM, David Shere dsh...@steelerubber.com wrote: select distinct a.PartNumber as 'Part Number', ( select count(1) from Transactions b where b.PartNumber = a.PartNumber) as 'No. Sold' from listings a order by a.PartNumber It currently takes 30 seconds to run. Transactions has 1200 records and listings has 7000. Multiple listings can have the same part number, as can transactions. We'd like to know how many transactions there are for each part number, including those part numbers for which there are listings but no transactions. Given the and zero transactions requirement, I can't figure out how to do this query with a join. how about using LEFT JOIN: SELECT ... FROM listings a LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber ... wouldn't that tabulate also the unsold parts? and for speed, does Transactions.PartNumber have an index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Innodb buffer pool size filling up
-Original Message- From: machiel.richards [mailto:machiel.richa...@gmail.com] Sent: Tuesday, December 01, 2009 6:17 AM To: 'Claudio Nanni' Cc: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up The size was at 2Gb and was recently changed to 3Gb in size during the last week of November (around the 23rd / 24th) and as of this morning was already sitting at 2.3gb used. [JS] At the others have said, the whole purpose of a buffer pool is to hold as much frequently used data as possible. That lowers the probability of having to do physical I/O, which is much slower than memory access. The buffer pool should be full. The total database size is about 750Mb. [JS] It does surprise me that the buffer pool fills up, even though it is three times the size of your database. My guess is that whatever mechanism is used to scavenge space in the buffer pool isn't triggered until the buffer pool is full; but that is simply a guess, I really have no idea. 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 Regards Machiel From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: 01 December 2009 01:12 PM To: machiel.richards Cc: mysql@lists.mysql.com Subject: Re: Innodb buffer pool size filling up That is basically its use, the buffer pool is the collection of all mysql innodb buffers, and after warm up it goes to keep all cacheable data. How big is your INNODB_BUFFER_POOL_SIZE ? Cheers Claudio 2009/12/1 machiel.richards machiel.richa...@gmail.com There are no errors in the logs at all. We have a script to calculate the percentages used and free in order to do daily,weekly and monthly reporting trend analyses and thus we notice the growth but no errors. -Original Message- From: Neil Aggarwal [mailto:n...@jammconsulting.com] Sent: 01 December 2009 08:55 AM To: mysql@lists.mysql.com Subject: RE: Innodb buffer pool size filling up Machiel: We have a MySQL database where the INNODB_BUFFER_POOL_SIZE keeps on filling up. Are you getting any errors or just noticing the buffer pool is full? I saw some error messages about the buffer pool size becoming a problem if the fscync is slow. Do you see any more info in the logs? Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS VPS for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio -- 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 can I improve this query?
At 01:21 PM 12/1/2009, David Shere wrote: select distinct a.PartNumber as 'Part Number', ( select count(1) from Transactions b where b.PartNumber = a.PartNumber) as 'No. Sold' from listings a order by a.PartNumber It currently takes 30 seconds to run. Transactions has 1200 records and listings has 7000. Multiple listings can have the same part number, as can transactions. We'd like to know how many transactions there are for each part number, including those part numbers for which there are listings but no transactions. Given the and zero transactions requirement, I can't figure out how to do this query with a join. Do the Left Join as the other person said and also replace the Distinct Order By with Group by PartNumber and you won't need the sort. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
