RE: cluster or replication
I've 10 server in differents locations, I want to make a broadcast, I would like to have always datas synchronized between this network, each database have the same tables and same structure. Each insert or update in database will be executed for each server within this broadcast, what's the best choice ? At the moment, replication is your best option. Replication works very nicely over wide-area-networks, where the bandwidth between each node could be dramatically different. The only constraint you'll have here is that there can only be one MASTER that has to accept all the INSERT/UPDATE/DELETE (or anything that will make the data change). All other nodes would be considered SLAVEs and be READ-ONLY. Hope this helps. a -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Major Difference in response times when using Load Infile utility
Test 1 Amount of data - 5.5 million rows. Time Taken - 6+hrs Approximately. Test2 Amount of data - 0.45 million rows. Time Taken - 2 mins approximately. Is this an InnoDB database by any chance? If it is, and it is a clean import, then disable the FOREIGN_KEY_CHECKS. SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0; This is a small tip i picked up on the MySQL documentation that someone had left in the comments and has been to date one of those tips that has literally saved DAYS of my life. a ps Remember to put them back on again after you finish the import SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS=1; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
This recipe is intended to minimize the impact on ongoing database operations by inhibiting writes only during a relatively speedy operation (creating a snapshot). The long dump operation can ... This seems to be a rather long winded way of doing this. Why not replicate the database and therefore not have to bring it down ever. I wrote a blog entry about this very thing, and had some interesting comments back on http://blog.spikesource.com/mysql_hotbackup.htm Hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HTML in MySQL?
If you use textarea field of a form, it produces null characters (\n) in the end of every string. I recommed to replace them with br tags before writing into the database. It'll help to avoid output problems. Use preg_replace(); for it. Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup and restore a database in a query ？
Could we do a database dump/backup in a query like below ? mysqldump.exe --default-character-set=gb2312 --opt --host 192.168.0.1 -u root -p -C mydbmydbfile or restore a database in a query like below ? mysql.exe -h 192.168.0.1 -u root -p -C mydbmydbfile Then we can do backup and restore in GUI mode without call mysqldump.exe,mysql.exe in character mode . Ah okay now i see what you are trying to do. The [mysqldump] is a utility that sits outside of the main mysql engine. You cannot invoke this from within the mysql shell from the best of my knowledge. As for pulling in backups from the mysql shell, then yes that is possible using a number of techniques: % mysql SOURCE [path to your file] or % mysql LOAD DATA [path to your file] Links to more information: http://dev.mysql.com/doc/mysql/en/load-data.html http://blog.spikesource.com/mysql_hotbackup.htm hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. t: 650 249 4279 b: http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: show master/slave status privileges ?
I have made a user with the following command: GRANT ALL ON *.* TO 'test'@123.123.123.123' IDENTIFIED BY 'h4x0r' Silly question Morten, and I am sure you have probably done it, but you are definitely running: % mysql FLUSH PRIVILEGES; -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance of DB with many tables
But what about INNODB? And are there table engine-independent implementation details which might cause performance problems for a database with many tables? This thread got me wondering how many file-handles are open for INNODB tables since it is only one large file on the file system. A quick look at our production server that is running approximately 50 tables within an INNODB instance, I see the number of open file handles is significantly less than 50. Which is what i would have expected. Looking at the other table formats, I do see a file handle open for every single table (more than one file handle since there is multiple files that describe a table under MyISAM). BTW on Linux systems you can check this using: % lsof -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
dan wrote: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). So don't be spouting any sweeping statements. If your distribution doesn't have that limitation, then fantastic, good for you. But for others it is indeed a real limitation. The original question was indeed a geniue one, and while the poster accidently typed in the wrong size, i wouldn't be so quick to jump all over him. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
Thank you, a much reasoned and sensible reply. This is information people can use, as oppose to the posts that 'say well its okay for me, you must be stupid' types. ;) Dan Nelson wrote: In the last episode (Apr 06), Alan Williamson said: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). That is because although Linux binaries can access files over 2gb, they do not do so by default. Apache was probably not compiled with the required defines (-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64), so that's why it stops at 2gb even though both the kernel and filesystem most likely do support larger files. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? Mysql's configure script checks for systems that require special flags to access large files, so no mysql binaries should have this problem on modern Linux systems (i.e. any 2.4 kernel) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CURDATE() bug?
Could anyone tell me if this is a bug or not. SQL: SELECT CURDATE()+0; RESULT: 20040331 Thats good. However consider this: SQL: SELECT CURDATE()+1; RESULT: 20040332 Not so good. Infact with this version any WHERE clauses you would put this in, fails to bring back the right result. Does CURDATE() support numeric addition like this? Or is the +0 purely a casting-hack to get the right format. Its not meant as pure addition. Thoughts? thanks alan -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CURDATE() bug?
Does CURDATE() support numeric addition like this? Or is the +0 purely a casting-hack to get the right format. Its not meant as pure addition. Yes, hav a look at http://www.mysql.com/doc/en/Date_and_time_functions.html for explaination further for addition, use date_add(curdate(), interval 1 day) or watever i know how to add dates, that wasn't the point of the post! but thank you nonetheless! ;) I was merely looking for clarification. If +0 is purely a casting hack then it should be highlighted as such so people don't assume. By using +0 does suggest its a numerical addition and therefore why stop at 0. Why not 1 etc etc etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CURDATE() bug?
RTFM! and what was the reason for this rudeness? Can't you explain yourself without descending into this sort of language? I do read the manual, and it is this reason i posted to the list. Clarity is a wonderful thing, and sadly the manual isn't clear on this matter. I stand by my original statement. If you believe it not to be a bug then so be it; we agree to disagree. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs. INNODB for a single blob table
A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Thanks for that Chris, interesting thoughts. For clarification, there is *NO* UPDATEs running on this table. Not a single one! :) Many more SELECTs than INSERTs Chris Nolan wrote: Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just the list please!
Can people please just email the list and not the person *AND* the list!!! i get duplicate emails and its very annoying to what is a great list so far. kinda puts me off from answering peoples questions! thanks! :) - Original Message - From: Alan Williamson [EMAIL PROTECTED] To: cvarda [EMAIL PROTECTED] Sent: Tuesday, March 16, 2004 5:30 PM Subject: Re: Blocking INSERT/UPDATE on SLAVE (replication) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using OR
Keith wrote: is there any alternative to using OR for selecting between values? ie: pla.type='1' OR pla.type='2' OR pla.type='3' OR pla.type='4' Keith for stuff like this i try and arrange things in numerical blocks so i am doing selects like pla.type = 1 AND play.type = 4, probably not needing both constraints depending on how you arrange your query. I also had a table that had two columns that needed an OR on. I moved it to another table, and used a join on those two tables and boy did that make a difference. Removing the OR makes a huge performance gain. hope this helps, i'll let the real SQL experts jump in here and give their response. -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC timeout after 4.0.8 - 4.0.18 upgrade?
With respect to this problem, I am not running on Windows, but Redhat, and seeing this problem often. Which part of: http://www.mysql.com/documentation/connector-j/index.html#id2803835 should i be looking at? thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
