Re: Event feature already working in Server 5.1.37
Daevid, all, Daevid Vincent wrote: I don't get it... I mean, I get the concept -- it's a crontab; but why would someone opt to put these events here instead of in the God-given CRONTAB as everything else in the system uses? This just seems like one more place to forget about a query/code and have unexpected things happen. There's more than one way to do it. (Perl slogan, I didn't look up the author) We already have a plethora of 'cron-like' tools: * [[...]] Agreed - but this is Unix/Linux only (not Windows), and this multitude doesn't make things easier for people working on several systems. IMNSHO, the question is whether you are viewing some to-be-scheduled task an aspect of the system as a whole or rather as a database aspect. In the first case, use your system scheduler, like cron; in the latter case, it makes sense to handle it internal to the database. The advantage of scheduling database tasks in the database is that this allows database backups and migrations to include it. If you handle that scheduling via cron (or some other scheduler), you need to handle it as a separate thing in backups and migrations. In addition, Dan's points are of course valid ones. Unless I'm missing some killer functionality this provides (and from that URL, I'm not seeing any), then I wish the Sun/mySQL team would have spent their precious time on more pressing features and or bug-fixes [[...]] My answer above is to explain why this is seen useful by several people, not to claim any relative priority of this and other changes. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
On Mon, Jan 25, 2010 at 10:08 PM, Yong Lee yong@gogoants.com wrote: yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key Only mostly true :-) It *is* the same for MyISAM, but for InnoDB the primary key is special, as that is the one that stores the data inline (clustered index). Additional unique keys will only contain a reference to the primary key value for the record. -- 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: Event feature already working in Server 5.1.37
On Tue, Jan 26, 2010 at 12:21 AM, Daevid Vincent dae...@daevid.com wrote: * at * /etc/crontab * /var/spool/crontabs/root * /var/spool/crontabs/joeblow * /etc/cron.d/ * /etc/cron.daily/ * /etc/cron.hourly/ * /etc/cron.monthly/ That entire list represents exactly two tools: at and crontab. Allright, three if you wanna be anal about it: anacron. At and Cron are also different tools: at does one-time scheduling, while cron handled repeated scheduling. Unless I'm missing some killer functionality this provides (and from that URL, I'm not seeing any), then I wish the Sun/mySQL team would have spent their precious time on more pressing features and or bug-fixes such as this one that is now FOUR YEARS old... (that is erroneously marked as 'feature request'!) Except for the very very very useful is automagically included in backup (and possibly replication) that Joerg already mentioned (that was the second thing I thought of), there is also the ability to schedule by the second, whereas cron can only schedule by the minute, and at doesn't handle repeating events. Yes, I can definitely see the use in this. If you can't, then just ignore it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Fwd: auto_increment without primary key in innodb?
Yeah, Paul... This is so clear...the auto_increment column may be indexed like: - KEY(); - UNIQUE(); - PRIMARY KEY() ...when you create or alter a table. -- Wagner Bianchi 2010/1/25 Paul DuBois paul.dub...@sun.com The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: optimization
From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization
Get yourself a copy of the book High Performance MySQL 2nd Edition Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English ISBN-10: 0596101716 ISBN-13: 978-0596101718 Here is a brief preview of the first edition: http://books.google.ca/books?id=iaCCQ13_zMICprintsec=frontcoverdq=high+performance+mysqlcd=1#v=onepageq=f=false Mike At 10:19 AM 1/26/2010, John G. Heim wrote: From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
impact of altering ft_min_word_len
Hello, we want to set the global variable ft_min_word_len from the default four to the value three. This is needed because we want to search for words with the max. length of three characters in one application. I've read that after setting ft_min_word_len to the new value, a REPAIR TABLE tablename QUICK; is required. Now the question: The MySQL holds more than one database but I only want to take affect the modification to one special database. Is there a problem with indizes in other databases or can I ensure that the affect only regards to one database? Regards Spiker -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization
♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really need to run two different types of tables. If I were you, I would use InnoDB exclusively unless there is legitimate reason to do otherwise. In an environment that is running 25% writes and a decent query rate you are bound to have contention issues with MyISAM. While there are always going to be edge cases for MyISAM, your default should be innodb and your config should reflect this. Changing your tables to InnoDB is a simple ALTER TABLE which you can script if there are a number of tables to convert. Allocate as much of your available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of total RAM) and I bet you would see a dramatic difference. That is simplifying things somewhat, but should give an idea. keith On Tue, Jan 26, 2010 at 11:53 AM, mos mo...@fastmail.fm wrote: Get yourself a copy of the book High Performance MySQL 2nd Edition Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English ISBN-10: 0596101716 ISBN-13: 978-0596101718 Here is a brief preview of the first edition: http://books.google.ca/books?id=iaCCQ13_zMICprintsec=frontcoverdq=high+performance+mysqlcd=1#v=onepageq=f=false Mike At 10:19 AM 1/26/2010, John G. Heim wrote: From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877
MySQL University session on January 28: Introduction to the Drizzle Microkernel
Introduction to the Drizzle Microkernel http://forge.mysql.com/wiki/Introduction_to_the_Drizzle_Microkernel This Thursday (January 28th, 17:00 UTC), we'll be resuming our MySQL University sessions with an Introduction to the Drizzle Microkernel by Brian Aker. Brian is located on the US West coast, so the session will start a bit later – see the information in the list of upcoming sessions (http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions). Brian started the Drizzle project and is the mastermind behind Drizzle development. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks: Here's the tentative list of upcoming sessions: * February 4: Optimizing Queries With Explain (Morgan Tocker) * February 11: MySQL Galera - Multi-Master Replication (Seppo Jaakola Alex Yurchenko) * February 18: Performance Schema: Instrumenting Code (Marc Alff) * February 25: Securich - Security Plugin for MySQL (Darren Cassar) * March 4: MySQL Column Databases (Robin Schumacher) * March 11: Improving MySQL Full-Text Search (Kristofer Pettersson) By the way, did I mention that we need more speakers to fill up the 2010 schedule? If you'd like to be a speaker, have a look at this blog post: http://blogs.sun.com/mysqlf/entry/mysql_university_speakers_wanted1 Cheers, Stefan -- Stefan Hinz stefan.h...@sun.com, Documentation Manager MySQL Phone: +49-30-82702940, Fax: +49-30-82702941, http://dev.mysql.com/doc Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
Philipp Maske [Location Bretagne] wrote: Hi, I just wanted to remark, that the Event feature is already working in server version 5.1.37 (installed on Debian). In tech resources is mentioned that this feature would be available since version 5.1.6 (see http://dev.mysql.com/tech-resources/articles/event-feature.html). So I wanted to give up using it- but fortunately I tried it on my 5.1.37 server and it works fine.. So I you have a MySQL server version prior 5.1.6 an need the Event feature - I suggest just give it a try . Perhaps you are confused by the numbering sequence? Version 5.1.37 is 29 releases *later* than 5.1.6 . Those versions prior to 5.1.6 (the ones that would not have this feature) would be 5.1.0, 5.1.1, 5.1.2, 5.1.3, 5.1.4, and 5.1.5 . Sorry for the confusion. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: impact of altering ft_min_word_len
spikerl...@gmx.de wrote: Hello, we want to set the global variable ft_min_word_len from the default four to the value three. This is needed because we want to search for words with the max. length of three characters in one application. I've read that after setting ft_min_word_len to the new value, a REPAIR TABLE tablename QUICK; is required. Now the question: The MySQL holds more than one database but I only want to take affect the modification to one special database. Is there a problem with indizes in other databases or can I ensure that the affect only regards to one database? Regards Spiker Once you change the Global setting, it affects all fulltext indexes. You will need to do the same REPAIR TABLE command on all tables currently using a fulltext index to keep the index and the setting in sync. Failure to do so can cause problems (crashes and corruptions) if you continue to use an index created with a different ft_min_word_len than the server currently has. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SCALING INSERT
Krishna Chandra Prajapati wrote: Hi shawn, As the data grows to 20 millions the insert rate will become very slow. In such case i am getting 2000 insert/seconds only. Therefore my objective is not achieved. I cannot slow up the insert rate of 10,000/second. I am getting data (inserted by users at this rate) Is there any other way to do so. (distributed servers) As mentioned in http://dev.mysql.com/doc/refman/5.1/en/optimization.html Many things can affect the speed at which you can import data: * The actual size and data types of the rows you are inserting. * The storage engine you are using * The method you use to insert the rows (INSERT vs. INSERT (extended format) vs LOAD DATA INFILE ...) * The number of and types of indexes on the table you are inserting into * The CPU or disk contention from other processes on the same machine. * The speed of your network and or storage devices * The use of DRBD ... Full treatment of all of those factors is beyond the level of assistance I am permitted to supply in these forums. However if you care to ask a specific question or two I may be able to bend the rules a little. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need an extra pair of eyes
I have a query that is giving me fits, but I can't find where there error is. I know its one of those things that I'll slap myself in the forehead for not seeing, but after a half an hour, it's all a blur. ;) If someone can spot it, I'd be most grateful. REPLACE INTO product_dimensions SET `productid` = '173846', `variantid` = '73130', `type` = 's', `height` = '3.7500', `width` = '3.5000', `depth` = '5.3300' WHERE `productid` = 173846 `variantid` = 73130 `type` = 's' I've also tried it this way REPLACE INTO product_dimensions ( `productid`, `variantid`, `type`, `height`, `width`, `depth` ) VALUES ( '173846', '73130', 's', '3.7500', '3.5000', '5.3300' ) WHERE `productid` = 173846 `variantid` = 73130 `type` = 's' And the error for both is the same: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `productid` = 173846 `variantid` = 73130 `type` = 's'' at line 1 -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.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: Need an extra pair of eyes
Please forgive my ignorance. Going from update to replace and left the 'where' part of the statement. Definitely a DOH! moment. -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.com -Original Message- From: John Nichel [mailto:jnic...@kegworks.com] Sent: Tuesday, January 26, 2010 12:54 PM To: mysql@lists.mysql.com Subject: Need an extra pair of eyes I have a query that is giving me fits, but I can't find where there error is. I know its one of those things that I'll slap myself in the forehead for not seeing, but after a half an hour, it's all a blur. ;) If someone can spot it, I'd be most grateful. REPLACE INTO product_dimensions SET `productid` = '173846', `variantid` = '73130', `type` = 's', `height` = '3.7500', `width` = '3.5000', `depth` = '5.3300' WHERE `productid` = 173846 `variantid` = 73130 `type` = 's' I've also tried it this way REPLACE INTO product_dimensions ( `productid`, `variantid`, `type`, `height`, `width`, `depth` ) VALUES ( '173846', '73130', 's', '3.7500', '3.5000', '5.3300' ) WHERE `productid` = 173846 `variantid` = 73130 `type` = 's' And the error for both is the same: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `productid` = 173846 `variantid` = 73130 `type` = 's'' at line 1 -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=j...@kegworks.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: optimization
Just to be clear, you're suggesting I convert all of the spamassassin, drupal, and mediawiki tables to innodb too? Or just my own database? What about the mysql database itself? I wouldn't convert those tables, would I? - Original Message - From: Keith Murphy bmur...@paragon-cs.com To: mysql@lists.mysql.com Sent: Tuesday, January 26, 2010 11:06 AM Subject: Re: optimization ♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really need to run two different types of tables. If I were you, I would use InnoDB exclusively unless there is legitimate reason to do otherwise. In an environment that is running 25% writes and a decent query rate you are bound to have contention issues with MyISAM. While there are always going to be edge cases for MyISAM, your default should be innodb and your config should reflect this. Changing your tables to InnoDB is a simple ALTER TABLE which you can script if there are a number of tables to convert. Allocate as much of your available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of total RAM) and I bet you would see a dramatic difference. That is simplifying things somewhat, but should give an idea. keith On Tue, Jan 26, 2010 at 11:53 AM, mos mo...@fastmail.fm wrote: Get yourself a copy of the book High Performance MySQL 2nd Edition Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English ISBN-10: 0596101716 ISBN-13: 978-0596101718 Here is a brief preview of the first edition: http://books.google.ca/books?id=iaCCQ13_zMICprintsec=frontcoverdq=high+performance+mysqlcd=1#v=onepageq=f=false Mike At 10:19 AM 1/26/2010, John G. Heim wrote: From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization
You absolutely *should not* convert the mysql database to InnoDB. Read the above sentence again :) All others, unless you had a specific reason not to do so, yes, I would convert them. keith On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim jh...@math.wisc.edu wrote: Just to be clear, you're suggesting I convert all of the spamassassin, drupal, and mediawiki tables to innodb too? Or just my own database? What about the mysql database itself? I wouldn't convert those tables, would I? - Original Message - From: Keith Murphy bmur...@paragon-cs.com To: mysql@lists.mysql.com Sent: Tuesday, January 26, 2010 11:06 AM Subject: Re: optimization ♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really need to run two different types of tables. If I were you, I would use InnoDB exclusively unless there is legitimate reason to do otherwise. In an environment that is running 25% writes and a decent query rate you are bound to have contention issues with MyISAM. While there are always going to be edge cases for MyISAM, your default should be innodb and your config should reflect this. Changing your tables to InnoDB is a simple ALTER TABLE which you can script if there are a number of tables to convert. Allocate as much of your available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of total RAM) and I bet you would see a dramatic difference. That is simplifying things somewhat, but should give an idea. keith On Tue, Jan 26, 2010 at 11:53 AM, mos mo...@fastmail.fm wrote: Get yourself a copy of the book High Performance MySQL 2nd Edition Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English ISBN-10: 0596101716 ISBN-13: 978-0596101718 Here is a brief preview of the first edition: http://books.google.ca/books?id=iaCCQ13_zMICprintsec=frontcoverdq=high+performance+mysqlcd=1#v=onepageq=f=false Mike At 10:19 AM 1/26/2010, John G. Heim wrote: From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877
RE: Event feature already working in Server 5.1.37
-Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent For a hosted environment (or a restricted corporate environment), it means you don't have to give your users shell accounts; they can schedule table cleanup operations, summary table refreshes, and other operations completely within MySQL. Fair enough. I can't imagine a host these days that doesn't give you a dedicated VM sandbox though. There are hosts out there that put all the fish in the same pond?! Wow. I certainly wouldn't trust that scenario with my data/site. The events will also fire the same whether the server is running Unix or Windows. People really use Windows for a mySQL server? Weird. It just seems so clunky. Don't get me wrong. I love XP. I run it on all my 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Event feature already working in Server 5.1.37
-Original Message- From: Daevid Vincent dae...@daevid.com Sent: 26 January 2010 21:50 To: dnel...@allantgroup.com Cc: mysql@lists.mysql.com Subject: RE: Event feature already working in Server 5.1.37 People really use Windows for a mySQL server? Weird. I'm seem to remember reading somewhere that Windows is currently the most popular platform for MySQL! It just seems so clunky. Don't get me wrong. I love XP. I run it on all my 12+ notebooks, netbooks, desktops, etc. But for any LAMP dev work, I either have a dedicated Linux box setup or a Virtual Machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk === John Daisley MySQL 5.0 Certified Database Administrator (CMDBA) MySQL 5.0 Certified Developer Cognos BI Developer Telephone: +44(0)1283 537111 Mobile: +44(0)7812 451238 Email: john.dais...@butterflysystems.co.uk === Sent via HP IPAQ mobile device -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Event feature already working in Server 5.1.37
Daevid Vincent wrote: -Original Message- From: Dan Nelson [mailto:dnel...@allantgroup.com] Sent: Monday, January 25, 2010 11:42 PM To: Daevid Vincent ...snipped ... People really use Windows for a mySQL server? Weird. ... Yes, they do. Not only is MySQL as cheap as the free version of MS SQL but it doesn't suffer from the hard limits the free version of MS SQL imposes and it works across all of your servers, regardless of platform. MS products are limited to Windows boxes. You cannot assemble a new Linux box and get MS-anything to run on it natively. With C, C++, .NET, JAVA, and ODBC connection options available, it's very easy to make a connection to MySQL from practically any MS development language. Some connectors will even integrate themselves into Visual Studio. Windows, as foreign as it may seem, is actually a very viable MySQL development platform. I encourage you to try it out and let us know what you think. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimization
On Tue, Jan 26, 2010 at 11:23 AM, Keith Murphy bmur...@paragon-cs.com wrote: You absolutely *should not* convert the mysql database to InnoDB. Read the above sentence again :) All others, unless you had a specific reason not to do so, yes, I would convert them. keith On Tue, Jan 26, 2010 at 2:18 PM, John G. Heim jh...@math.wisc.edu wrote: Just to be clear, you're suggesting I convert all of the spamassassin, drupal, and mediawiki tables to innodb too? Or just my own database? What about the mysql database itself? I wouldn't convert those tables, would I? - Original Message - From: Keith Murphy bmur...@paragon-cs.com To: mysql@lists.mysql.com Sent: Tuesday, January 26, 2010 11:06 AM Subject: Re: optimization ♫ I would recommend the same to you about reading High Perf. MySQL as Baron, et al wrote a great book about performance on MySQL. That being said, it has been my experience that in 99% of client cases they don't really need to run two different types of tables. If I were you, I would use InnoDB exclusively unless there is legitimate reason to do otherwise. In an environment that is running 25% writes and a decent query rate you are bound to have contention issues with MyISAM. While there are always going to be edge cases for MyISAM, your default should be innodb and your config should reflect this. Changing your tables to InnoDB is a simple ALTER TABLE which you can script if there are a number of tables to convert. Allocate as much of your available RAM as possible to the innodb_buffer_pool (typically 50 - 80% of total RAM) and I bet you would see a dramatic difference. That is simplifying things somewhat, but should give an idea. keith On Tue, Jan 26, 2010 at 11:53 AM, mos mo...@fastmail.fm wrote: Get yourself a copy of the book High Performance MySQL 2nd Edition Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English ISBN-10: 0596101716 ISBN-13: 978-0596101718 Here is a brief preview of the first edition: http://books.google.ca/books?id=iaCCQ13_zMICprintsec=frontcoverdq=high+performance+mysqlcd=1#v=onepageq=f=false Mike At 10:19 AM 1/26/2010, John G. Heim wrote: From: Jaime Crespo Rincón jcre...@warp.es Sent: Monday, January 25, 2010 5:30 PM 2010/1/25 John G. Heim jh...@math.wisc.edu: I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is running the latest mysql-server from debian lenny (5.0.1). I have databases for drupal, moodle, spamassassin, horde3, and a small database for departmental stuff. The problem is that inserts/updates are sometimes very slow, on the order of a minute. I am hoping somebody can sspot something wrong in my config. Here's the optimization settings section (for your convenience). The whole my.cnf is reproduced below that: Are your databases using MyISAM or InnoDB? Both. Maybe that's the problem? I started creating database tables for my own web apps with the default mysql configuration. I believe the default database engine is MyISAM. But then I wanted to use foreign keys and I saw that it required me to use InnoDB. So I converted some tables to InnoDB but not all. Maybe it was a mistake not to convert all of them. After that, I installed drupal, moodle, and mediawiki. I haven't looked at what kind of tables those packages create. They may not specify it and the default is still whatever it is when you install mysql, MyISAM I think. * If MyISAM, you could be suffering contention problems on writes because of full table locks. No easy solution but engine change or database sharding. Also key_buffer, (and the other buffers) coud be too small for 16GB of RAM. Are you really using more thant 10% of it? You could also disable other engines if unused. * If InnoDB, you have not set innodb_buffer_pool_size nor log size. You could increase the pool to 50% of ram available. Those are very general suggestions. It depends a lot on your hardware (slow storage?), other apps installed on the same machine or the load of the server, among others. Well, it could be that the disks aren't real fast. The server is also running a print server (lprng). I don't think that's very CPU intensive but it might be slowing down writes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bmur...@paragon-cs.com -- Chief Training Officer Paragon Consulting Services 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
