Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Francisco Rodrigo Cortinas Maseda wrote: Hi, I have experienced similar problems to the one you have; the problem you have is that the time gap between the failure and now is so big that you cannot resume replication, because of the big data portion you have to replicate. Well, that was yesterday - so I guess I could. I see the binlog ids where replication stopped and resumed. They match every time. How would I find out that replication resumed at a wrong position? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimal MySQL server -- opinions?
Hi, Three things... 1. You need to let us know what the DB server will be doing. Many CPU cores are only important of you have many CPU intensive MySQL connections in parallel. Will you have a read-intensive or write-intensive database load? Those 2950III you're considering can take up to 8 disks. If you're doing very read-intensive operations, you may want to consider a RAID1 pair for your OS, apps and MySQL tmp tables and a RAID 5 array for the MySQL data (to get the most disk space for your money without sacrificing redundancy). However; if you're doing a heavy work with lots of reads and writes at the same time then you should consider RAID1+0 for your data. RAM will always help for both MySQL caches and buffers and don't underestimate the great effect that lots of RAM for your filesystem cache will have (talking about unix/linux here, can't speak for windows). 2. All the hardware vendors have promotions running all the time which they change every month. One month it will be cheaper disk, the next month will be cut-price RAM etc... The end result will be about the same... 3. It's very easy to upgrade memory and processors as long as you don't mind 15 minutes or so of downtime for that server, linux will just see the new h/w when it comes back up. With hardware like HP and Dell you won't even need a screwdriver, it's all easy to use clips. Cheers, Andrew -Original Message- From: Rene Fournier [mailto:[EMAIL PROTECTED] Sent: Sun, 27 April 2008 22:57 To: mysql@lists.mysql.com Subject: Optimal MySQL server -- opinions? Okay, the previous subject was too narrow, what I am really looking for are opinions on general disk/memory/cpu configurations, manufacturer notwithstanding... As stated previously, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why is explain.key-len not size of key field ?
Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor
Re: Optimizing table (shall I create a primary field?)
Thank you all for your suggestions. So it's very important to make primary fields be as smaller as possible, right? I'm going to change 'isbn' from VARCHAR(100) to VARCHAR(25) and, if possible (I might change my code), from VARCHAR to BIGINT. By the way, which are optimal values for key_len parameter when doing EXPLAIN? Regards, --Charles ISBN field is way too long, I think they just changed it to 13 characters. Depending on your application leading '0' may be important so you may be stuck with a character field. As was suggested, loose the '-' and spaces. I don't think they are standard and I would think its easier to universally remove them. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
How to know the maximum length of a field
Hi. I've got a table with some fields, which I created with VARCHAR(100). I want to optimize this table, and I want to modify the length of these fields from VARCHAR(100) to VARCHAR(maximum_length), where maximum_length is the length of the record with the longest field. I could create a Perl script to do it, but I wonder if there's a mySQL command which does it automatically. Thank you very much, --Charles
Re: How to know the maximum length of a field
Hi Charles, to get max . # characters: SELECT MAX(CHAR_LENGTH(fieldname)) AS `MaxChars`; to get max. # bytes: SELECT MAX(LENGTH(fieldname)) AS `MaxBytes`; HTH, Cor - Original Message - From: Charles Lambach [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 1:21 PM Subject: How to know the maximum length of a field Hi. I've got a table with some fields, which I created with VARCHAR(100). I want to optimize this table, and I want to modify the length of these fields from VARCHAR(100) to VARCHAR(maximum_length), where maximum_length is the length of the record with the longest field. I could create a Perl script to do it, but I wonder if there's a mySQL command which does it automatically. Thank you very much, --Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Yes, I did FLUSH PRIVILEGES and I think I only have one username/host entry for this user. Adam Erik Giberti wrote: Did you FLUSH PRIVILEGES? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why is explain.key-len not size of key field ?
On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the maximum length of a field
On Tuesday 29 April 2008 08:21:37 Charles Lambach wrote: Hi. I've got a table with some fields, which I created with VARCHAR(100). I want to optimize this table, and I want to modify the length of these fields from VARCHAR(100) to VARCHAR(maximum_length), where maximum_length is the length of the record with the longest field. I could create a Perl script to do it, but I wonder if there's a mySQL command which does it automatically. Thank you very much, --Charles Couldn't you also use procedure analyse()? SELECT column FROM table PROCEDURE ANALYSE(10, 2000); kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Starting a 2nd MySQL instance on UNIX
Would you kindly supply the changes you made, for our collective education? Thanks. Arthur On Mon, Apr 28, 2008 at 11:54 AM, Mark-E [EMAIL PROTECTED] wrote: Hi Ian, Thanks for the reply. I was specifying the new port of 3307. I actually got it working over the weekend. Turns out I had to add a few entries in the mysqld section of the my.cnf file and I was able to connect. Regards, Mark
Re: why is explain.key-len not size of key field ?
- Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:24 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. Rob Wultsch [EMAIL PROTECTED] Thanks Rob, So key-len is expressed in bytes and not in characters. But that means that each normal character (a..z) is 3 bytes in UTF8 ? TIA, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why is explain.key-len not size of key field ?
On Tue, Apr 29, 2008 at 7:39 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: - Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:24 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. Rob Wultsch [EMAIL PROTECTED] Thanks Rob, So key-len is expressed in bytes and not in characters. But that means that each normal character (a..z) is 3 bytes in UTF8 ? TIA, Cor Max memory usage is 3 bytes in MySQL. I am not an expert on this subject. Take a look at the following: http://en.wikipedia.org/wiki/UTF-8 http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why is explain.key-len not size of key field ?
- Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:44 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 7:39 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: - Original Message - From: Rob Wultsch [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 29, 2008 2:24 PM Subject: Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I have a dictionary table like: CREATE TABLE IF NOT EXISTS `mydictionary` ( `EN` varchar(36) default NULL, `DE` varchar(36) default NULL, `ES` varchar(36) default NULL, `FR` varchar(36) default NULL, `IT` varchar(36) default NULL, `NL` varchar(36) default NULL, `PT` varchar(36) default NULL, KEY `EN` (`EN`), KEY `DE` (`DE`), KEY `ES` (`ES`), KEY `FR` (`FR`), KEY `IT` (`IT`), KEY `NL` (`NL`), KEY `PT` (`PT`) ) ENGINE=MyISAM DEFAULT CHARSET = UTF8; When I run the query: EXPLAIN SELECT `EN`, `FR` FROM `mydictionary` WHERE `FR` Like comp% ORDER BY `FR`; EXPLAIN says: select type = SIMPLE, type = range, key = FR, key-len = 111, ref = NULL, extra = using where Any idea why key-len is 111 ? Thanks, Cor Your using a multi byte character set. Rob Wultsch [EMAIL PROTECTED] Thanks Rob, So key-len is expressed in bytes and not in characters. But that means that each normal character (a..z) is 3 bytes in UTF8 ? TIA, Cor Max memory usage is 3 bytes in MySQL. I am not an expert on this subject. Take a look at the following: http://en.wikipedia.org/wiki/UTF-8 http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/ -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) The 2nd link gave the answer stating: MySQL must be pessimistic and assume the worst-case scenario of every character requiring 3 bytes. Thanks Rob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing table (shall I create a primary field?)
On Tue, Apr 29, 2008 at 5:09 AM, Charles Lambach [EMAIL PROTECTED] wrote: Thank you all for your suggestions. So it's very important to make primary fields be as smaller as possible, right? I'm going to change 'isbn' from VARCHAR(100) to VARCHAR(25) and, if possible (I might change my code), from VARCHAR to BIGINT. By the way, which are optimal values for key_len parameter when doing EXPLAIN? Regards, --Charles The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. http://dev.mysql.com/doc/refman/5.0/en/using-explain.html Note: that is length in bytes. I don't know how much faster your queries will get by reducing the varchar size, I have not had to deal with many performance issues of this type. If it is effective (you are noting query times, right?) you should be able to further reduce the size by change the character set from multi-btye utf-8 to single byte ascii. The query would be something like: ALTER TABLE `books` CHANGE `isbn` `isbn` VARCHAR( 25) CHARACTER SET ascii NOT NULL -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Starting a 2nd MySQL instance on UNIX
Hi Arthur, Sure, no problem! This actually proved to be rather tricky because the first instance was installed in /usr/local/mysql. It seems that by default, MySQL looks for things in this locations so if you deviate from it, you have to be explicit with mysql and tell it where to look for things. My new MySQL 50 instance had to run alongside the 4.0.20 instance so I installed it into /usr/local/mysql-50 ** Here is a summary of things I had to do to bring this up properly ** I created a my.cnf and put it in /usr/local/mysql-50 In the my.cnf, I defined the following (The first entry is for the mysql program and the 2nd entry is for the mysqld program). [mysql] socket=/tmp/mysql50/mysql.sock port=3307 [mysqld] user=mysql5 pid-file=/usr/local/mysql-50/mysql50.pid log=/usr/local/mysql-50/mysql50d.log port=3307 max_allowed_packet=32M == I had to set this so I could import the MySQL 4.0.20 database properly. Some users may not need this set as high. socket=/tmp/mysql50/mysql.sock #Path to installation directory. All paths are usually resolved relative to this. basedir=/usr/local/mysql-50 #Path to the database root datadir=/usr/local/mysql-50/data You have to be careful to not only pick a separate port, but also a separate pid file for the process ID and a separate socket file, especially if you are running both instance at the same time. I modified the mysql.server in the /support-files folder. I had to set the basedir and the datadir. I then copied this to the /bin folder. I edited the mysql_install_db file in the /scripts folder and set the basedir and datadir. I then ran that to create the mysql database. As root, I started the server ./bin/mysql.server start (The server starts up :) ). Since the user mysql50 is defined in this script, it then starts mysqld as the mysql50 user. Now, the tricky part, to run mysql and access the mysql database, you cannot just say mysql -u root -p mysql, it will try to connect to the default instance in /usr/local/mysql. What you have to do is: Mysql --defaults-file=/usr/local/mysql-50/my.cnf -u root -p mysql This now properly connects to the new mysql50 instance. Also, if you want to run mysqladmin, you need to specify the --defaults-file option. Make sure wherever you use the --defaults-file option that it is the FIRST command line option used. It took me quite some time to get this all working but now, I understand MySQL much better. I hope this proves to be some help to you and others out there who may be going through the same thing. Regards, Mark From: Arthur Fuller [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 29, 2008 9:29 AM To: Eramo, Mark Cc: mysql@lists.mysql.com Subject: Re: Starting a 2nd MySQL instance on UNIX Would you kindly supply the changes you made, for our collective education? Thanks. Arthur On Mon, Apr 28, 2008 at 11:54 AM, Mark-E [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] wrote: Hi Ian, Thanks for the reply. I was specifying the new port of 3307. I actually got it working over the weekend. Turns out I had to add a few entries in the mysqld section of the my.cnf file and I was able to connect. Regards, Mark
R: Starting a 2nd MySQL instance on UNIX
Hello, I faced this issues a few years ago and I'd like to give my contributions. The easy and clean way I've found: -- -- One installation for each mysql instance -- On each instance you can have as many databases as you want. -- One different mysql user and homedir (mysql41,mysql50) for each mysql installation. -- Put .my.cnf file in the home directory of each mysql user with the right parameters (different ports and socket files at least, this is the way I have found not to conflict between instances) Ex: :-.my.cnf: # The MySQL server [mysqld] port= 3515 socket = /tmp/mysql5015.sock skip-locking key_buffer = 256M max_allowed_packet = 1M :-: -- Start each mysql instance with his own user -- In this way I have several MySQL server instances (from 3.23 to 5.x) wonderfully working on the same 2.7 Solaris machine. Aloha! Claudio Nanni -Messaggio originale- Da: Eramo, Mark [mailto:[EMAIL PROTECTED] Inviato: martedì 29 aprile 2008 15.54 A: Arthur Fuller Cc: mysql@lists.mysql.com Oggetto: RE: Starting a 2nd MySQL instance on UNIX Hi Arthur, Sure, no problem! This actually proved to be rather tricky because the first instance was installed in /usr/local/mysql. It seems that by default, MySQL looks for things in this locations so if you deviate from it, you have to be explicit with mysql and tell it where to look for things. My new MySQL 50 instance had to run alongside the 4.0.20 instance so I installed it into /usr/local/mysql-50 ** Here is a summary of things I had to do to bring this up properly ** I created a my.cnf and put it in /usr/local/mysql-50 In the my.cnf, I defined the following (The first entry is for the mysql program and the 2nd entry is for the mysqld program). [mysql] socket=/tmp/mysql50/mysql.sock port=3307 [mysqld] user=mysql5 pid-file=/usr/local/mysql-50/mysql50.pid log=/usr/local/mysql-50/mysql50d.log port=3307 max_allowed_packet=32M == I had to set this so I could import the MySQL 4.0.20 database properly. Some users may not need this set as high. socket=/tmp/mysql50/mysql.sock #Path to installation directory. All paths are usually resolved relative to this. basedir=/usr/local/mysql-50 #Path to the database root datadir=/usr/local/mysql-50/data You have to be careful to not only pick a separate port, but also a separate pid file for the process ID and a separate socket file, especially if you are running both instance at the same time. I modified the mysql.server in the /support-files folder. I had to set the basedir and the datadir. I then copied this to the /bin folder. I edited the mysql_install_db file in the /scripts folder and set the basedir and datadir. I then ran that to create the mysql database. As root, I started the server ./bin/mysql.server start (The server starts up :) ). Since the user mysql50 is defined in this script, it then starts mysqld as the mysql50 user. Now, the tricky part, to run mysql and access the mysql database, you cannot just say mysql -u root -p mysql, it will try to connect to the default instance in /usr/local/mysql. What you have to do is: Mysql --defaults-file=/usr/local/mysql-50/my.cnf -u root -p mysql This now properly connects to the new mysql50 instance. Also, if you want to run mysqladmin, you need to specify the --defaults-file option. Make sure wherever you use the --defaults-file option that it is the FIRST command line option used. It took me quite some time to get this all working but now, I understand MySQL much better. I hope this proves to be some help to you and others out there who may be going through the same thing. Regards, Mark From: Arthur Fuller [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 29, 2008 9:29 AM To: Eramo, Mark Cc: mysql@lists.mysql.com Subject: Re: Starting a 2nd MySQL instance on UNIX Would you kindly supply the changes you made, for our collective education? Thanks. Arthur On Mon, Apr 28, 2008 at 11:54 AM, Mark-E [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] wrote: Hi Ian, Thanks for the reply. I was specifying the new port of 3307. I actually got it working over the weekend. Turns out I had to add a few entries in the mysqld section of the my.cnf file and I was able to connect. Regards, Mark Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto
mysql-proxy losting connection
Hi! I'm using phpBB3 directly connected on my mysql-proxy: // phpBB 3.0.x auto-generated configuration file $dbms = 'mysqli'; $dbhost = '10.25.0.12'; #$dbport = '3306'; $dbport = '4040'; $dbname = 'phpbb'; $dbuser = 'phpbb'; $dbpasswd = 'phpbb'; $table_prefix = 'phpbb_'; $acm_type = 'file'; $load_extensions = ''; I'm starting on this way: [EMAIL PROTECTED] mysql-proxy-0.6.1]# LUA_PATH=lib/?.lua src/mysql-proxy --proxy-read-only-backend-addresses=10.25.0.11:3306 --proxy-backend-addresses=10.25.0.12:3306 --proxy-lua-script=lib/rw-splitting.lua Works as well for a while, but after stops with this error: = General Error SQL ERROR [ mysqli ] Lost connection to MySQL server during query [2013] An sql error occurred while fetching this page. Please contact an administrator if this problem persists. = And really, I just can't connect on the first try, but connect at second tried: $ mysql -h 10.25.0.12 -uphpbb -pphpbb -P4040 phpbb ERROR 2013 (HY000): Lost connection to MySQL server at 'reading final connect information', system error: 0 $ mysql -h 10.25.0.12 -uphpbb -pphpbb -P4040 phpbb Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3325 Server version: 5.0.22-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye = What can I do to fix this? Thanks! -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql DBA/Windows Server Administrator opportunity-Kansas City
I am not sure if this is the appropriate place to do this kind of posting but I thought this discussion group might be interested in hearing about this opportunity. I am working with a client in Kansas City that is looking for a contract to hire resource that has experience as a Mysql DBA that has some Windows Server Administration experience. Below are some details about the position. If anyone is interested in hearing more about this opportunity, please contact me at the email address or phone number below. Job description: MySQL DBA/Windows 2003 Server Admin. Responsibilities: * Must be able to respond to operational alerts 24/7. * Responsible for the daily administration of our production database servers and OS. * Assuring the security of all systems. * Installs, maintains, and upgrades database software. * Perform maintenance and upgrade of application schema components and database structures. * Maintain database backup and recovery environment; verify operation of backup strategy and data integrity. * Monitor and manage database replication and failover/recovery as needed. * Run database consistency checks and maintenance procedures and fix as needed. * Monitor and manage database server performance, availability, capacity and error logs. * Identify, troubleshoot and resolve real-time database issues. * Windows administration, security management, patching and performance monitoring. Qualifications: * On-call availability for emergencies and willingness to work non-standard hours. * A degree in Computer Science or related field. * Three or more years relevant experience including implementation, administration and support of production MySQL systems. * Two or more years of experience as both a database administrator and a Windows 2003 server administrator. * Experience in designing, implementing, testing and maintaining data replication services, as well as database backup, security and recovery strategies. * Strong familiarity with database clustering. * Experience with optimization and troubleshooting techniques to address performance problems on MySQL Server. * Proficient with Windows operating system security and utilities. * Experience with MS SQL Server 2005 or Oracle is a strong plus. * Production IIS/Web administration experience a plus. * Programming backgrounds in any .NET language a plus Lori Kurovski Solutions Now, Inc. 8695 College Blvd, Suite 220 Overland Park, KS 66210 913-422-0293 [EMAIL PROTECTED]
Practical hierarchies
Hi everybody, I would like to discuss there hierarchical storage theory. Currently I have came across 2 types for storing hierarchical data (yes, I´ve read the article at mysql.com, and MANY others sites), but I would like to know your option about the day-to-day usage... I mean I´ve only used hierarchy for some small to medium projects, but I now I am planning a big account project (yes, I´ve researched many open-source solutions, but none was suitable for an enterprise level solution), and if someone has already walked this path... Well, I would like to share and discuss... How Oracle, SAP and Microsoft Dynamics did? Some one has some insight? ow they manage to store UNSPSC taxonomy data on their systems? Here is a table using parent and child relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryParentID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryParentID `,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (` CatalogCategoryCreatedBy `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexC` (` CatalogCategoryCreatedOn `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: Easy to understand and implement the operating clas es, not export / inport friendly Cons: Recursive behavior, not suited for reports generation because of the recursivity Here is a table using transverse relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryLeftPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryRightPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (`CatalogCategoryLeftPosition`,`CatalogCategoryRightPosition`), KEY `CatalogsCategoriesIndexC` (` CatalogCategoryCreatedBy `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexD` (` CatalogCategoryCreatedOn `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: SQL friendly so much less processing and queries required, not export / inport friendly Coms: Not that easy to understand or implement the operating classes Here is a table using UNSPSC like relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryLevel1` int(8) unsigned NOT NULL default '0', `CatalogCategoryLevel2` int(8) unsigned NOT NULL default '0', `CatalogCategoryLevel3` int(8) unsigned NOT NULL default '0', `CatalogCategoryLevel4` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0',
Re: Practical hierarchies
Just correcting my self, The hierarchical storage theory is nested set model, and not transverse. And also I know that oracle 10g has a hierarchical query, but I am more interested on pure SQL2 theories and solutions. And also, SHOULD (or could) MySQL have some type or recursive query? For example: SELECT RECURSIVE * FROM ProductsCategories USING ProductCategoryParentID = ProductCategoryID Is this syntax factive? Best regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Apr 29, 2008, at 1:21 PM, Bruno B B Magalhães wrote: Hi everybody, I would like to discuss there hierarchical storage theory. Currently I have came across 2 types for storing hierarchical data (yes, I´ve read the article at mysql.com, and MANY others sites), but I would like to know your option about the day-to-day usage... I mean I´ve only used hierarchy for some small to medium projects, but I now I am planning a big account project (yes, I´ve researched many open- source solutions, but none was suitable for an enterprise level solution), and if someone has already walked this path... Well, I would like to share and discuss... How Oracle, SAP and Microsoft Dynamics did? Some one has some insight? ow they manage to store UNSPSC taxonomy data on their systems? Here is a table using parent and child relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryParentID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryParentID `,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (` CatalogCategoryCreatedBy `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexC` (` CatalogCategoryCreatedOn `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: Easy to understand and implement the operating clas es, not export / inport friendly Cons: Recursive behavior, not suited for reports generation because of the recursivity Here is a table using transverse relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryLeftPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryRightPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (` CustomerID`,`CatalogID`,`CatalogCategoryID`,`CatalogCategoryActive`), KEY
Re: Practical hierarchies
Bruno, Interesting emails. You might consider talking to Arjen Lentz about his special hierarchical-storage magic. It's still in development, but ... well, I'll let you talk with him about it. Best, Baron On Tue, Apr 29, 2008 at 2:12 PM, Bruno B B Magalhães [EMAIL PROTECTED] wrote: Just correcting my self, The hierarchical storage theory is nested set model, and not transverse. And also I know that oracle 10g has a hierarchical query, but I am more interested on pure SQL2 theories and solutions. And also, SHOULD (or could) MySQL have some type or recursive query? For example: SELECT RECURSIVE * FROM ProductsCategories USING ProductCategoryParentID = ProductCategoryID Is this syntax factive? Best regards, Bruno B. B. Magalhães BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. On Apr 29, 2008, at 1:21 PM, Bruno B B Magalhães wrote: Hi everybody, I would like to discuss there hierarchical storage theory. Currently I have came across 2 types for storing hierarchical data (yes, I´ve read the article at mysql.com, and MANY others sites), but I would like to know your option about the day-to-day usage... I mean I´ve only used hierarchy for some small to medium projects, but I now I am planning a big account project (yes, I´ve researched many open-source solutions, but none was suitable for an enterprise level solution), and if someone has already walked this path... Well, I would like to share and discuss... How Oracle, SAP and Microsoft Dynamics did? Some one has some insight? ow they manage to store UNSPSC taxonomy data on their systems? Here is a table using parent and child relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryParentID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0', KEY `CatalogsCategoriesIndexA` (`CustomerID`,`CatalogID`,`CatalogCategoryParentID `,`CatalogCategoryID`,`CatalogCategoryActive`), KEY `CatalogsCategoriesIndexB` (`CatalogCategoryCreatedBy`,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), KEY `CatalogsCategoriesIndexC` (`CatalogCategoryCreatedOn`,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Pros: Easy to understand and implement the operating clas es, not export / inport friendly Cons: Recursive behavior, not suited for reports generation because of the recursivity Here is a table using transverse relationship: CREATE TABLE `CatalogsCategories` ( `CustomerID` int(8) unsigned NOT NULL default '0', `CatalogID` int(8) unsigned NOT NULL default '0', `CatalogCategoryID` int(8) unsigned NOT NULL default '0', `CatalogCategoryLeftPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryRightPosition` int(8) unsigned NOT NULL default '0', `CatalogCategoryCode` varchar(20) NOT NULL default '', `CatalogCategoryName` varchar(200) NOT NULL default '', `CatalogCategoryDescription` text character set latin1 NOT NULL, `CatalogCategoryActive` int(1) unsigned NOT NULL default '0', `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0', `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0', `CatalogCategoryModifiedBy` int(8)
Union query
I have three tables in a survey system (that I didn't design) surveys (the instance of a user taking a survey) with survey_id and show_id (each survey is in response to a particular show) responses (possible responses to questions) with response_id response_map ( survey_id, response_id ) representing a user's answer to a survey. I'm trying to determine how many surveys per show responded with response_id 30 and response_id 28 I think I need a union here, but am not sure. So far, I THINK I can figure out how many surveys per show responded with just response 30 as follows: SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE m.response_id = 30 AND m.survey_id = s.survey_id GROUP BY s.show_id Am I right in assuming that the following would give me the count of surveys per show that had both response 30 and 28 with the following? SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE m.response_id = 30 AND m.survey_id = s.survey_id GROUP BY s.show_id UNION SELECT COUNT(m.survey_id) cnt, s.show_id FROM surveys s, response_map m WHERE m.response_id = 28 AND m.survey_id = s.survey_id GROUP BY s.show_id; Thanks in advance for any help kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Hi all, I feel obliged to report on my success with migration from 32bit to 64bit platform. Last Sunday I braced myself and migrated 3 servers (one master and two slaves) with approximately 100GB data each by simply rsyncing the data files. It took about 1 hour total downtime. Everything looks great so far. I ran lots of tests, especially on currency columns and all tests were successfull. It is fair to note that I don't have any FLOAT columns in my databases. I have mixed table environment (MyISAM and InnoDB tables). Running MySQL 4.1.24, Linux binaries. Let me know if you have any questions. Mihail On Apr 25, 2008, at 12:48 PM, Mihail Manolov wrote: I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: UPDATEs against one db affecting others
I'll do that, thanks. (Sorry for the long response time, I was in Dublin this past week...). On Mon, Apr 21, 2008 at 3:16 PM, Wm Mussatto [EMAIL PROTECTED] wrote: On Mon, April 21, 2008 07:28, Waynn Lue wrote: Is there any way to prevent UPDATEs on one database on one mysql instance from affecting SELECTs on other databases in the same instance? I'm noticing a whole bunch of backed up connections on other databases when I run massive UPDATEs on one. Thanks! Are there any I/O or CPU load issues? If you don't have any stored procedures that work between them, I'd check server level limits. MySQL is supposed to be multi-threaded to avoid just this sort of problem, but if your disk and/or CPU are maxed out (or for that matter you are out of memory) the whole system may be tied up. Just my thoughts warning its a Monday here. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- 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]
mysql variables
Hi, Key_blocks_unused 952405 Key_blocks_used 395539 Key_blocks_used is increasing day by day and Key_blocks_unused is decreasing day by day. Ater a month Key_blocks_unused will reach to 0. Does it mean that i need to increase the key_buffer_size. Already key_buffer_size=1G. While going through forums and lists , i found that when key_blocks_unused is less. then select query will become slow. So whats the right solution. Thanks, -- Krishna Chandra Prajapati
Re: Rewriting query to avoid inline view
schizophrenic Hi Rob, On Wed, Apr 23, 2008 at 6:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Apr 23, 2008 at 6:39 AM, Morten Primdahl [EMAIL PROTECTED] wrote: Thanks Rob and Baron, I'd never heard of the integers table approach before, really good stuff! If memory serves postgres has something similar built in, so the syntax is something like seq(1..100) or something like that (I can't remember the function name for the life of me). What you were thinking of is generate_series ( http://www.postgresql.org/docs/8.3/static/functions-srf.html ) . /schizophrenic -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql variables
In the last episode (Apr 30), Krishna Chandra Prajapati said: Key_blocks_unused 952405 Key_blocks_used395539 Key_blocks_used is increasing day by day and Key_blocks_unused is decreasing day by day. Ater a month Key_blocks_unused will reach to 0. Does it mean that i need to increase the key_buffer_size. Already key_buffer_size=1G. While going through forums and lists , i found that when key_blocks_unused is less. then select query will become slow. So whats the right solution. In a perfect world, you would set key_buffer_size to the total size of all your .MYI files. Depending on the size of your tables, that may simply not be possible. Luckily, it's usually not necessary, either. What is more important is the hit rate. Run SHOW STATUS LIKE 'key%', and compare Key_read_requests (how many times mysql asked for a key) against Key_reads (how many times mysql actually had to go to disk to fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate. If it's 0.99 or greater, that means that 99% of your key reads are already coming from the key buffer, and adding more key buffers is unlikely to give you a measurable performance increase. Don't worry too much about your write hit rate ( 1-(Key_writes/Key_write_requests) ); it's always going to be lower because mysql tries to keep the on-disk copy of the index up to date. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql variables
Hi Dan, I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0. There will be no free blocks then how insert query will work. Thanks Krishna Chandra Prajapati On Wed, Apr 30, 2008 at 10:42 AM, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Apr 30), Krishna Chandra Prajapati said: Key_blocks_unused 952405 Key_blocks_used395539 Key_blocks_used is increasing day by day and Key_blocks_unused is decreasing day by day. Ater a month Key_blocks_unused will reach to 0. Does it mean that i need to increase the key_buffer_size. Already key_buffer_size=1G. While going through forums and lists , i found that when key_blocks_unused is less. then select query will become slow. So whats the right solution. In a perfect world, you would set key_buffer_size to the total size of all your .MYI files. Depending on the size of your tables, that may simply not be possible. Luckily, it's usually not necessary, either. What is more important is the hit rate. Run SHOW STATUS LIKE 'key%', and compare Key_read_requests (how many times mysql asked for a key) against Key_reads (how many times mysql actually had to go to disk to fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate. If it's 0.99 or greater, that means that 99% of your key reads are already coming from the key buffer, and adding more key buffers is unlikely to give you a measurable performance increase. Don't worry too much about your write hit rate ( 1-(Key_writes/Key_write_requests) ); it's always going to be lower because mysql tries to keep the on-disk copy of the index up to date. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Krishna Chandra Prajapati
