Block size of filesystem
Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Will you be using the MyISAM or InnoDB table engines? I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. -Aaron On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Ben Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Upgrade to 5.0.51b seems to have solved the problem. On wednesday, I saw the problem about a dozen times during a peak time. Upgraded wednesday night and have not seen the error since. Thanks Dominik Juan Eduardo Moreno wrote: Hi, This error occur when slave servers could incorrectly interpret an out-of-memory error from the master and reconnect using the wrong binary log position. This was fix in 5.0.48 version. Please, try to update your version ( from 5.0.45) of mysql and try again. regards, Juan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Thanks, Aaron. Will you be using the MyISAM or InnoDB table engines? Both, but InnoDB more. I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. Ok, that's interesting. :-) I'll check it. Iñigo Aaron Blew wrote: Will you be using the MyISAM or InnoDB table engines? I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. -Aaron On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Thanks Ben. I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Perfect. That sounds the way Aaron explain about 16k blocks in InnoDb. And actually we have more InnoDB tables tan MyISAM. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Perfect. I'll check it. Iñigo Ben Clewett wrote: I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Ben Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
I'm seeing that architecture has almost allways the limit in 4 kb (block size - page). Theoretically architecture of 64 bits would offer up to 8 kb, but it seems to be that it usually has 4 kb too because of compatibiliry issues with its i386 ancestors. Any idea about that? We run Intel Core 2 Duo on kernel 2.6.21-1 and i386 by now. Do you think it could be better to change the architecture in order to get the advantages of 8 kb blocks size? Iñigo Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
Will you be using the MyISAM or InnoDB table engines? I had heard that InnoDB uses 16k blocks internally, so that might be a good starting point, though I'd love to have someone confirm or deny that this is actually true. Yep, Aaron. Look at: http://www.innodb.com/innodb/features/ and expand the *wspifmd* link read more of InnoDB Features: database page sizes configurable, from 4 to 64 kB, default 16 kB. Iñigo -Aaron On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García [EMAIL PROTECTED] wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo -- Iñigo Medina García Librería Díaz de Santos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Log Optimisation
Dear MySql, I am trying to optimise InnoDB, and trying to find out how much of the innodb log file contains row data which has not been written to storage. Therefore I can optimize the size of the log, keeping it low to reduce crash recovery time yet high enough to be useful. I can see the varaible 'Innodb_data_pending_writes' which may be what I need. This is zero, I am not sure whether this means I have no uncommitted data in the log, or something else. Can't find anything much on Google. If any member can help me, would be very usesful :) Regards, Ben. * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Log Optimisation
Hi Ben, Dear MySql, I am trying to optimise InnoDB, and trying to find out how much of the innodb log file contains row data which has not been written to storage. Therefore I can optimize the size of the log, keeping it low to reduce crash recovery time yet high enough to be useful. :-) I can see the varaible 'Innodb_data_pending_writes' which may be what I need. This is zero, I am not sure whether this means I have no uncommitted data in the log, or something else. Can't find anything much on Google. If any member can help me, would be very usesful :) What spits SHOW INNODB STATUS\G ? Iñigo Regards, Ben. * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- Iñigo Medina García Librería Díaz de Santos Madrid (Spain) [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Log Optimisation
Thanks for the idea. Unfortunately nothing I can easily use (for instance in MySql Administrator) to log and monitor the lag in bytes between log writes and row data writes. :) Iñigo Medina García wrote: Hi Ben, Dear MySql, I am trying to optimise InnoDB, and trying to find out how much of the innodb log file contains row data which has not been written to storage. Therefore I can optimize the size of the log, keeping it low to reduce crash recovery time yet high enough to be useful. :-) I can see the varaible 'Innodb_data_pending_writes' which may be what I need. This is zero, I am not sure whether this means I have no uncommitted data in the log, or something else. Can't find anything much on Google. If any member can help me, would be very usesful :) What spits SHOW INNODB STATUS\G ? Iñigo Regards, Ben. * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp and the On Update Current_Timestamp clause
Hi, How does one know if ON UPDATE CURRENT_TIMESTAMP was specified when creating a column? How do I retrieve this bit of info from the metadata queries? (also MySQL 4.1) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and the On Update Current_Timestamp clause
SHOW CREATE TABLE ... Martijn Tonies wrote: Hi, How does one know if ON UPDATE CURRENT_TIMESTAMP was specified when creating a column? How do I retrieve this bit of info from the metadata queries? (also MySQL 4.1) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp and the On Update Current_Timestamp clause
SHOW CREATE TABLE ... Yes, I thought so :-( From a coding point of view, this requires parsing... Why isn't there anything in show full columns. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
pls help clarify dox: InnoDB Consistent Non-Locking Read behavior
Hello, I _thought_ I knew how InnoDB worked, but due to a recent mysql doc change, I am no longer sure--the change made the dox significantly less clear, and potentially code-breaking. Please can someone tell me the real behavior of InnoDB in the following case, and ideally clarify the dox too... The question comes up in the first paragraph of 13.5.10.4. Consistent Non-Locking Read: http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. Note that the exception to the rule causes the following anomaly: if you update some rows in a table, a SELECT will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the same table, the anomaly means that you may see the table in a state that never existed in the database. The unclear wording is if you update some rows in a table, a SELECT will see the latest version of the updated rows, but it might also see older versions of any rows What does the author mean by any rows? Do you mean that when you do a SELECT, you may get back a result for your modified row, AND you may ALSO get back a result for an older version of the SAME row? This is very very important as it affects how we can use non-locking read at the lowest level of our code. You may wonder why I suspect this case...it's all because of what the text USED to say: (change made by user paul on 2007-08-23 16:38:39 +0200 (Thu, 23 Aug 2007) http://lists.mysql.com/commits/32967?f=plain ) -see the latest version of the updated rows, while it sees the -old version of other rows. If other users simultaneously update -the same table, the anomaly means that you may see the table in -a state that never existed in the database. +see the latest version of the updated rows, but it might also +see older versions of any rows. If other users simultaneously +update the same table, the anomaly means that you may see the +table in a state that never existed in the database. the old wording the old versions of OTHER rows was crystal clear: you will only see one copy of your new row, and it will be your new copy. the new wording is unclear. but the new wording makes me wonder if InnoDB could return multiple copies of the rows I have modified. Can someone clarify the actual InnoDB behavior? Could someone suggest a clearer wording for the dox that 1) expresses the actual InnoDB behavior 2) covers whatever case Paul was trying to cover when he made that change? Thanks for your time! Hopefully we can clarify this for all mysql users! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Block size of filesystem
That's true in some workloads, but with InnoDB you'll usually run into data file fragmentation before filesystem fragmentation (unless it's a shared system). This is especially true if your application runs a lot of updates or deletes as random chunks of space will become free at different points within the InnoDB data file. In a business intelligence application where there's not much deleting this probably isn't a concern... I think the best way to approach it may be to look at your average row size and base your InnoDB and filesystem block sizes around that. -Aaron On Fri, May 9, 2008 at 12:43 AM, Ben Clewett [EMAIL PROTECTED] wrote: I would use as large a block size as you dare, especially with InnoDB. Makes reading and writing faster as custs down seek time as cuts down disk fragmenation and avoids block table reads. With MyIsam you have lots of files, but if you only have a few again might work well with a large block size. Also have a look at the stripe size of your raid system, might work well aligning them if you can. This URL also gives some tips for an ext3 file system on RAID, look for 'stride': http://insights.oetiker.ch/linux/raidoptimization.html Ben Iñigo Medina García wrote: Hi friends, we're developing a new web application that works with a db around 1gb and 30 tables. We work with linux, and I'm evaluating the benefit of making an specific partition (ext3) for mysql in order to have a bigger Block Size. Block Size by default in / partition is 4096. Do you think that could be good? Any experience? Iñigo * This e-mail is confidential and may be legally privileged. It is intended solely for the use of the individual(s) to whom it is addressed. Any content in this message is not necessarily a view or statement from Road Tech Computer Systems Limited but is that of the individual sender. If you are not the intended recipient, be advised that you have received this e-mail in error and that any use, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. We use reasonable endeavours to virus scan all e-mails leaving the company but no warranty is given that this e-mail and any attachments are virus free. You should undertake your own virus checking. The right to monitor e-mail communications through our networks is reserved by us Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley, Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17 Registered in England No: 02017435, Registered Address: Charter Court, Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best CPU config for a busy DB server
Hey everyone, I'm pretty sure this is right but I wanted to double-check: Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best CPU config for a busy DB server
Yes it can use multiple cores. Mysqld is a multithreaded service. Saravanan --- On Sat, 5/10/08, JW [EMAIL PROTECTED] wrote: From: JW [EMAIL PROTECTED] Subject: Best CPU config for a busy DB server To: mysql@lists.mysql.com Date: Saturday, May 10, 2008, 3:52 AM Hey everyone, I'm pretty sure this is right but I wanted to double-check: Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking (Was: Best CPU config for a busy DB server)
On Friday 09 May 2008 04:32:10 pm Saravanan wrote: --- On Sat, 5/10/08, JW [EMAIL PROTECTED] wrote: From: JW [EMAIL PROTECTED] Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able\ to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW Yes it can use multiple cores. Mysqld is a multithreaded service. Saravanan I just found this interesting tidbit: *** MySQL On Multi-Core Machines - The DevShed technical tour explains that MySQL can spawn new threads, each of which can execute on a different processor/core. What it doesn’t say is that a single thread can only execute on a single core, and if that thread locks a table, then no other threads that need that table can execute until the locking thread/query is complete. Short answer: MySQL works well on multi-core machines until you lock a table. One of our programmers was wondering if this is referring to such implicit lock such as when you you read from a table (SELECT) or only explicit table locking, which we don't (currently) use in any of our code. Does anyone know? JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking (Was: Best CPU config for a busy DB server)
At 05:05 PM 5/9/2008, you wrote: On Friday 09 May 2008 04:32:10 pm Saravanan wrote: --- On Sat, 5/10/08, JW [EMAIL PROTECTED] wrote: From: JW [EMAIL PROTECTED] Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able\ to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW Yes it can use multiple cores. Mysqld is a multithreaded service. Saravanan I just found this interesting tidbit: *** MySQL On Multi-Core Machines - The DevShed technical tour explains that MySQL can spawn new threads, each of which can execute on a different processor/core. What it doesnât say is that a single thread can only execute on a single core, and if that thread locks a table, then no other threads that need that table can execute until the locking thread/query is complete. Short answer: MySQL works well on multi-core machines until you lock a table. One of our programmers was wondering if this is referring to such implicit lock such as when you you read from a table (SELECT) or only explicit table locking, which we don't (currently) use in any of our code. Does anyone know? JW Table locking will occur with MyISAM tables when any row(s) of the table is being updated (Update,Delete,Insert,Load Data etc). If you are only executing Select statements, then they can be executed in parallel and won't be blocked. You can have 4 or more processors, but the biggest bottleneck will still be disk access. Putting more RAM into the computer will help if you if you also tune your My.Cnf file to make use of the extra RAM. If the table is small enough, you could try and put it all in memory using the Heap table engine and avoid disk access altogether. Mike -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
