Innodb buffer pool usage
Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel
Re: Innodb buffer pool usage
Hi Machiel, What do you mean with innodb buffer pool is at 100% full ? There are several status variables associated with innodb buffer pool ie: Innodb_buffer_pool_pages_free is the number of unused data pages. Innodb_buffer_pool_pages_total is the total number of pages. Innodb_buffer_pool_pages_data is the total number of used data pages (clean and dirty). Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of clean data pages can be calculated from these first two status variables. etc.. You can calculate the usage ratio with a basic recipe: Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total This is the value that you have to monitor. If its close to 1, then you set up too much memory for innodb (warning: that this can be due to frequent flushed to disk, so you have to check that too); if on the other hand the ratio is too low, then you effectively are running out of resources and may need to add more memory to innodb. You can imply other things, with the other variables too. Obviously you have to do the job and review this values along the time, maybe there are some actions/effects like running reports or etl processes, you have to figure out what is going on as a whole not just the values in the formula. BTW: In Oracle is the same story, one thing is what you reserve for...and other thing is the actual usage. The latter if you are tuning Oracle manually, because one important difference in Oracle 10 and 11 is that the buffers can grow and shrink automatically (if you configure it) so you can say use the 100% memory at your convenience and Oracle can, for example, reduce the sort buffers and extend the index buffers on the fly. Obviously this also has advantages and disadvantages, but as a new DBA is good to get involved in this concepts and comparisons between dbms. Carlos Proal On 3/16/2010 12:46 AM, Machiel Richards wrote: Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel -- 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 usage
HI Carlos We run a script for the monitoring and the output received is as follows: InnoDB Buffer Pool __ Usage 3.00G of 3.00G %Used: 100.00 Read hit 100.00% Pages Free 0%Total: 0.00 Data145.31k 73.91 %Drty: 0.00 Misc 51301 26.09 Latched 0.00 Reads 6.71G4.0k/s From file68.42k 0.0/s0.00 Ahead Rnd41 0.0/s Ahead Sql 1487 0.0/s Writes132.01M79.7/s Flushes 2.31M 1.4/s Wait Free 0 0/s Maybe this will help you out? Are we perhaps monitoring the wrong thing? Regards Machiel -Original Message- From: Carlos Proal [mailto:carlos.pr...@gmail.com] Sent: 16 March 2010 9:31 AM To: mysql@lists.mysql.com Subject: Re: Innodb buffer pool usage Hi Machiel, What do you mean with innodb buffer pool is at 100% full ? There are several status variables associated with innodb buffer pool ie: Innodb_buffer_pool_pages_free is the number of unused data pages. Innodb_buffer_pool_pages_total is the total number of pages. Innodb_buffer_pool_pages_data is the total number of used data pages (clean and dirty). Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of clean data pages can be calculated from these first two status variables. etc.. You can calculate the usage ratio with a basic recipe: Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total This is the value that you have to monitor. If its close to 1, then you set up too much memory for innodb (warning: that this can be due to frequent flushed to disk, so you have to check that too); if on the other hand the ratio is too low, then you effectively are running out of resources and may need to add more memory to innodb. You can imply other things, with the other variables too. Obviously you have to do the job and review this values along the time, maybe there are some actions/effects like running reports or etl processes, you have to figure out what is going on as a whole not just the values in the formula. BTW: In Oracle is the same story, one thing is what you reserve for...and other thing is the actual usage. The latter if you are tuning Oracle manually, because one important difference in Oracle 10 and 11 is that the buffers can grow and shrink automatically (if you configure it) so you can say use the 100% memory at your convenience and Oracle can, for example, reduce the sort buffers and extend the index buffers on the fly. Obviously this also has advantages and disadvantages, but as a new DBA is good to get involved in this concepts and comparisons between dbms. Carlos Proal On 3/16/2010 12:46 AM, Machiel Richards wrote: Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel -- 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=arch...@jab.org
Re: dash was converted o a wierd character
On *nix, look for a utility called convmv. I've got a hunch that your original file comes from a windows host, and the filenames may have been copied from a word document or something similar. Microsoft knows best, and thus tends to convert regular dashes into some weird, slightly elongated version. If you copy that to a filename, and then move that file to a *nix host, you get strange stuff. It's all for your own good, apparently. On Mon, Mar 15, 2010 at 11:08 PM, Néstor rot...@gmail.com wrote: I have an sql file that I dump(mysqldump) and then I installed on a new system and some how the dashes on the file were changed to some wierd character. When I look at the sql file in my windows machine using PUTTY I get stuff like 1.01.A â the second When I look at the same file from my linux machine via ssh -y the I get stuff like 1.01.A – the second All I know is that this wierd character original was a dash (-) How can I search for this character and convert it to a dash? Thanks, Nestor :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Innodb buffer pool usage
Hi Carlos (and all) I had a look at the script that we use an the following is the calculations that is used to calculate the innodb buffer usage. Machiel -Original Message- From: Carlos Proal [mailto:carlos.pr...@gmail.com] Sent: 16 March 2010 9:31 AM To: mysql@lists.mysql.com Subject: Re: Innodb buffer pool usage Hi Machiel, What do you mean with innodb buffer pool is at 100% full ? There are several status variables associated with innodb buffer pool ie: Innodb_buffer_pool_pages_free is the number of unused data pages. Innodb_buffer_pool_pages_total is the total number of pages. Innodb_buffer_pool_pages_data is the total number of used data pages (clean and dirty). Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of clean data pages can be calculated from these first two status variables. etc.. You can calculate the usage ratio with a basic recipe: Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total This is the value that you have to monitor. If its close to 1, then you set up too much memory for innodb (warning: that this can be due to frequent flushed to disk, so you have to check that too); if on the other hand the ratio is too low, then you effectively are running out of resources and may need to add more memory to innodb. You can imply other things, with the other variables too. Obviously you have to do the job and review this values along the time, maybe there are some actions/effects like running reports or etl processes, you have to figure out what is going on as a whole not just the values in the formula. BTW: In Oracle is the same story, one thing is what you reserve for...and other thing is the actual usage. The latter if you are tuning Oracle manually, because one important difference in Oracle 10 and 11 is that the buffers can grow and shrink automatically (if you configure it) so you can say use the 100% memory at your convenience and Oracle can, for example, reduce the sort buffers and extend the index buffers on the fly. Obviously this also has advantages and disadvantages, but as a new DBA is good to get involved in this concepts and comparisons between dbms. Carlos Proal On 3/16/2010 12:46 AM, Machiel Richards wrote: Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel -- 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=arch...@jab.org
Re: Innodb buffer pool usage
There will be an increase in IO and a noticeable decrease in performance if the buffer pool is too small. Give it all the memory which is not needed elsewhere. If you can set it a little larger than the size of all your innodb tablespaces that would be good. Oracle is a very different animal to MySQL! Regards John On Tue, Mar 16, 2010 at 6:46 AM, Machiel Richards machi...@rdc.co.zawrote: Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel
Re: mysql RAID
http://assets.en.oreilly.com/1/event/27/Linux%20Filesystem%20Performance%20for%20Databases%20Presentation.pdf 2010/3/10 John G. Heim jh...@math.wisc.edu Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Re: mysql RAID
What are your plans for OS as this can have an impact on hardware choice especially if you are considering Solaris or Open Solaris. Regards John On Tue, Mar 16, 2010 at 3:22 PM, Евгений Килимчук ekilimc...@gmail.comwrote: http://assets.en.oreilly.com/1/event/27/Linux%20Filesystem%20Performance%20for%20Databases%20Presentation.pdf 2010/3/10 John G. Heim jh...@math.wisc.edu Hi, I am working on configuring a new hardware database server. I'm a little confused as to what to do about disk. We have several mysql databases but by far the 2 most active are spamassassin bayesian rules and horde3/imp web mail. Both do a lot of updates. The bayesian rules are added to each time a spam message comes in for any of our 200 users. And the horde3/imp writes address book updates and preferences quite often. I have read (and have been told) to stay away from RAID-5 for update-intensive systems. Are there performance concerns with RAID-10 as well? We will be buying from Dell (done deal for reasons too complicated to go into) and the disks they're selling are 146 Gb. I can get up to 8 of them in the server we're buying. I asked them about just getting 2 big disks and going with RAID-1. My understanding is that with RAID-10, the system can do multiple reads and writes simultaneously so throughput is improved oversystems w/o RAID or with RAID-1. But the same logic would apply to RAID-5 only it doesn't work out that way. I just want to make sure I'm configuring this system correctly before I order it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Need help with query optimization
I have the following query: SELECT TAP.ID, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID=TAP.CurrentMemberID JOIN Chapters C On C.ID=M.ChapterID JOIN Schools S On S.ID=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.ID WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: dash was converted o a wierd character
On Tue, Mar 16, 2010 at 5:06 AM, Johan De Meersman vegiv...@tuxera.be wrote: On *nix, look for a utility called convmv. I've got a hunch that your original file comes from a windows host, and the filenames may have been copied from a word document or something similar. Microsoft knows best, and thus tends to convert regular dashes into some weird, slightly elongated version. If you copy that to a filename, and then move that file to a *nix host, you get strange stuff. It's all for your own good, apparently. That is exactly the phenomenon I was referring to.and I run into it again and again. Here is a copy of the table explaining the details of those characters. It should inspire some ideas on how to address these in a manner appropriate to your environment. glyph Unicode HTMLHTML/XMLTeX Windows Char Codes figure dash - U+2012 (8210) none#x2012; or #8210; none en dash - U+2013 (8211) ndash; #x2013; or #8211; -- ALT + 0150 em dash -- U+2014 (8212) mdash; #x2014; or #8212; --- ALT + 0151 horizontal bar -- U+2015 (8213) none#x2015; or #8213; none swung dash ~ U+2053 (8275) none#x2053; or #8275; none -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trancate table from command line?
Is there a way to use mysqladmin (or mysql) to truncate a table as a one-off command from the command line? I have an issue with importing data from one database into another, but the second database might have columns that the first did not. On database 1, I use mysqldump to grab certain tables, and when I try to simply overlay those tables onto the new database, I get an error ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1 So we're thinking we should truncate the table first, but it appears this must be done manually in the mysql shell, which won't work... this operation needs to be scripted, or to be presented as one command line that can be copy-and-pasted. How can I do something like : mysqladmin -h db_server -u user -pPASSWORD DATABASE truncate table table1 ? -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trancate table from command line?
On Tue, Mar 16, 2010 at 02:12:42PM -0700, John Oliver wrote: Is there a way to use mysqladmin (or mysql) to truncate a table as a one-off command from the command line? I have an issue with importing data from one database into another, but the second database might have columns that the first did not. On database 1, I use mysqldump to grab certain tables, and when I try to simply overlay those tables onto the new database, I get an error ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1 So we're thinking we should truncate the table first, but it appears this must be done manually in the mysql shell, which won't work... this operation needs to be scripted, or to be presented as one command line that can be copy-and-pasted. How can I do something like : mysqladmin -h db_server -u user -pPASSWORD DATABASE truncate table table1 ? Just to leave an answer behind for the next character who runs into this... for i in table1 table2 table3 ; do mysql -h db_server -u user -pPASSWORD -e truncate table $i DATABASE; done -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need help with query optimization
Can you please show us the indexes on both the tables. regards anandkl On Tue, Mar 16, 2010 at 11:47 PM, Jesse j...@msdlg.com wrote: I have the following query: SELECT TAP.ID http://tap.id/, M.UID, TAP.FirstName, TAP.MI, TAP.LastName, TAP.State, TAP.SchoolName, TAP.StateApproved, TAP.DiplomatApproved, C.ChapterType, S.Region, S.District,Pts.TotPoints FROM TorchAwardParticipants TAP JOIN Members M On M.ID http://m.id/=TAP.CurrentMemberID JOIN Chapters C On C.ID http://c.id/=M.ChapterID JOIN Schools S On S.ID http://s.id/=C.SchoolID JOIN (SELECT AchievementID,Sum(Points) As TotPoints FROM TorchAwardSelAct TASA WHERE LocalApproveStatus='A' GROUP BY AchievementID) Pts ON Pts.AchievementID=TAP.IDhttp://tap.id/ WHERE TAP.State='OH' AND TAP.Sub='1' AND TAP.SubmittedToDiplomat IS NOT NULL ORDER BY TAP.LastName, TAP.FirstName, Pts.TotPoints The TorchAwardParticipants table has about 84,000 records in it. The query takes almost 40 seconds to return the data, which is only 51 rows. An EXPLAIN returns the following: ++-+++-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+++-+---+-+-++-+ | 1 | PRIMARY | derived2 | ALL| NULL| NULL | NULL| NULL| 4382 | Using temporary; Using filesort | | 1 | PRIMARY | TAP| eq_ref | PRIMARY,CurrentMemberID | PRIMARY | 4 | Pts.AchievementID | 1 | Using where | | 1 | PRIMARY | M | eq_ref | PRIMARY,IX_Members_3| PRIMARY | 4 | bpa.TAP.CurrentMemberID | 1 | | | 1 | PRIMARY | C | eq_ref | PRIMARY,IX_Chapters_1 | PRIMARY | 4 | bpa.M.ChapterID | 1 | | | 1 | PRIMARY | S | eq_ref | PRIMARY | PRIMARY | 4 | bpa.C.SchoolID | 1 | | | 2 | DERIVED | TASA | index | NULL| AchievementID | 5 | NULL| 161685 | Using where | ++-+++-+---+-+-++-+ What is the best way to optimize this query so that it doesn't take 40 seconds to return the dataset? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
MySQL Community Server 5.1.45 has been released
Dear MySQL users, MySQL Community Server 5.1.45, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.45 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.45 on new servers or upgrading to MySQL 5.1.45 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-45.html Enjoy! === C.1.1. Changes in MySQL 5.1.45 InnoDB Plugin Notes: * This release includes InnoDB Plugin 1.0.6. This version is considered of Release Candidate (RC) quality. In this release, the InnoDB Plugin is included in source and binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64, ia64), and generic Linux RPM packages. It also does not work for FreeBSD 6 and HP-UX or for Linux on generic ia64. Functionality added or changed: * mysqltest has a new --max-connections option to set a higher number of maximum allowed server connections than the default 128. This option can also be passed via mysql-test-run.pl. (Bug#51135: http://bugs.mysql.com/bug.php?id=51135) * mysql-test-run.pl has a new --portbase option and a corresponding MTR_PORT_BASE environment variable for setting the port range, as an alternative to the existing --build-thread option. (Bug#50182: http://bugs.mysql.com/bug.php?id=50182) * mysql-test-run.pl has a new --gprof option that runs the server through the gprof profiler, much the same way the currently supported --gcov option runs it through gcov. (Bug#49345: http://bugs.mysql.com/bug.php?id=49345) * mysqltest has a new lowercase_result command that converts the output of the next statement to lowercase. This is useful for test cases where the lettercase may vary between platforms. (Bug#48863: http://bugs.mysql.com/bug.php?id=48863) * mysqltest has a new remove_files_wildcard command that removes files matching a pattern from a directory. (Bug#39774: http://bugs.mysql.com/bug.php?id=39774) Bugs fixed: * Partitioning: Attempting to drop a partitioned table from one connection while waiting for the completion of an ALTER TABLE that had been issued from a different connection, and that changed the storage engine used by the table, could cause the server to crash. (Bug#42438: http://bugs.mysql.com/bug.php?id=42438) * Replication: Adding an index to a table on the master caused the slave to stop logging slow queries to the slow query log. (Bug#50620: http://bugs.mysql.com/bug.php?id=50620) * Replication: Queries which were written to the slow query log on the master were not written to the slow query log on the slave. (Bug#23300: http://bugs.mysql.com/bug.php?id=23300) See also Bug#48632: http://bugs.mysql.com/bug.php?id=48632. * mysqld_multi failed due to a syntax error in the script. (Bug#51468: http://bugs.mysql.com/bug.php?id=51468) * Referring to a subquery result in a HAVING clause could produce incorrect results. (Bug#50995: http://bugs.mysql.com/bug.php?id=50995) * Use of filesort plus the join cache normally is preferred to a full index scan. But it was used even if the index is clustered, in which case, the clustered index scan can be faster. (Bug#50843: http://bugs.mysql.com/bug.php?id=50843) * For debug builds, SHOW BINARY LOGS caused an assertion to be raised if binary logging was not enabled. (Bug#50780: http://bugs.mysql.com/bug.php?id=50780) * Incorrect handling of BIT columns in temporary tables could lead to spurious duplicate-key errors. (Bug#50591: http://bugs.mysql.com/bug.php?id=50591) * Full-text queries that used the truncation operator (*) could enter an infinite loop. (Bug#50351: http://bugs.mysql.com/bug.php?id=50351) * mysqltest no longer lets you
Mysql Performence config ?
Hi i use MySQL Server 5.1.42 on a IBM Xseries x3950 (32Go,4xDual Core,Mandriva 64 Bits, 250 GoRAID SAS). I search the best my.cnf configuration for performence, actually i use : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 log-bin=mysql-bin binlog_format=mixed server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout On this serveur, i have 3 database with a table of ~30 000 000 of entry for ~3.5Go Thanks for your help Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trancate table from command line?
If you use *nix, you can use this form: *echo* TRUNCATE TABLE you_table_name; | *mysql* -A your_db_name -ulogin -ppassword And: *cat* your_file.sql | *mysql* -A your_db_name -ulogin -ppassword 2010/3/17 John Oliver joli...@john-oliver.net Is there a way to use mysqladmin (or mysql) to truncate a table as a one-off command from the command line? I have an issue with importing data from one database into another, but the second database might have columns that the first did not. On database 1, I use mysqldump to grab certain tables, and when I try to simply overlay those tables onto the new database, I get an error ERROR 1062 (23000) at line 24: Duplicate entry '378-4' for key 1 So we're thinking we should truncate the table first, but it appears this must be done manually in the mysql shell, which won't work... this operation needs to be scripted, or to be presented as one command line that can be copy-and-pasted. How can I do something like : mysqladmin -h db_server -u user -pPASSWORD DATABASE truncate table table1 ? -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
