RE: Oracle DBA here looking for advice on MySQL ....
I'd say MySQL 2nd edition by Paul Dubois. It has the first 200 pages with stuff you already know, but the next 800 pages are mysql specific. Very good reference book and best practices guide. The only thing it's missing is what I think should be in every book (and is in virtually none). 5 pages devoted to the initial setup of the program on each major OS. It's such a simple thing, but often there are very competent individuals who just want to read the best practices setup in concise form. Then, when more time is available, go back and tweak, etc.. -Original Message- From: Johnson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2003 6:43 PM To: MySQL Users Subject: Oracle DBA here looking for advice on MySQL What is the best book on MySQL with regard to its Architecture and how it starts up, shutdowns, processes queries, rolls back data, etc etc. ? I am not looking for a SQL book here. What is the best My SQL book you have read ? Thank you in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
runlevel setting on default rpm is wrong (in my opinion)
We were affected by the power outage last week and I think people should be aware - I'm not sure if it's fixed (I'm virtually certain it is not), but the default rpm install of 4.0.12 has mysql starting up in runlevel 4 only. When our power was restored, RedHat went to runlevel 3 and mysql never started on its own. To find out if you are affected run: /sbin/chkconfig --list mysql Then, run: /sbin/runlevel If chkconfig shows the 3:off and runlevel shows N 3, then mysql will not start on its own. This is probably because I don't have X running by default on the DB machine. I consider this a good thing. To rectify the situation: /sbin/chkconfig --level 3 mysql on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql processlist sleep time
I think I see the problem. Mysql really needs to be on it's own box. It's designed to just use as much power as it can find. This is a good thing for those with dedicated machines. I don't know if there's a configuration setup that tell mysql that it's not the head honcho. Does Windows have a way to start a process (mysql) in low priority? -Original Message- From: Gary Broughton [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:20 AM To: [EMAIL PROTECTED] Subject: RE: Mysql processlist sleep time The PHP one is indeed used less, probably by about 10% of the users while it's being tested. I was simply wondering if the idle timeouts were possibly responsible for the CPU usage problems, and I thought (rightly or wrongly?), that setting the 'xxx_timeout' options would close those persistent connections after the set number of seconds. It's just so bizarre that the mysqld program eats up all the available CPU most of the time, inevitably almost grinding things to a halt. I've searched high and low for a solution, asking advice in lots of places, tweaking loads of things here and there, and nothing seems to make any difference whatsoever. I appreciate that Windows, MySQL and PHP is not really the combination of choice though! :-) Many thanks for your reply. Gary -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 22:55 To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- 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]
RE: Designing a secure database?
The point of the hash is that it doesn't matter who sees the data, right? username | passwordhash | ipaddress | permission adam | AALKJA2344AFDS | 10.1.1.1 | rwxrwxr-- jake | 45324AFSDAF3423 | 10.1.1.1 | rwxrwxr-- tyrone | AALKJFF323FSDAF | 10.1.1.1 | rwxrwxr-- All you can ever do is compare the username passwordhash combo to what's in the database. There's no way to find the password from this. What is important though, is to make sure that nobody (except where necessary) has insert/update access to the table. One thing I would not recommend doing (because I hate enums and sets), but would be helpful, is defining the acceptable Ip addresses in the column definition using enum. -Original Message- From: Lefevre, Steven [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 5:00 PM To: [EMAIL PROTECTED] Subject: Designing a secure database? I'm working on a website/database deal, and right now I'm designing the security model. It's using LAMP(HP), so all the tricks are going to be through PHP. First off, this will be behind a firewall with SSL encryption. We may also setup some kind of VPN tunneling.I'll be ignoring other security details that don't apply to the problem at hand, but please feel free to suggest. I'm going to have users enter a username and password, with PHP emulating the htaccess dialogue boxes. I would like to check the entered values in a permissions table. This table looks like: username | passwordhash | ipaddress | permission All users will have a static IP, so they have to match the username-password-ipaddress combination. I'm storing the passwords as an MD5 hash in case someone breaks in and reads the table. So what MySQL user does PHP log onto as the database as, in order to read the permissions table? (After that, we just go with the logged user's permission). If I make a special user that just has permission to read the permission table, do I have to store that user's password plaintext in a php script somewhere, thus adding a security risk if someone were to get a hold of that password? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication
Also, one has to work out the cost of high availability. If you're talking about a situation where you reduce downtime from 4 hours/yr to .5 hours/yr and it costs you x dollars, you have to make sure that the extra 3.5 hours of downtime would cost more than that much money. The system you have appears to cost $10-100k more than a standard master-slave setup. If you're using a stable unix, stable mysql, good hardware (Sun,HP,IBM), and RAID in a datacenter. You're talking about 99.95% uptime right there. Throw in a slave and it's probably 99.99%. People always seem to forget that downtime is usually caused by human error on a well made system. Human error is what most effort needs to be taken to correct. That means putting your effort into reducing DELETE and UPDATE statement rights, keeping everybody off the machines, having auto_commit off by default. Just think of all the times you've seen a service unavailble due to somebody [EMAIL PROTECTED] up as compared to a kernel fault or a faulty RAID card. -Original Message- From: Ian Neubert [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 6:23 PM To: [EMAIL PROTECTED] Cc: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: RE: MySQL Replication Good question :) I got a message from a person off the list that suggested I use network disk mirroring or a NAS/SAN/NFS system to handle that. I'm not sure if the mirroring would be 100% perfect, but the NAS/SAN solution should as either server would be reading and writing to the same physical data. But, then I have another point of failure. Heh. I realize that creating the perfect HA system is probably the most difficult thing to do, and doesn't come cheaply either. However, I'm going to think it through and try anyway :) I've read your presentations on your website and have used that info for my plan here, but its a little difficult to get details from just the slides (as you even mentioned on your site) :) Do you bother with multi-masters? How do you ensure redundancy on the write/master server? ... Ian Neubert Director of IS TWAcomm.com, Inc. http://www.twacomm.com/ -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 2:53 PM To: Ian Neubert Cc: Dathan Vance Pattishall; [EMAIL PROTECTED] Subject: Re: MySQL Replication On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote: I was trying to design it so that the slaves wouldn't know they had connected to a different master, as they both masters would have the same IP address that gets failed over based on the Linux Virtual Server software and VRRP (like heartbeat from Linux-HA). That path is a very, very, very difficult one. How can you absolutely guarantee that each master's binlog will be indentical in name, size, and content? If you can't, this scenario really falls apart. (I've suggested enhancements to MySQL that would fix this but don't know if they're terribly high on the priority list...) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg) -- 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]
RE: Mysql processlist sleep time
exactly, NULL is a good thing. It means that php isn't constantly tearing down sessions and starting new ones. The connect process is virtually always the longest step of the query (except the big queries, but the goal is to keep them minimized in an application and use good design, indexes, etc.) -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 5:55 PM To: Gary Broughton Cc: [EMAIL PROTECTED] Subject: Re: Mysql processlist sleep time On Thu, Aug 07, 2003 at 07:54:24PM +0100, Gary Broughton wrote: Hi all I continue to have problems with the CPU usage with MySQL and PHP under IIS 5 (Win2000). I recently rewrote our messageboards in PHP (from ASP). I now have both online separately, and if I look at the processlist, the times on the ASP version rarely hit double figures, but those on the PHP version often reach several hundred (wait and inactivity timeouts are set to 300 - I thought this would stop it?!). I'm not sure what the problem is. From your description, it sounds as if the PHP one is either used less or is more efficient about using connections, since they're idle more often. I am at a real loss as to why the processes are not being cleared. I am using a persistent connection at the top of the webpage, and every MySQL query is ended with a 'mysql_free_result()' statement, including before any redirects using the 'header' command. Hang on. You're using *persistent* connections, so why would you expect them not to persist? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,516,276 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reindexing
I just did a major insert of new data and now all my selects have slowed down. The table is innodb. Is there a way to reindex everything without having to drop anything. Otherwise, I suppose I will have to drop the indexes and remake them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dbase calculations
I believe views in Oracle (SQL Server? Sybase?) can do this if you need it. -Original Message- From: Andy Jackman [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 12:36 PM To: MySQL Subject: Re: dbase calculations Kalle, The usual way to do this is to create the table with the 2 real fields and then use a query to 'create' the sum field at run time. For example assume you have this table: create table my_table ( field_1 int(9), field_2 int(9) ); then you can write this query: SELECT field_1, field_2, (field_1 + field_2) AS my_sum FROM my_table; This print 3 'fields', the third one is called my_sum and contains the sum of the other two (the AS keyword gives a field a name). Hope this helps, Andy. Kalle Saarinen wrote: Hello I'm rather new when it comes to databases and I was hoping that someone could help me out! I was just wondering is it possible to make a field in MySQL dbase wich is a total of two other fields. ie. field_XX is a sum of field_1 and field2 Thanks -Kalle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: large mysql/innodb databases
There's also netapp (www.netapp.com). I don't have much experience in some of these things, but I would stick with the same vendor that you use now or whomever they have a partnership with. I can't imagine that Sun,HP,EMC,Netapp,IBM have major differences in the quality of their solutions or even the price (when totatl cost of ownership is included). -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 3:09 PM To: Andrew Braithwaite Cc: [EMAIL PROTECTED] Subject: RE: large mysql/innodb databases On Wed, 2003-07-23 at 15:27, Andrew Braithwaite wrote: Power problems are handled by our colo facility, we want to quickly restore for most hardware problems (disk/machine failures). Just have multiple inexpensive fully replicated servers with failover built into the application layer (that's what we do) - Individual machines can go down and the service still stays up. When those dead boxes recover, they can catch up from the replication logs and go back into service.. Our first backup plan is to move over to the slaves if one of the masters fails right now, but we do need to have something on tape, just in case of some sort of major disaster. On a periodic basis, we will take a snapshot using innodb hotbackup of the master machine that will go to a third box with a bunch of big raid-5 ide drives. We were planning on starting with NFS for the short term since innodb hot backup doesn't go over the network and figure something else out later. That's a good idea - my findings were that NFS was really slow and the best solution was to backup from a fully replicated slave (after it had temporarily stopped replicating) by piping the raw data files through tar and gzip (appropriate for you as you're not concerned abou cpu) to a backup big raid-5 ide server. NFS seems to be working ok for now since it's writing to a big raid-5 ide server. Heikki mentioned that they are working on adding the ability to use innodbhotbackup over a socket, so we plan to move to that when it's available. We aren't really comfortable with taking snapshots from a slave since we've had problems with slaves getting corrupted over time. Most of the problems have been fixed, but until there have been no replication bugs fixed for a few months, we aren't comfortable relying solely on it. One issue we have is that we are trying to plan out our setup for storing a total of about 25TB of data and we are trying to find the lowest cost solution, with decent reliability. And I'm trying to find the secret of eternal youth :) We're considering moving to a solution like EMC's -- do you or anybody else have any experience with that? Cheers, Andrew -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Wednesday 23 July 2003 22:51 To: Andrew Braithwaite Cc: [EMAIL PROTECTED] Subject: RE: large mysql/innodb databases We don't expect recovery to be shorter than the time it takes for the hardware to copy the data over. Restoring from tape should be a solution that is only needed in the case of a severe problem. Power problems are handled by our colo facility, we want to quickly restore for most hardware problems (disk/machine failures). We don't actually store any archive/aggregate information. Everything we store on the main databases is used on a relatively constant basis. What we are currently thinking about doing right now is having an identical master and slave, each with about 500 gigs (later these will be at about 1TB each). On a periodic basis, we will take a snapshot using innodb hotbackup of the master machine that will go to a third box with a bunch of big raid-5 ide drives. We were planning on starting with NFS for the short term since innodb hot backup doesn't go over the network and figure something else out later. This machine would then shutdown the slave, copy over the new snapshot, and restart replication at the point from the point that innodb hotbackup started running at. We would also take the snapshot from the IDE box, and write it to tape at this point. Any thoughts on this? What are you doing? One issue we have is that we are trying to plan out our setup for storing a total of about 25TB of data and we are trying to find the lowest cost solution, with decent reliability. On Wed, 2003-07-23 at 14:33, Andrew Braithwaite wrote: Hi, I'm afraid that with that amount of data and having a few huge constantly updated tables will result in huge restore times for disaster recovery (just untaring/copying backups of the magnitude of terabytes back to the live environment will take hours and hours..) You're talking massive enterprise sized solutions and we're on a budget in
RE: large mysql/innodb databases
With new HP ultrium tape drives, you can get 200GB/hr transfer rate. I kind of hate tapes (just like everybody else), but tapes have really improved in the past few years. These things are under $6k and could back up 1-2 TB overnight without much problem. With a library (MSL6060), you can have 4 drives and 60 tapes for 12 TB backup. I agree with the idea of skipping tape backup altogether, but that's only if the data is reconstructable in a worst case scenario or if the value of the lost data times the chance of it going down is not worth more than the catastrophic failure backup cost. -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 6:28 PM To: 'Joe Shear' Cc: [EMAIL PROTECTED] Subject: RE: large mysql/innodb databases Power problems are handled by our colo facility, we want to quickly restore for most hardware problems (disk/machine failures). Just have multiple inexpensive fully replicated servers with failover built into the application layer (that's what we do) - Individual machines can go down and the service still stays up. When those dead boxes recover, they can catch up from the replication logs and go back into service.. On a periodic basis, we will take a snapshot using innodb hotbackup of the master machine that will go to a third box with a bunch of big raid-5 ide drives. We were planning on starting with NFS for the short term since innodb hot backup doesn't go over the network and figure something else out later. That's a good idea - my findings were that NFS was really slow and the best solution was to backup from a fully replicated slave (after it had temporarily stopped replicating) by piping the raw data files through tar and gzip (appropriate for you as you're not concerned abou cpu) to a backup big raid-5 ide server. One issue we have is that we are trying to plan out our setup for storing a total of about 25TB of data and we are trying to find the lowest cost solution, with decent reliability. And I'm trying to find the secret of eternal youth :) Cheers, Andrew -Original Message- From: Joe Shear [mailto:[EMAIL PROTECTED] Sent: Wednesday 23 July 2003 22:51 To: Andrew Braithwaite Cc: [EMAIL PROTECTED] Subject: RE: large mysql/innodb databases We don't expect recovery to be shorter than the time it takes for the hardware to copy the data over. Restoring from tape should be a solution that is only needed in the case of a severe problem. Power problems are handled by our colo facility, we want to quickly restore for most hardware problems (disk/machine failures). We don't actually store any archive/aggregate information. Everything we store on the main databases is used on a relatively constant basis. What we are currently thinking about doing right now is having an identical master and slave, each with about 500 gigs (later these will be at about 1TB each). On a periodic basis, we will take a snapshot using innodb hotbackup of the master machine that will go to a third box with a bunch of big raid-5 ide drives. We were planning on starting with NFS for the short term since innodb hot backup doesn't go over the network and figure something else out later. This machine would then shutdown the slave, copy over the new snapshot, and restart replication at the point from the point that innodb hotbackup started running at. We would also take the snapshot from the IDE box, and write it to tape at this point. Any thoughts on this? What are you doing? One issue we have is that we are trying to plan out our setup for storing a total of about 25TB of data and we are trying to find the lowest cost solution, with decent reliability. On Wed, 2003-07-23 at 14:33, Andrew Braithwaite wrote: Hi, I'm afraid that with that amount of data and having a few huge constantly updated tables will result in huge restore times for disaster recovery (just untaring/copying backups of the magnitude of terabytes back to the live environment will take hours and hours..) You're talking massive enterprise sized solutions and we're on a budget in the same sentence (which are not compatible with each other) - I know because we are the same here! A couple of things I can suggest: 1. Redesign your applications so that you archive/aggregate information that will never be used again. 2. Write a function that will backup the often changed stuff on a daily basis and backup the seldom changed stuff on a weekly basis. (as you're on a budget use a few inexpensive IDE raid 5 linux boxes - 6 x 250GB = 1.25 TB for backup) 3. Put in place a replication system that is so resilient that how ever many machines go down, there will still be plenty of fully replicated servers to satisfy the demand. Make sure that you have UPS so that if
RE: Could someone please help in choosing a manual...
I think the reference manual is basically what's on the web. The book you ordered, MySQL, Second Edition is indispensible. Since Paul is on the line, if there's ever a third edition, it would be nice to have the book and the appendices broken into separate books (they'd be bundled together at the store). Sometimes, 1,000 pages is a bit heavy to move around on one's desk :-) -Original Message- From: Ola Ogunneye [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 2:46 PM To: [EMAIL PROTECTED] Subject: Re: Could someone please help in choosing a manual... Hi Paul, Thanks for the info, but is MySQL, Second Edition the same as the reference manual? The link you gave me has 3 books and none of them was the reference manual. Are you saying that I need the reference manual and then the Second Edition? I just purchased the Second Edition from Amazon. Thanks Ola Paul DuBois [EMAIL PROTECTED] 07/22/03 01:56PM At 13:28 -0400 7/22/03, Ola Ogunneye wrote: I am still getting my feet wet on mysql. I, however have seen the MYSQL Refereence Manual at the mysql site while I have also seen Mysql Cookbook referenced or mentioned in the list. As a true novice starting out, can someone tell me which of the two would be best suited for a true newbie Everyone needs the MySQL Reference Manual. If you're new to MySQL, then MySQL Cookbook may not be the best choice for a first book. A more general text is MySQL, Second Edition. Some comparison information is available here: http://www.kitebird.com/mysql-book-comparison.php Disclaimer: I wrote both books, so of course I know more about them than other author's books. I'm sure other people on this list will provide recommendations for which books they've found most helpful. Thank you all in advance Ola -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- 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]
RE: Can mysql handle this load?
Certainly datetime is the way to go. It takes up 8 bytes per row, as opposed to 4 bytes for int. But, even if there are 10 million rows (over 27 years of data), thats only a 4 MB difference. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 10, 2003 5:13 PM To: [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? why use INT for a date? i am used to do this with my bulletin board, since i need a 1-second resolution and so i can easily use the time() function in php and format the output string with date(), which is also using unix timestamps. but for applications that only need a resolution of 1 day, something like DATE would be better, i think. for client side, it's more processing to get the date displayed and to do some arithmetics with it (calculate time spans etc.), right? correct me if i'm wrong, since i had some chaotic encounters with DATE and TIMESTAMP values at the beginning of my 'mysql time', and i'm using INT unix timestamps since then... -yves -Ursprüngliche Nachricht- Von: Rudy Metzger [EMAIL PROTECTED] An: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Adam Gerson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Gesendet: Mittwoch, 9. Juli 2003 17:19 Betreff: RE: Can mysql handle this load? Why using int for date? Better suited would be DATE or DATETIME (or even TIMESTAMP, depending how you want to use it). For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp when you make them UNSIGNED. For status I would choose CHAR(1), you can put a lot of information into that, which also stays (a bit) human readable. Also enums would be ok but are a mess to change later (in the application). Do yourself a favor and use a master detail relation for this, eg: CREATE TABLE student_status ( Status CHAR(1) NOT NULL,/* short status flag, eg. A */ Verbose VARCHAR(20) NOT NULL, /* verbose description, e.g. ABSENT */ PRIMARY KEY(status) ) Maybe keep 'verbose' on char to force fixed line size and thus faster access. Cheers /rudy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: woensdag 9 juli 2003 16:42 To: [EMAIL PROTECTED]; Adam Gerson Cc: [EMAIL PROTECTED] Subject: Re: Can mysql handle this load? i think this should be no problem... i'd think of some table layout like this: date int PRIMARY student_id int PRIMARY status int extra_data what-you-want then you should get about 360,000 records per year. i saw people on this list reporting about millions of records etc... and i guess they had a little greater tables than you should get here. but why would you want to move any previous records to another table all the time? just keep it in one table and back up anything older than 5 years or so. that keeps your table at, say 50 MB, and you can run real-time queries anytime :) -yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's up with this GATOR crap?
The whole point of gator is that the web site owner has no control of this. There is nothing mysql.com webmaster can do about this. I think they could sue GatorWare, but to what end. In the future, it's important to take a innocent until proven guilty approach on this list. -Original Message- From: C. Reeve [mailto:[EMAIL PROTECTED] Sent: Thursday, July 10, 2003 11:43 PM To: [EMAIL PROTECTED] Subject: What's up with this GATOR crap? Hi, I have noticed recently that every time I go to the MySQL manual page I get prompted to install Gator spyware. If MySQL condones or is going to use spyware, I may have to consider using another database. This may sound extreme, but I do not condone the use of spyware in any form and would hope that others feel the same way. Comments -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID hardware suggestions/experience
Where'd you get it. I've had bad experiences with generic machines but I'll take a peak if you send the link? There are a couple of things I didn't mention 2U Form Factor with tool-less rails Redundant Power Supply Redundant Fans (any 2 fans can go) Battery Backed RAID for full commit even on abrupt power loss dual Gbit ethernet Remote Console/Power administration without Operating System 400 MHz FSB DVD-ROM All drive are hot swap Fully supported and tested on RedHat Linux ES 2.1 (no weird hardware bugs) The last one is worth 5k alone. I've had generic machines just freeze from some weird kernel incompatibility with a raid card. With 30 machines though, you can afford to lose one. For me, with 1 or 2, I cannot and must get the best. -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 2:39 PM To: 'Adam Nelson'; 'mysql' Subject: RE: RAID hardware suggestions/experience ---Original Message- --From: Adam Nelson [mailto:[EMAIL PROTECTED] --Sent: Tuesday, June 17, 2003 11:56 AM --To: 'Bernd Jagla'; 'mysql' --Subject: RE: RAID hardware suggestions/experience -- --We recently bought a kick $%#%% machine for ~10k -- --HP DL380 --2x2.8GHz Xeon --1GB RAM --5 15k scsi drives (2 RAID 1 for OS and logs/3 RAID 5 for data) --RedHat Linux Enterprise Edition 2.1 You overpaid by 5K 2 x 2.8 GHZ Xeon 4 GB of RAM 5 15K SCSI Drives ICP SCSCI RAID control card with 1 Gb of ram on it. I just bought 30 of these boxes to build out my mysql farm for close to 400-600 queries a second with 60 connections a second of mix read / writes. -- -- --This machine easily handles 200 queries/sec and never gets a load --average above 1.5. For your space requirements, you may need the HP --ML370 with 5 RAID 5 drives. An important thing to remember is that the --raid card is very fast and the more drives (to a point) you put on it, --the better, so better to have 5 smaller drives than 3 bigger drives. --The reason we use raid 5 is that 95% of our queries are selects. If --your ratio is smaller, you will want to consider RAID 1 or 10. Another --thing I recommend is to stay with the big players (IBM,HP) and stay away --from Dell which is cut rate. If you want to save money, get a white box --over dell. Also, we get it from a good salesman at cdw. His address is --[EMAIL PROTECTED] Since they are in Chicago, there is no sales tax. --Lastly, if you're looking to buy soon, HP small-business direct --(www.smb.compaq.com) is offering free shipping until June 30 (but then --you have to pay tax - basically the same amount). -- -- -- -Original Message- -- From: Bernd Jagla [mailto:[EMAIL PROTECTED] -- Sent: Tuesday, June 17, 2003 1:15 PM -- To: mysql -- Subject: RAID hardware suggestions/experience -- -- -- Sorry I forgot to mention: -- -- We are using IRIS on an Origion2000, 7GB memory, 8 CPUs. I -- was thinking of -- spending up to $10K. -- I also wanted the redundant data for speeding up the seeks, I -- also need to -- speed up the writes. -- -- Bernd -- -- -- -- = -- -- Please note that this e-mail and any files transmitted -- with it may be -- privileged, confidential, and protected from disclosure under -- applicable law. If the reader of this message is not the -- intended -- recipient, or an employee or agent responsible for -- delivering this -- message to the intended recipient, you are hereby -- notified that any -- reading, dissemination, distribution, copying, or other -- use of this -- communication or any of its attachments is strictly -- prohibited. If -- you have received this communication in error, please notify the -- sender immediately by replying to this message and deleting this -- message, any attachments, and all copies and backups from your -- computer. -- -- -- -- --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]
RE: RAID hardware suggestions/experience
We recently bought a kick $%#%% machine for ~10k HP DL380 2x2.8GHz Xeon 1GB RAM 5 15k scsi drives (2 RAID 1 for OS and logs/3 RAID 5 for data) RedHat Linux Enterprise Edition 2.1 This machine easily handles 200 queries/sec and never gets a load average above 1.5. For your space requirements, you may need the HP ML370 with 5 RAID 5 drives. An important thing to remember is that the raid card is very fast and the more drives (to a point) you put on it, the better, so better to have 5 smaller drives than 3 bigger drives. The reason we use raid 5 is that 95% of our queries are selects. If your ratio is smaller, you will want to consider RAID 1 or 10. Another thing I recommend is to stay with the big players (IBM,HP) and stay away from Dell which is cut rate. If you want to save money, get a white box over dell. Also, we get it from a good salesman at cdw. His address is [EMAIL PROTECTED] Since they are in Chicago, there is no sales tax. Lastly, if you're looking to buy soon, HP small-business direct (www.smb.compaq.com) is offering free shipping until June 30 (but then you have to pay tax - basically the same amount). -Original Message- From: Bernd Jagla [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 1:15 PM To: mysql Subject: RAID hardware suggestions/experience Sorry I forgot to mention: We are using IRIS on an Origion2000, 7GB memory, 8 CPUs. I was thinking of spending up to $10K. I also wanted the redundant data for speeding up the seeks, I also need to speed up the writes. Bernd = Please note that this e-mail and any files transmitted with it may be privileged, confidential, and protected from disclosure under applicable law. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this communication or any of its attachments is strictly prohibited. If you have received this communication in error, please notify the sender immediately by replying to this message and deleting this message, any attachments, and all copies and backups from your computer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Machine requirements
I suppose you're doing this on your workstation (Windows?)? Ideally, Mysql should be on it's own machine - it (or any database) is designed to suck up resources. Also, you definitely need to tweak the configuration file. MySQL's default config is very anemic (annoying I know) as opposed to Oracle or SQL Server where they gear the configuration towards heavy/large databases by default. Here is my configuration (probably not optimal, but a step in the right direction) for a dedicated dual 2.8GHz Xeon with 1GB ram and dual channel raid running RedHat Enterprise Linux 2.1: [EMAIL PROTECTED] adam]$ cat /etc/my.cnf # my.cnf [mysqld] user=mysql set-variable = sort_buffer=2M set-variable = thread_cache_size=40 set-variable = record_buffer=2M set-variable = table_cache=730 set-variable = key_buffer=256M set-variable = tmp_table_size=256M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 set-variable = max_connections=200 set-variable = max_user_connections=195 set-variable = max_connect_errors=20 # innodb stuff added 05/16/02 innodb_data_home_dir = innodb_data_file_path = /data/mysql/ibdata:1G innodb_log_group_home_dir = /var/lib/mysql/iblogs set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_files_in_group=7 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 default-table-type=innodb blackrat said: I've just started using mySql. I'm an experience programmer with SQL based database, but know nothing about mySql. I'm setting up a single user database to be accessed with some Perl programs, with one main table, which will contain about 3 million lines. I'm running on an Athlon 1250 with 384meg ram, and finding the response time too slow. Just getting a count of the number of records by typing in an SQL query [not using Perl] takes over a minute, also when access is going on, I can't do anything else on them machine like access the web, it becomes too slow to be usable. Am I better setting up the database on another computer and accessing it through TCP/IP. I have available a spare machine which is a Celeron 450. -- 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]
RE: fastest DB engine
InnoDB is very fast. In theory, I guess MyISAM is considered faster (that may not even be true these days). We're talking about fine hairs here and I haven't heard anybody complain about InnoDB-specific speed problems. Anyway, if you're using Windows, you're must not be too concerned about speed ; - ) -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Monday, June 16, 2003 5:00 AM To: [EMAIL PROTECTED] Subject: fastest DB engine hi all! i am currently working on a community projekt the DB is a mySQL DB but i am not sure which engine to use i know that innoDB supports foreign keys which i really want i order to keep the data consistent since there will be a lots of insert, delete and update activity with forums and guestbooks and new users and so on and i know i can have different table types in the same DB but from what i've learned myISAM is the fastest is this true second, i have followed some discussions here about foreign keys but i still have nog managed to create foreign keys i have a table users and another userTypes where tblUsers have columns userID, usertypeID, username, password... and tblUserType userTypeID, userType so i want userTypeID in tblUsers to be a foregn key referencing the userTypeID col in userTypes both tables are myISAM userID is indexed and primary key in tblUsers userTypeID is indexed and primary key in tblUserType userTypeID in both tables are int(10) what is wrong? my OS is win XP pro and i have mySQL 4.1 (not really sure exact version since i cannot reach the DB from where i am right now) thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_thread_concurrency and hyperthreading
So I'm setting up a fancy new machine with Xeons doing hyperthreading. What this means is that there are 2 physical processors, but as far as linux is concerned, there are 4. Does anybody know whether thread_concurrency should be 2*(Number of Physical Processors) or 2*(Number of Virtual Processors). I'm leaning towards the virtual (which comes out to 8) but I am open to suggestions. mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disabling version number
I would be wary of disabling version(). That's the kind of annoying thing that sys admins do when they don't understand the life of a developer. Some programs and modules require the version() function to work. Security to that extreme is only useful if you understand that it may cause more downtime than a breakin. If that is understood and the time/money spent is worth it, then that is fine. I can only see this kind of security necessary for medical or classified information. -Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 11:39 AM To: Florian Effenberger Cc: [EMAIL PROTECTED] Subject: Re: disabling version number Florian Effenberger wrote: No, why? Part of my security concept, I generally disable all version numbers. You can patch mysql source and recompile ;) However, if someone has enough access rights on your system to run select version();, showing mysql version number should be the least important of your problems. Regards, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using two databases in a query?
This should work, but I would consider using the 4.0.12 instead of the alpha release. -Original Message- From: dreq jkj [mailto:[EMAIL PROTECTED] Sent: Thursday, March 20, 2003 11:02 AM To: [EMAIL PROTECTED] Subject: Using two databases in a query? Is it possible to use multiple databases in a query? (Privided that the user has correct permissions, that is) I have tried to use mysql as root and make a such query, and got a result. The result, however, did look very strange. database ccs_db.ccs_admin: userID int unsigned username... database jobbguiden.jobbguiden_admin_user: id int username varchar(...) Using select: select jobbguiden.jobbguiden_admin_user.username, ccs_db.ccs_admin.username, ccs_db.ccs_admin.adminID from ccs_db.ccs_admin left join jobbguiden.jobbguiden_admin_user on(ccs_db.ccs_admin.adminID=jobbguiden.jobbguiden_admin_user.id); Result: +--+--+-+ | username | username | adminID | +--+--+-+ | mall | autorun | 1 | | stefan | dbtransfer | 2 | | NULL | staffan grön | 3 | | 4 |tarina | 5 |ffan | 6 | | 7 | | 8 | | 9 | | 10 |ha | 11 |an | 12 |el | 13 |an | 14 |el | 15 |ik | 16 |le | 17 |na | 18 |ny | 19 | | 20 |signer +--+--+-+ All usernames from ccs_db has been trunkated in a strange manner... Is this action completly un-supported, but semi-working...? Btw; I'm running mysql 4.0.1-alpha... //drew _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
viewing uncommited transactions (InnoDB)
Does anyone know how to view uncommited transactions for a session? This is more of a problem when using MySQL Manager when it asks whether or not to commit the transaction list. Is there a definitive way to know what queries have yet to be committed/rolled back? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: The Security of MySQL
Are you using Windows? If so, this root/mysql user talk will be meaningless. You can still make the directory secure and only touchable by the user that mysql is running as. Is this what you need? -Original Message- From: Dyego Souza do Carmo [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 12:13 PM To: [EMAIL PROTECTED] Subject: The Security of MySQL I'm using MySQL-Pro+InnoDB 4.0.11 and i have a BIG problem... My users is hacking the database because the MySQL system tables are stored in .MYD format and to hack database is simple , only rename the database and copy the blank database... restart MySQL and the permissions is FULL FOR ALL USERS... Exists in MySQL routines to ENCRYPT tables ? or the data inside tables ? the functions like ENCODE and DECODE print a password in log file ( IN CLEAR TEXT) and this is terrible for me ! Exists the PASSWORD on CREATE TABLE STATEMENT but i'm using and is same without the clause. Please MySQL-Team and users... The security of MySQL is too simple ? only rename and the database is opened for world ? please help in advance ;) Tanks Tanks very much sql,query,innodb,mysql -- --- ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento -- --- E S C R I B A I N F O R M A T I C A -- --- The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 221602060 $ look into my eyes Phone : +55 041 296-2311 r.112 look: cannot open my eyes Fax : +55 041 296-6640 -- --- Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
request for recommendations on a machine
I've gotten some weird responses to this type of question before, but I'd like some input anyway. We are getting a new dedicated database server. Currently our load average is hovering above 2 and although things work fine, that snappiness is fading as the load becomes higher. We may be able to squeeze some more speed out of the current machine, but we are interested in making a redundant setup anyway, so a new machine it is. Currently, we have a dual 1.3 Ghz PIII processor machine with raid 1 scsi160 drives on Linux 2.4.7-10smp with 1GB RAM running 3.23.49a-Max on an ext3 partition. I feel that there are three problems. High load in general, not the best filesystem for the job, and the fact that there is only one raid1 diskspace. This is my solution: dual Xeon 2.8 GHz, 2GB RAM, 1 RAID 1 partition (2 disks) for the OS, 1 RAID5 partition (3disks) for the db. Then, what I would like to get some feedback on is whether to use a raw partition or what filesystem. Although ext3 is slow, I have actually had somebody unplug the database machine, plug it back in, and when it rebooted, everything worked fine and there was no corruption. I think I have to thank ext3 for that. If the raw partition cannot handle that, then I am not interested in using it. Any comments are greatly appreciated. Does anybody have 300 queries per second or more? What kind of hardware do you have? This is top now: 11:56am up 22 days, 2:07, 1 user, load average: 4.22, 3.64, 3.25 100 processes: 96 sleeping, 4 running, 0 zombie, 0 stopped CPU0 states: 93.4% user, 6.2% system, 0.0% nice, 0.0% idle CPU1 states: 90.2% user, 9.4% system, 0.0% nice, 0.0% idle Mem: 1028432K av, 1020672K used,7760K free, 0K shrd, 53612K buff Swap: 522104K av, 33328K used, 488776K free 364720K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 4434 mysql 17 0 441M 409M 2744 R35.3 40.7 901:02 mysqld-max 4470 mysql 9 0 441M 409M 2744 S21.0 40.7 61:07 mysqld-max 4462 mysql 17 0 441M 409M 2744 S20.2 40.7 63:07 mysqld-max 4463 mysql 9 0 441M 409M 2744 S20.2 40.7 65:34 mysqld-max 4449 mysql 15 0 441M 409M 2744 R19.3 40.7 66:47 mysqld-max 18483 mysql 10 0 441M 409M 2744 S19.1 40.7 103:58 mysqld-max 4437 mysql 14 0 441M 409M 2744 S14.8 40.7 64:52 mysqld-max mysql 9 0 441M 409M 2744 S11.0 40.7 65:39 mysqld-max 4450 mysql 16 0 441M 409M 2744 S10.1 40.7 65:14 mysqld-max 8776 adam 15 0 988 988 764 R 7.2 0.0 0:03 top 4001 mysql 9 0 441M 409M 2744 S 6.6 40.7 79:12 mysqld-max 18580 mysql 9 0 441M 409M 2744 S 4.9 40.7 101:26 mysqld-max 4467 mysql 9 0 441M 409M 2744 S 4.4 40.7 64:10 mysqld-max 18577 mysql 13 0 441M 409M 2744 R 1.5 40.7 99:48 mysqld-max 4432 mysql 9 0 441M 409M 2744 S 0.3 40.7 66:15 mysqld-max 4458 mysql 9 0 441M 409M 2744 S 0.3 40.7 62:31 mysqld-max And mysqladmin status: Uptime: 1908444 Threads: 10 Questions: 268078040 Slow queries: 21540 Opens: 1098 Flush tables: 1 Open tables: 730 Queries per second avg: 140.469 And vmstat: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 2 0 0 33328 5104 53652 366912 0 0 3 66 2 4 1 4 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Percentile calculations
I believe you can do: select avg(scantime) from percentile; select std(scantime) from percentile; std() is the same as stddev() which finds the standard deviation. If the scantimes are gaussian (pretty good assumption if it's a large dataset), then the 95th percentile will be avg() + 2*stddev(). This should be very fast and quite accurate. Really it's probably better since I would think that you want the value within which 2 standard deviations fall rather than exactly 95%. This is more useful from a statistics point of view. -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 01, 2003 2:17 AM To: Karl Dyson Cc: [EMAIL PROTECTED] Subject: Re: Percentile calculations Hi. On Fri 2003-01-31 at 17:22:37 -, [EMAIL PROTECTED] wrote: [...] select count(*) from percentile where criteria; Work out 95% or this value. create temporary table percentile (id int unsigned auto_increment primary key, scantime decimal(20,10)); insert into percentile (scantime) select processingtime from emails where same criteria order by processingtime desc limit value from above; select min(scantime) from percentile; If I am not mistaken, you can pick the row in question without a separat table: Basically you limit the query to the first n rows (in descending order) and then pick the one with the lowerst value, which should have been the last in the limited range. So you could pick that row directly, I think: SELECT processingtime FROM emails WHERE blah ORDER BY processingtime DESC LIMIT value,1 Second, why use 95% of the table, if you can do with 5%? In other words, calculate the remainder, sort ascending and limit by the remainder you calculated. The difference should especially in your case, where you copy those rows and can avoid to do so for 90% of the columns. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using more than one CPU on FreeBSD?
I would be scared out of my gourd to do the dual mysqld processes. Just backup the machine and put linux on there. That was my solution to the FreeBSD problem. -Original Message- From: Tommy F. Eriksen [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 3:14 AM To: [EMAIL PROTECTED] Subject: Using more than one CPU on FreeBSD? Hi, I've inherited a FreeBSD/MySQL database-server (Compaq DL360, dual P3 1GHz), running (at the moment): Server version: 4.0.3-beta However, as far as I can tell, MySQL/FreeBSD 4.6.2-RELEASE still can't agree on utilizing more than one CPU. My question is this: A year or two ago, someone mentioned simply running two mysqld's on the same database-files (using file-locking) and then, using some form for loadbalancing between the two, was able to use more than one CPU for the mysqlds. Is this still the recommended way of doing this? I know I could use Linuxthreads, but the times I've tried them in the past, they have done more harm than good (low performance, unstability etc). Any advice would be appreciated :) Kind regards, Tommy Eriksen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Copying to temp table
If I'm getting copying to temp table often on some big queries, I usually increase tmp_table_size, but for innodb, is that variable used or is it innodb_buffer_pool_size? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL security flaws uncovered
The real problem is the lack of a central knowledgebase. Is there one that I'm not aware of? Even if there is, it should be very obvious off the front page of the website. -Original Message- From: Csongor Fagyal [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 5:34 AM To: [EMAIL PROTECTED] Subject: Re: MySQL security flaws uncovered Michael She wrote: It's bad for business : ) Maybe they're taking the MS route. I second this. These vulnerabilities are serious, they must be given more attention. Apache, PHP, RedHat and so on and so on are very careful with issues like this, all vulnerabilities/exploits are immediately published through all possible channels. Yes, it is always a pain to find out something like this, obviously the MySQL team just would like to forget this once and for all, but doing troublesome reinstalls/upgrades and so on is still better then getting our system hacked. - Cs. At 12:19 AM 12/18/2002 -0500, Michael Bacarella wrote: A good question posted to another list.. forwarded message follows Several vulnerabilities have been found in the MySQL database system, a light database package commonly used in Linux environments but which runs also on Microsoft platforms, HP-Unix, Mac OS and more. http://zdnet.com.com/2100-1104-977958.html So why no mention on the MySQL.COM site? That rather bugs me. In contrast, sites for products like Apache or Bind are very clear about current/past security issues. Is MySQL.COM the wrong place? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can MySQL handle 120 million records?
That's the only thing wrong with Mysql is what it doesn't do. Everything it does do it does fantastically. -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 10:12 AM To: Michael She Cc: Qunfeng Dong; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Can MySQL handle 120 million records? Michael She wrote: I agree. MySQL is a great database, but I wouldn't call it enterprise grade. Considering that the database is used to store billing information... one has to be weary about losing all the records due to a bug or deficiency in Besides actual additional features (management software, SNMP support, stored-procedures and the like), what would lead you to decide that MySQL isn't ready for the big time, assuming a site didn't need the above. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: more about using sets
I agree entirely. SETS and ENUMS should be avoided by any normal user (frankly, I think they should be deprecated). They are not portable and it's just horrific to be changing data with an ALTER statement. Foreign Key relationships (even if they aren't real as in standard MySQL) are the way to go. Get InnoDB and use foreign keys before it's too late and you're stuck with a hodge podge system. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:39 AM To: [EMAIL PROTECTED] Subject: Re: more about using sets In article [EMAIL PROTECTED], David T-G [EMAIL PROTECTED] writes: Hi, all -- I'm still trying to get a good handle on how sets can be useful to me. I have three scenarios so far. 1) A set of states (US Mail type, not turing type :-) I can pick from the list of states when entering address data, and storing the set entry should take less space than storing even a 2-char string. 2) A set of ccard types (MC, Visa, AmEx) It's easy to have a pick list to avoid misspellings and such 3) A set of pay scale levels (master, journeyman, apprentice, intern) Each staff member needs to be at a certain scale so that the software knows how much to pay him or her per session. We don't want to make up pay levels that aren't in our list. For each of these, is a set the way to go, and is it saving me anything? In the third case, I want to restrict the level in the personnel table to one of the defined levels. Do I just use a set in the field definition and then list from there (and then it gets messy if we add a new level) or do I create a jobscalelevels table and define the levels in there and then set the personnel.level field to match and forget about the idea of a set? I avoid SETs whenever possible - i.e. always unless storage/performance is extremely important. They give you nothing which you can't do with a separate value table and foreign keys, but they compromize portability. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Serwer Hardware p4 or pIII ?
I've posted my comments before but the important thing is that P4 is largely unnecessary as it doesn't have instructions that apply to server applications (mostly). So, PIII (dual is quite helpful) the fastest you can get without paying a premium 1 GB ram dual scsi drives (raid 1) This is the simplest scenario and will handle tons of queries (100/sec) with drive failover (very very nice) very fast and it can fit in 1U. If you have less money, I would drop the second proc, then move to lower speed proc, then less memory. -Original Message- From: Nicolas MONNET (Tech) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 10:46 AM To: Helmut Apfelholz Cc: [EMAIL PROTECTED] Subject: RE: Serwer Hardware p4 or pIII ? On Wed, 2002-12-04 at 15:23, Helmut Apfelholz wrote: --- Simon Green [EMAIL PROTECTED] wrote: MySQL uses memory and HDD the most and so processor speed is not so important. Well, processor speed is also important, on some of our servers processors are almost 100% occupied. If your bottleneck is memory speed, you will see 100% CPU usage even if the CPU actually spend 99% of its time idle, waiting for data to come in. I'm not too up to date on the latest RAM technology, but I hear there's several types of DDR, the most expensive one being significantly faster. Or is it? Anyone care to share some insight on this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Slow performance using 3.23 on RH 8.0
The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 I'm running Mysql 3.23.52 on a Redhat 8.0 installation booting to Gnome. The machine is a dual AMD 1800, 1 gig of ram, one Ultra ATA IDE drive, and 2 18 gig scsi 10,000 RPM drives on a RAID controller running Raid 0. I've got everything except /db on the IDE drive, /db is the only thing on the raid array. I've got a couple of smallish tables and one larger table with about 7 gigs of data. The larger table is a fixed row format table with each row being 462 bytes wide. I have a primary auto increment int column and a unique index on a varchar 60. Pack keys is off, delayed key writes on. With this kind of hardware I was expecting pretty good performance, but I haven't seen it yet. I finally decided something was wrong when I had to run an alter table on the 7 gig table, adding 3 columns, a varchar 12, a varchar 50, and a datetime columm and it took over 10 HOURS to complete. That seems way too slow to me... I've included relevant portions (the uncommented portions) from my.cnf, the OS installation was fairly vanilla, using defaults for just about everything. The file system is ext3. Any suggestions or things I haven't included that you need? Sorry if I'm doing something really stupid here... relatively new to Linux after a lot of years of windoze. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Slow performance using 3.23 on RH 8.0
Oh, and what's up with the thread_concurrency being 6? That doesn't make any sense unless you have a tri-processor setup. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 I'm running Mysql 3.23.52 on a Redhat 8.0 installation booting to Gnome. The machine is a dual AMD 1800, 1 gig of ram, one Ultra ATA IDE drive, and 2 18 gig scsi 10,000 RPM drives on a RAID controller running Raid 0. I've got everything except /db on the IDE drive, /db is the only thing on the raid array. I've got a couple of smallish tables and one larger table with about 7 gigs of data. The larger table is a fixed row format table with each row being 462 bytes wide. I have a primary auto increment int column and a unique index on a varchar 60. Pack keys is off, delayed key writes on. With this kind of hardware I was expecting pretty good performance, but I haven't seen it yet. I finally decided something was wrong when I had to run an alter table on the 7 gig table, adding 3 columns, a varchar 12, a varchar 50, and a datetime columm and it took over 10 HOURS to complete. That seems way too slow to me... I've included relevant portions (the uncommented portions) from my.cnf, the OS installation was fairly vanilla, using defaults for just about everything. The file system is ext3. Any suggestions or things I haven't included that you need? Sorry if I'm doing something really stupid here... relatively new to Linux after a lot of years of windoze. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Slow performance using 3.23 on RH 8.0
Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 I'm running Mysql 3.23.52 on a Redhat 8.0 installation booting to Gnome. The machine is a dual AMD 1800, 1 gig of ram, one Ultra ATA IDE drive, and 2 18 gig scsi 10,000 RPM drives on a RAID controller running Raid 0. I've got everything except /db on the IDE drive, /db is the only thing on the raid array. I've got a couple of smallish tables and one larger table with about 7 gigs of data. The larger table is a fixed row format table with each row being 462 bytes wide. I have a primary auto increment int column and a unique index on a varchar 60. Pack keys is off, delayed key writes on. With this kind of hardware I was expecting pretty good performance, but I haven't seen it yet. I finally decided something was wrong when I had to run an alter table on the 7 gig table, adding 3 columns, a varchar 12, a varchar 50, and a datetime columm and it took over 10 HOURS to complete. That seems way too slow to me... I've included relevant portions (the uncommented portions) from my.cnf, the OS installation was fairly vanilla, using defaults for just about everything. The file system is ext3. Any suggestions or things I haven't included that you need? Sorry if I'm doing something really stupid here... relatively new to Linux after a lot of years of windoze. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M set-variable= table_cache=512 set-variable= sort_buffer=22M set-variable= record_buffer=22M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=6 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 0 tmpdir = /tmp/ [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [myisamchk] set-variable= key_buffer=500M set-variable= sort_buffer=8M set-variable= read_buffer=10M set-variable= write_buffer=30M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com
RE: Slow performance using 3.23 on RH 8.0
Go for it. Whatever works works. Caveat, MSDN is not an actual license for production use, only development. If you ever get audited and are using it on a production machine, you will have to pay the license. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:23 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Well, I've got an MSDN subscription so I have access to a legal copy.. the non-unix thing is a downside but there are number of scp command line utilities for Windoze that I can use to move the data back and forth as needed I'm seriously thinking of biting the bullet and going that way. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:18 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Don't let this list fool you. SQL Server is a very good product. It is far superior to Mysql in every way except cost and the fact that it doesn't run on unix. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:12 PM To: 'Adam Nelson'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 Actually it is hardware Raid 0, not software. I knew about the safety issue but I had been told that from a performance stand-point that Raid 0 was the fastest. I've watched the disk activity on the IDE drive and there is next to none, but I guess it's possible something is going on there. Gnome is because linux is very new to me... I found the GUI to be comfortable coming from a Windoze world. Since I first installed I've had a crash course in doing it from the command line (I'm managing a web and mail server as well) so at some point I could probably undo it. I'll try the variables when I get a chance. Just as an aside I had a friend running SQL Server on a 2000 box that is a pretty similar configuration... he added the same 3 columns to a table with 5 keys and 3 times as many columns in less than 2 minutes. -Original Message- From: Adam Nelson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 4:35 PM To: 'Ledet, Mike'; [EMAIL PROTECTED] Subject: RE: Slow performance using 3.23 on RH 8.0 The first thing I would do is toss the ultra ata drive and just use the scsi drives running raid1, raid0 just isn't safe and hardware raid1 is much faster than you would think. This may seem counter-intuitive, but there are all sorts of bus issues that could be interfering. You may very well have more logging going on on the ata drive than you think. Second, do not install X or gnome at all. What's the point? Third, look at these variables (although I doubt they will help much): set-variable = table_cache=256 set-variable = tmp_table_size=256M If this doesn't work, get in touch. -Original Message- From: Ledet, Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 11:01 AM To: '[EMAIL PROTECTED]' Subject: Slow performance using 3.23 on RH 8.0 I'm running Mysql 3.23.52 on a Redhat 8.0 installation booting to Gnome. The machine is a dual AMD 1800, 1 gig of ram, one Ultra ATA IDE drive, and 2 18 gig scsi 10,000 RPM drives on a RAID controller running Raid 0. I've got everything except /db on the IDE drive, /db is the only thing on the raid array. I've got a couple of smallish tables and one larger table with about 7 gigs of data. The larger table is a fixed row format table with each row being 462 bytes wide. I have a primary auto increment int column and a unique index on a varchar 60. Pack keys is off, delayed key writes on. With this kind of hardware I was expecting pretty good performance, but I haven't seen it yet. I finally decided something was wrong when I had to run an alter table on the 7 gig table, adding 3 columns, a varchar 12, a varchar 50, and a datetime columm and it took over 10 HOURS to complete. That seems way too slow to me... I've included relevant portions (the uncommented portions) from my.cnf, the OS installation was fairly vanilla, using defaults for just about everything. The file system is ext3. Any suggestions or things I haven't included that you need? Sorry if I'm doing something really stupid here... relatively new to Linux after a lot of years of windoze. Thanks in advance Mike ** my.cnf * [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock datadir = /db/mysql skip-locking set-variable= key_buffer=500M set-variable= max_allowed_packet=2M
RE: Insert row in x for every row in y
You can use insert group_map (user_id,group_id) select user_id,6 as any_label from user -Original Message- From: Zabel, Ian [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 11:35 AM To: [EMAIL PROTECTED] Subject: Insert row in x for every row in y I've having trouble figuring out the SQL to do this. I have two tables, user, and group_map, and I want to insert a row into group_map for every row in user. user user_id group_map user_id group_id For each user_id: insert into group_map ( user_id, group_id ) values ( user_id, 6 ) Can I use a select into or something? Or should I just script it outside of mysqlclient? Ian. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Copy Records in a table...
This style of insert does not work in MySQL. The two table names must be different. -Original Message- From: Alan McDonald [mailto:alan;meta.com.au] Sent: Thursday, November 07, 2002 9:56 PM To: Doug Coning; [EMAIL PROTECTED] Subject: RE: Copy Records in a table... insert into mytable(field1, field2, field3) select field1, field2, newvalue from mytable where productcode=xx -Original Message- From: Doug Coning [mailto:lists;coning.com] Sent: Friday, 8 November 2002 14:35 To: [EMAIL PROTECTED] Subject: Copy Records in a table... Hi everyone, I'm still learning MySQL. I have a database of 600 items. I am still adding products. Several of these products are identical in nature, but have maybe one or two columns that are different. Is there a way to write a SQL command that selects these items and then inserts them, and then updates them respectively with one command? Thanks, Doug MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Performance over a network
I agree that whatever the app is, having the ability to unplug the database (or for it to go down) and have there be a queue on the other machine is ideal. This will mean that even if the db machine reboots for whatever reason in the middle of the night, nobody will ever know the difference. This is good application design. -Original Message- From: Brent Baisley [mailto:brent;landover.com.] Sent: Friday, October 25, 2002 9:20 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Performance over a network It would be helpful to know how much data you are trying to pump across. If you are having trouble finishing in under 30 seconds over a 100mb connection, it must be a lot of data. The first thing to check is to make sure you have your connections set to full duplex. Even if there are only two machines talking you could be getting a lot of collisions, especially if you are transferring data in small amounts. Which brings me to the next suggestion. If you are doing many individual sql inserts you may not be using the network efficiently. You want to be able to fill multiple network packets during your transfer, taking advantage of what some refer to as burst mode. You should be using this style insert: INSERT INTO db (field1,field2,...) VALUES (val1,val2,...),(val1,val2,...),(val1,val2,...),(val1,val2,...),... If you are still having trouble, you may want to rethink how you are going about transferring the data. Perhaps creating an import file locally and transferring the file over to the database machine. You then have a program on the db machine to process files that are transferred. In this scenario you don't have any timing issues since you are essentially creating a queue that is being processed on the db machine. Once a file is processed it's deleted and then the program checks for any other files ot process. This also allows you to take the database down for maintenance if you have to. Lots of benefits to this setup. On Thursday, October 24, 2002, at 08:45 PM, [EMAIL PROTECTED] wrote: * Is there any explicit tuning which can be done to speed up access over the network (short of adding gig-ethernet cards which isn't likely) ? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL oil change
These are good ideas. Just remember, if it ain't broke, don't fix it. Unless I see performance degradation, I don't see the need to do maintenance that could potentially create huge downtime (defrag doesn't work, table files are permanently destroyed). The risks must be weighed. I used ext3 filesystem which needs virtually no maintenance. Innodb seems to just keep on chugging without any loss of speed. Granted, my data is for the most part low write, high read with the writes being sequential. I am very diligent about indexing as well (extremely important for high-read systems). Also, I use oversized hardware to keep things snappy without having to waste time worrying about the minutae (ie. an extra $1k on hardware can be an amazing boost) -Original Message- From: mos [mailto:mos99;fastmail.fm] Sent: Thursday, October 24, 2002 6:23 PM To: [EMAIL PROTECTED] Subject: Re: MySQL oil change At 03:38 PM 10/24/2002, you wrote: Does anyone have any good maintenance tips for MySQL that should be done on a regular basis? I know table optimization is good to do from time to time but I would like to know of there is anything else I can do to help keep MySQL stable and running smoothly. Thanks Mark Mark, Just my 2 cents (so you know you're getting you money's worthg), is don't forget to frequently defrag the hard disk. If you have the time, you *may* want to unload and reload the data every now and then (make sure you have backups in place). This helps to make the data more contiguous specially if using InnoDb. Or you can use Optimize Table if you do a lot of deletes to MyISAM tables. Large tables may pose a problem with the Optimize command because some people have reported crashes. Of course, and I stress again, make sure you have backups in place. Run Myisamchk daily (hourly?) on your tables to make sure they haven't become corrupted. And like changing the oil in your car, make sure the car is not running at the time.g (In other words, have scheduled down times when you can perform routine maintenance.) Also see http://www.linux.gr/cgi-bin/info2www?(mysql)Performance and http://i4net.tv/marticle/get.php?action=getarticlearticleid=4 Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange behavior of CASE .. WHEN ... THEN....
I think that's confusing, but right. every null value is distinct, thus null != null. weird, but null is not a value, it's the lack of a value, so nothing can be shown about it. so, SELECT IF( NULL = NULL, 0 , 1 ) AS RESULT ; is not the same as SELECT IF( NULL is NULL, 0 , 1 ) AS RESULT ; -Original Message- From: Harald Fuchs [mailto:lists-mysql;news.protecting.net] Sent: Friday, October 25, 2002 5:42 AM To: [EMAIL PROTECTED] Subject: Re: Strange behavior of CASE .. WHEN ... THEN In article 000701c27193$1bf2bfa0$aa3fe7cb@jsheo, Heo, Jungsu [EMAIL PROTECTED] writes: Hello, every one. I Found a bug about CASE .. WHEN .. THEN.. mysql SELECT VERSION() ; ++ | VERSION() | ++ | 4.0.3-beta | ++ 1 row in set (0.00 sec) mysql select CASE NULL WHEN NULL THEN 0 ELSE 1 END AS RESULT ; ++ | RESULT | ++ | 1 | ++ 1 row in set (0.00 sec) I think RESULT should be '0'. Am I wrong? IF() works finely. mysql SELECT IF( NULL IS NULL, 0 , 1 ) AS RESULT ; ++ | RESULT | ++ | 0 | ++ 1 row in set (0.00 sec) Is this a bug or a mistake of mine? The latter one. While NULL IS NULL returns true, NULL = anything returns false, even if anything is NULL. [Filter fodder: SQL query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql on a separate server from webserver
Something like this: $site::dbName= 'DBI:mysql:test:10.10.2.20'; for a database called test on 10.10.2.20 Then add the user for the web machine (assuming it's 10.10.2.10) to the authorized list: Grant select on test.* to [EMAIL PROTECTED] identified by 'password'; Flush privileges; Everything else is exactly the same (and I find it works much better and is obviously more secure). -Original Message- From: Nestor [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 3:09 AM To: [EMAIL PROTECTED] Subject: Mysql on a separate server from webserver I have written previous websites where I used perl or PHP to access a Mysql DB wen bith the webserver and the Mysql db are on the same server. My questions is how different is it to access or connect to the Mysql database when the database is on a separate server(machine)? Thanks, Nestor A. Florez http://www.IneedWork.org ---My website --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.384 / Virus Database: 216 - Release Date: 8/21/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: InnoDB and disk geometry
The raid function is not useful (that's to get around the 2GB filesize limit on certain OS). I think it's a misleading function (I think it should be called RAIF - Redundant Array of Inexpensive Files). I use RAID 1 (hardware) scsi and my load looks to be processor bound (believe it or not) with a dual processor machine. I think that unless you are using a load that requires a quad processor machine, scsi160 drives on a raid card are fast enough, and hence there is no requirement for separate disks. If you must have separate files, I can only assume that there is an undocumented way to create a database in a specified tablespace. -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 2:54 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: InnoDB and disk geometry You might want to lookup MySQL and RAID. MySQL does support a database RAID setup. This confused me early on because I kept thinking of disk based RAID, but this is database based RAID. I haven't used it yet, so I can't help you any more that that. Hope it helps a little. On Thursday, August 15, 2002, at 08:38 PM, [EMAIL PROTECTED] wrote: A problem I have with InnoDB tables, which forced me to use MyISAM tables in a recent project, is as follows: (I am running MySQL on Solaris, not that it matters much for this problem.) With MyISAM tables, I can easily get different databases to reside on separate physical drives. The base directory for database files is /var/mysql/ Then I mounted a drive at /var/mysql/db_a and another at /var/mysql/db_b for instance. Then databases by the names db_a and db_b reside on different physical drives. But with InnoDB, you specify a tablespace file(s) and then the database you create go into those files. I cant figure out any reasonable way to tie specific databases to specific drives, except by running a mysqld server for each drive I want a database on. For large databases, proper planning of disk geometry can have a significant effect on performance. I very much like InnoDB, but can't seem to get around this problem. Any insight would be greatly appreciated. sean peters [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL vs. Oracle (not speed) - not part of the rant, but real information
I believe MySQL doesn't do a lot of the optimizations that MSSQL does. However, you can do it manually (I think) by playing around with both the order of the where clause and the order of the join clause The where clauses go in order, so you want to use the first part of the where clause to get rid of as many records as possible (ie. use the most restrictive where clause first and then go down from there). That way, you minimize the quantity of data getting through the first where clause and not the second or third, or nth. Also, MySQL has a really wimpy default configuration (I can't figure out why). Here is my /etc/my.cnf (I don't know what the equivalent is on windows) [mysqld] set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = table_cache=256 set-variable = key_buffer=256M set-variable = tmp_table_size=256M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 set-variable = max_connections=200 # innodb stuff added 05/16/02 innodb_data_file_path = ibdata:500M set-variable = innodb_buffer_pool_size=350M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_files_in_group=7 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=4 default-table-type=innodb -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 10:32 AM To: Francisco; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) I am not for one or the other I just hate to wait I need speed... we already have a MS-SQL server , so no more money needs to me spent... I did a 4 table join , drwing 3 years of sales data table one 6.5 million records --- AdminHierarchy table two 1.5 million records AdminCoverage table three 10191 records --- AdminProcuder table four 19823 records --- AdminProduct SELECT AdminHierarchy.WritingAgentID, AdminHierarchy.WritingAgentSlot, AdminHierarchy.ProducerID, tempsap.taxid, (ModalPremium * BillModeID * (PercentOfCase / 100)) AS TotalPaidPremium, AdminCoverage.CoverageID, AdminCoverage.CoverageIDSbc, AdminHierarchy.RegionCode, AdminProduct.LobId, AdminCoverage.StatusID, AdminCoverage.StatusDate, AdminCoverage.InitialPremiumDate, AdminCoverage.PaidToDate, tempsap.GROUPID FROM AdminHierarchy INNER JOIN AdminCoverage ON AdminHierarchy.CoverageID=AdminCoverage.CoverageID AND AdminHierarchy.CoverageIDSbc=AdminCoverage.CoverageIDSbc LEFT JOIN AdminProducer ON AdminProducer.ProducerID=AdminHierarchy.WritingAgentID LEFT JOIN AdminProduct ON AdminCoverage.ProductID=AdminProduct.ProductID Left join tempsap on AdminProducer.taxid = tempsap.taxid WHERE AdminCoverage.InitialPremiumDate = '2101' AND AdminCoverage.InitialPremiumDate = '20020430' ORDER BY AdminHierarchy.WritingAgentSlot,AdminCoverage.CoverageId, AdminCoverage.CoverageIdSbc, AdminHierarchy.ProducerID -Original Message- From: Francisco [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 9:21 AM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not speed) Hi Mary, I am not specially against or pro MySQL, Microsoft SQL Server, Oracle or any other database. Teams make their choices based on the project needs such as budget (is your team ready to spend thousands of dollars on Oracle and marry that corporation forever?), deployment (do you want your product with Oracle's price tag attached to it?), functionality: does the database server provide a viable solution technically speaking?, etc, etc. So it is not an issue of good or bad. But regardless of all that, what we should have, at least, is a great respect for people that has been working so hard to provide an affordable and viable alternative to the database server giants. I don't think that throwing those numbers without any other explanations about your test environments, SQL, tables, etc is a good practice. Do you think that you always get what you pay when you spend thousand of dollars in software and services without leaving you any other choices? I know that you did not put bad intentions behind your comment but should be more careful and precise. Whoever is interested on some benchmarks can go to: http://www.mysql.com/information/benchmarks.html There is an interesting article comparing Ms-SQL, Oracle, DB2 and MySQL in: http://www.pcmag.com/article2/0,4149,7279,00.asp Sincerely, Francisco --- Mary Stickney [EMAIL PROTECTED] wrote: I have been doing speed tests the same query ran on MYSQL took 45 minutes on MS-SQL it took 11 minutes.. yes you do get what you pay for -Original Message- From: Francisco [mailto:[EMAIL PROTECTED]] Sent: Friday, August 16, 2002 8:47 AM To: Mary Stickney; Elizabeth Bogner; [EMAIL PROTECTED] Subject: RE: MySQL vs. Oracle (not
RE: MySQL hardware concerns
Seems to me like a better architecture might be: N apache servers with mysql clients 1 Master Mysql Server 1 Slave Mysql Server/admin/backup server If you have 5 slave servers (one on each apache server), that would cause much more traffic on the internal network than each apache machine just being a client and running the queries on the master server. We do the kind of traffic you're talking about with the above architecture and it works great (plus you're not administering N+1 mysql servers with all their security/maintenance issues). Having the database totally removed from the internet is definitely safer. I highly recommend the DL360 G2 from compaq/HP with dual 1.4Ghz processors and 1 GB ram and raid 1 scsi drives. This machine is more expensive than the generic equivalent, but it's 100% worth it. If you're looking to double or triple your traffic, you may need to think about a dl380 with quad processors (but that's probably overkill -Original Message- From: Jeremy Hiatt [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 11:29 PM To: [EMAIL PROTECTED] Subject: Re: MySQL hardware concerns Can you translate 100,000 users into database numbers? How many SELECTs per second, UPDATEs per second, and so on? That'd help a lot. I believe roughly 70% of our queries are SELECTs, 29% UPDATEs, and less than a percent for both INSERTs and DELETEs. MySQL on localhost (3.23.46) up 4+22:08:37 Queries Total: 38,217,014 Avg/Sec: 89.86 Now/Sec: 131.80 Slow: 0 Threads Total: 1 Active: 1 Cached: 0 Key Efficiency: 99.98% Bytes in: 3,696,152,003 Bytes out: 4,006,033,106 +--++ | Variable_name| Value | +--++ | Handler_delete | 2056 | | Handler_read_first | 86116 | | Handler_read_key | 38126269 | | Handler_read_next| 66568466 | | Handler_read_prev| 0 | | Handler_read_rnd | 26653054 | | Handler_read_rnd_next| 4229676008 | | Handler_update | 12940207 | | Handler_write| 171166 | | Key_blocks_used | 15582 | | Key_read_requests| 77465425 | | Key_reads| 14742 | | Key_write_requests | 470685 | | Key_writes | 468967 | | Uptime | 425398 | +--++ This is from a production server and isn't as efficient as the work in progress on development servers (note handler_read_rnd_next), but these are accurate enough for scaling. Note that many queries take place server-side in automatic calculations (cron). We're leaning towards a dual 1GHz (512k cache) box with 4GB memory and SCSI raid 1-0. Comparitively the figures above are from a dual 1GHz (256k cache) running both apache+mysql, with 1GB memory and SCSI also. As I mentioned before this will be our main DB. Do you think this the best move for our money? How do I calculate how many Apache/MySQL Slave sub-servers I can add before our master maxes out? This seems like a hit and miss way to scale, hardware wise. For a few thousand $$$ in new hardware expenses I'd much prefer a hit. Thanks, Jeremy _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL hardware concerns
Having a separate slave server separate from everything else just seems like the clean way. Having a slave on every web machine will add a load to each web server necessitating better physical machines (since every change to the db has to replicate to N machines rather than just 1 machine). Both ways will work fine since 100 queries/sec is not pushing any sort of network envelopes or anything. -Original Message- From: Eric Anderson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 11:22 AM To: Adam Nelson Cc: [EMAIL PROTECTED] Subject: RE: MySQL hardware concerns On Tue, 30 Jul 2002, Adam Nelson wrote: Seems to me like a better architecture might be: N apache servers with mysql clients 1 Master Mysql Server 1 Slave Mysql Server/admin/backup server If you have 5 slave servers (one on each apache server), that would cause much more traffic on the internal network than each apache machine just being a client and running the queries on the master server. We do the kind of traffic you're talking about with the above architecture and it works great (plus you're not administering N+1 mysql servers with all their security/maintenance issues). Having the database totally removed from the internet is definitely safer. I highly recommend the DL360 G2 from compaq/HP with dual 1.4Ghz processors and 1 GB ram and raid 1 scsi drives. This machine is more expensive than the generic equivalent, but it's 100% worth it. If you're looking to double or triple your traffic, you may need to think about a dl380 with quad processors (but that's probably overkill Uhh.. either way, why wouldn't you just double-NIC it (one public NIC, one private NIC) and run a private network to keep the database off the Net? And if you '--skip-networking' the mysql slaves aren't exposed to the Net either (rightly so). Personally, I think having the data pushed out to the slave servers is the way to go, since if something happens to the master server (even a reboot) the slaves will continue to serve pages because of the local data. -- - Eric Anderson Wild Web Amateurs CyberIron Bodybuilding ICQ 3849549 http://www.wildwebamateurs.com http://www.cyberiron.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
processors
we are considering moving to a 4-way system as the load average on our 2-way system is at 2. Does anyone have recommendations? Is the larger 1MB cache on some xeon chips worth the extra $$$? I'm thinking of 2 raid1 arrays, one for the os and logs, the other for the db. The data is relatively small, but gets much in the way of select statements. sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Server speed working with databases and apache
I think any descent server would be fine: Linux/FreeBSD dual PIII 512MB ram (or better 1GB) 2 scsi drives with raid1 Frankly, you could even get cheaper than that with 1 processor. -Original Message- From: Javier Armendáriz [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 3:33 AM To: [EMAIL PROTECTED] Subject: Server speed working with databases and apache Hi everybody: I´m looking for some information about server speed. The problem: I need a server for about 40 concurren users, it will be in an internal net, the server will manage a mysql database with a master table of about 4000 records and some chil tables from 2 to 4 records, it will be accesed trougth apache with php. I need a response time about 0.1-0.2 seconds. Is there some information or good article about this issue What kind of machine would be better? Can anybody give me some information or reference??? Thanks a lot Sorry about my english Javier Armendáriz [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how to design mysql clusters with 30,000 clients?
I believe slashdot uses one heavy duty database server (quad xeon) and a redundant one. This isn't an actual cluster, but I would have to assume that slashdot gets more that 30,000 clients at a time. slashcode.org has information (somewhere deep in there, I couldn't find it, but I remember being told about it). -Original Message- From: Dave Watkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 6:44 PM To: Patrick Hsieh; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: how to design mysql clusters with 30,000 clients? At 16:02 22/05/2002 +0800, Patrick Hsieh wrote: Hello list, I am expecting to have 30,000 http clients visting my website at the same time. To meet the HA requirement, we use dual firewall, dual Layer-4 switch and multiple web servers in the backend. My problem is, if we use the user-tracking system with apache, php and mysql, it will surely brings a huge amount of database traffic. How can I balance mysql load among multiple mysql server yet assure the data consistency among them? My idea is: 1. use 3 or more mysql servers for write/update and more than 5 mysql servers for read-only. Native mysql replication is applied among them. In the mysql write servers, use 1 way replication like A-B-C-A to keep the data consistency. But I am afraid the loss of data, since we can't take the risk on it, especially when we are relying our billing system on it. This will not work. MySQL replication does not work like that. With MySQL replication you have one master and all others replicate from it. It is also the only server that can write to the DB. Your options for following this route would be to either use the experimantal 2 way replication support in the beta of MySQL4. Or use a different DB Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL/InnoDB question
I don't know the answer to this in mysql, but in oracle, while you can have big files, I've seen tables divided at, say, the 1 million record mark. So there could be 5 files for a fast 5 million record table. This is seamless to the user if there are views. Alas, mysql doesn't have views :-(. -Original Message- From: Craig Vincent [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 6:28 PM To: MySQL Subject: MySQL/InnoDB question I'm not new to MySQL but have only recently familiarized myself with InnoDB. One question I have so far been able to find an answer on is the datafiles. Let's assume I have 5 GB worth of information, this information changes regularly. Performance wisewould I be better off using a single 5GB file, or would there be benefits to using multiple datafilessay 5 datafiles containing 1GB of information each? Any documentation/links providing additional information in regards to this would be much appreciated. I couldn't find anything at either the MySQL or InnoDB site in regards to this. Sincerely, Craig Vincent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyISAM and innodb
I'm finally getting around to working with InnoDB for real :-) Anyway, is there any reason to still use MyISAM on any tables. Concurrency is my biggest problem (Locked tables, etc.). My theory is that the tables that wouldn't benefit from converting to InnoDB are so small (5-50 rows?) that I might as well just convert every table for simplicity sake (I have 15 tables, some have 5 records, some 25,000). Can anyone enumerate the reasons not to use InnoDB (besides what's listed at http://www.innodb.com/ibman.html#InnoDB_restrictions) from a performance standpoint? The only reason I see for using myIsam would be a table with extremely high insert rates (web logs, tcp logs, etc.) and very few users (or none). I guess embedded applications might be concerned about the footprint of innodb as well? SQL (to get around the spam filter) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Encrypting with PASSWORD() function
of course, the safe way is to always reset the password when such a thing happens. -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 14, 2002 10:48 AM To: [EMAIL PROTECTED] Subject: Re: Encrypting with PASSWORD() function Walter, Tuesday, May 14, 2002, 4:59:15 PM, you wrote: WDF I am trying Mysql Password() function, to encrypt password in a a WDF user/password WDF table, WDF it works fine both ways; it is to say, when I submit a login WDF (user/password) WDF combination it fetches the pair ok. WDF My question is, as I read in the documentation, that the process is WDF irreversible, how can I deal with the fact WDF that a user can forget his password, if I will not be able to retrieve the WDF original string, because what i can see is the encrypted data WDF is there a workaround to deal with this, either than storing in a separeted WDF table the original password string (not encrypted), or shall i simply not WDF use this function ? What about using another functions for encrypting password and user name, f.e. ENCODE()/DECODE() or DES_ENCRYPT()/DES_DECRYPT()? Look at: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html WDF thanks in advance to any suggestion WDF Walter -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Row Locking issue in 3.23.x
use InnoDB which does support row-locking (MySQL-Max) -Original Message- From: Sherzod B. Ruzmetov [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 14, 2002 9:23 AM To: [EMAIL PROTECTED]; MySQL Maling list Subject: Row Locking issue in 3.23.x Hi. As far as I know, MySQL doesn't suport row-locking, only table locking is available. But I figured table locking for sessions table of CGI::Session::MySQL would be quite inefficient, for there might be millions of rows and hundreds of them could be active at any time. So what do you guys think of locking a specific row with a semaphore file and what conventions should the locker and/or the table should follow? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Performance on Dual Processor machine
/etc/my.cnf isn't installed by default. Try my-huge.cnf (I think) Also, did you use mysql-max on the new one and mysql on the old (just wondering) This is what I use for a 1 Gig RAM single processor machine: [mysqld] set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = table_cache=256 set-variable = key_buffer=256M set-variable = tmp_table_size=48M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 The last caveat - did you compile the freebsd kernel to actually use the second processor? Lastly, how did you copy the database over, sometimes the indexes don't copy. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 3:34 PM To: [EMAIL PROTECTED] Subject: RE: MySQL Performance on Dual Processor machine [snip] I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! [/snip] Amazingly enough (I did not do the install of MySQL) there is no my.cnf on either machine (there are the default my-whatever.cnf files.). I belive that I should use the my-large.cnf as a starting point. Does anyone have any suggestions other than the default configuration for this file? Thanks! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: nvarchar
I think he is saying varchar with support for unicode (that's how it is labeled in sqlserver). -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Friday, April 12, 2002 1:07 AM To: saraswathy saras Cc: [EMAIL PROTECTED] Subject: Re: nvarchar On Thu, Apr 11, 2002 at 06:47:39AM +, saraswathy saras wrote: hi, what is the function of nvarchar...is it can use in mysql7. Huh? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 63 days, processed 1,719,588,428 queries (312/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Port 3306 restricted to IP addresses
Firewall isn't good enough (who else is inside your firewall, likely the entire hosting company or internal corporate network). The user table has a host column that I use. Also, you can enable ipfw or some other local firewall on the host itself if you are very serious. -Original Message- From: Michael Zimmermann [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 8:34 AM To: Tshering Norbu; [EMAIL PROTECTED] Subject: Re: Port 3306 restricted to IP addresses -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 At Donnerstag, 4. April 2002 12:23 Tshering Norbu wrote: For the inbound connection on port 3306 of MySQL Server, how do I restrict the connection to some IP addresses something like 1.2.3.* What do I need to do in my.cnf file? I let the firewall do that kind of restrictions. - -- Michael Zimmermann (Vegaa Safety and Security for Internet Services) [EMAIL PROTECTED] phone +49 89 6283 7632hotline +49 163 823 1195 Key fingerprint = 1E47 7B99 A9D3 698D 7E35 9BB5 EF6B EEDB 696D 5811 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rFZH72vu22ltWBERAnojAKCFZMYbUGcp/0dQz3gJbsoHKc9xeACdFoAZ GGT4fn5G1hD+qmaEZx1+Mf4= =pmYD -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [newbie post] linked lists..
I think you need to be more detailed. What is your goal? Let's work from there. -Original Message- From: Daniel Jarrett [mailto:[EMAIL PROTECTED]] Sent: Friday, March 29, 2002 3:19 AM To: [EMAIL PROTECTED] Subject: [newbie post] linked lists.. is there any way of doing a linked list in mysql.. ? the only alternative i've got is to have a table with a fixed number of columns liike this item1 item2 item3 item4...etc is this the only way to do what i'm trying to do. does anyone get what i'm on about? cheers dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: binary expansion?
I don't like the set type as I can't use it on other databases (even though I do not foresee changing databases, I like to keep the option open). The bit method is what I'll probably use. Thanks for the help :-) -Original Message- From: Joel Rees [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 27, 2002 1:05 AM To: adam nelson Cc: [EMAIL PROTECTED] Subject: Re: binary expansion? adam nelson queried: iPersonType is a list in the form of 1,2,4,8,16,32,64 so, let's suppose that szPersonType for 8 is lawyer and szPerson type for 2 is redhead a value of iPersonType of 10 would mean redhead lawyer. I'd like a query that looks for lawyers (ie. iPerson type is any number from 8 to 15 or 16+8=32 or 16+8+4=28 or 16+8+1=29 or 16+8+4+2=30 or 32+8=40 or 32+8+4=44 ,etc. up to the max(iPersonType)) If you don't like bit functions (manual section 6.3.5.1) you can always divide by the iPersonType and take modulus 2. A result of 1 says it's a hit, 0 says it's a miss. Incidentally, have you considered using a SET type here (manual section 6.2.3.4)? Joel Rees Alps Giken Kansai Systems Develoment Suita, Osaka - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
binary expansion?
It's been a while since I've done this, does anybody remember: If I have 2 tables: tblPerson ( iPersonID int szPersonDesc varchar iPersonType int ) tblPersonType ( iPersonType int szPersonType varchar ) iPersonType = iPersonType (foreign key between the 2 tables) iPersonType is a list in the form of 1,2,4,8,16,32,64 so, let's suppose that szPersonType for 8 is lawyer and szPerson type for 2 is redhead a value of iPersonType of 10 would mean redhead lawyer. I'd like a query that looks for lawyers (ie. iPerson type is any number from 8 to 15 or 16+8=32 or 16+8+4=28 or 16+8+1=29 or 16+8+4+2=30 or 32+8=40 or 32+8+4=44 ,etc. up to the max(iPersonType)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Leap seconds
Correct me if I'm wrong, but a timestamp field is not meant to be human updateable (ie. it's solely to record the last change to the record). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 1:31 AM To: [EMAIL PROTECTED] Subject: Leap seconds Description: A timestamp value selected back is 38 seconds later than what was inserted. How-To-Repeat: ln -s /usr/share/zoneinfo/right/US/Pacific /etc/localtime mysql test mysql create table teststamps (stamp timestamp); mysql insert teststamps values (2002010100); mysql select * from teststamps; ++ | stamp | ++ | 2002010138 | ++ 1 row in set (0.00 sec) Fix: Please tell me. Submitter-Id: Originator:matt Organization: MySQL support: none Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.41 (Source distribution) Environment: lrwxrwxrwx1 root root 36 Mar 14 00:06 /etc/localtime - /usr/share/zoneinfo/right/US/Pacific System: Linux alice.xork.org 2.4.16 #1 Sat Dec 8 18:43:34 PST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81) Compilation info: CC='gcc' CFLAGS='-O3' CXX='gcc' CXXFLAGS='-O3 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Aug 22 2001 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Aug 22 2001 /usr/lib/libc-client.a - c-client.a Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Script for Data base backup and recovery : Very essential
I use a perl method for a full week's worth of backups, this could be migrated to have a weekly, monthly, yearly snapshot as well. #!/usr/bin/perl my $szToday = `date +%a`; chop ($szToday); $dbDumpName = '/var/backup/dbDump.mysql'; $backupFile = '/var/backup/siteFiles'; $siteDir= '/home/dir/of/other/files'; system (rm -f $dbDumpName\-$szToday.gz); system (/usr/local/bin/mysqldump -u fsbo fsbo $dbDumpName\-$szToday); system (gzip $dbDumpName\-$szToday); system (rm -f $backupFile\-$szToday.tgz); system (tar cfz $backupFile\-$szToday.tgz $siteDir); -Original Message- From: Gerald R. Jensen [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 9:49 PM To: Chetan Lavti; [EMAIL PROTECTED] Subject: Re: Script for Data base backup and recovery : Very essential This has been covered here a number of times before ... a search of the archive avoids the need to repeat the same info. That being said ... We use a cronjob (root) that calls a shell script at 3am daily. The line in the cronjob is: * 3 * * * /usr/local/bin/dbbakup.sh username password The shell script: # #!/bin/sh # $1 = Unix/MySQL Username # $2 = Unix/MySQL Password if [ ! -e /dbbakup ] then mkdir /dbbakup fi mysqldump -u$1 -p$2 -c --add-drop-table --add-locks --flush-logs --databases account/dbbakup/account.sql mysqldump -u$1 -p$2 -c --add-drop-table --add-locks --flush-logs --databases payroll/dbbakup/payroll.sql mysqldump -u$1 -p$2 -c --add-drop-table --add-locks --flush-logs --databases contact/dbbakup/contact.sql # If you wanted to ftp the resulting scripts to another server, you could add code to the shell script to run ncftpput, etc. Gerald Jensen - Original Message - From: Chetan Lavti [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 12, 2002 11:16 PM Subject: Script for Data base backup and recovery : Very essential hi, Can anybody tell me how can I write script for MySQL backup and recovery.( i am newbie as per script is cencern) I am using Innodb table type and using all the default parameters specified in the my.cnf file (my-large.cnf) Looking for kind response.. Thanks and regards, Chetan Lavti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
password special character muck up (I think)
I just had the brilliant idea of using a password for mysql root with semi-colons: password is blahblah;; this appears to have not worked in some way and now I'm stuck. I don't want to restart since that isn't very graceful (on a production machine). I've tried blahblah;;; and blahblah and blahblah; Anyone know what to do? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: password special character muck up (I think)
I don't see how to submit 'blahblah;;' using the quotes. I've tried mysql -u root and then typed all the conceivable combinations for password and mysql -u root --password=blahblah;; and mysql -u root --password='blahblah;;' What's worse is that I've looked at the raw table file and confirmed that the password is blahblah;; I could change it manually, but I don't want to corrupt the table (I assume that's a real possibility). -- tried that on a dev machine, it doesn't work because I can't flush the privileges. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: password special character muck up (I think)
I can access the client using other users, but none have mysql database access (ie. everything is fine except that I can't add users). If nobody knows the answer, I will restart with skip-grant-tables during off-hours. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: extension to TUNING PRODUCTION MySQL SERVER
This is what I have on our dual PIII (1.1 Ghz) 1 GB ram [mysqld] set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = table_cache=256 set-variable = key_buffer=128M set-variable = tmp_table_size=16M set-variable = interactive_timeout=7200 set-variable = wait_timeout=240 Most of your changes seem unnecessary. Key buffer should definitely be 64MB or more (I'm thinking of moving to 192MB myself). -Original Message- From: vijay khanna [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 23, 2002 2:39 AM To: [EMAIL PROTECTED] Subject: extension to TUNING PRODUCTION MySQL SERVER Hello every one... We are going into making the final changes to our production MySQL server. production server configuration is Free BSD PIII dual processor 800 mhz, 750 MB RAM. back_log = 20 DEFAULT back_log = 200 CHANGED flush_time = 1800 sec DEFAULT flush_time = 3600 sec CHANGED key_buffer_size = 16 MB DEFAULT key_buffer_size = 30 MB CHANGED max_allowed_packet = 10MB DEFAULT max_allowed_packet = 20MB CHANGED max_connections = 100 DEFAULT max_connections = 250 CHANGED Apache ,Tomcat, MySQL is running on the same machine.Kindly give us your expert opinion is it wise to run the three servers with the given server hardware configuration on the same machine...? Is it safe to alter the above mentioned server variables...? If safe..what is the syntax to set these parameters on Free BSD shell An example would be more help full. Thanx. Vijay Khanna System Analyst www.spsoftware.com Ph: 09120 - 4006154 INDIA FOR THOSE WHO CAME LATE : Hiya forum.. I have my site powered by MySQL server running on FreeBSD. The site is facing problems when the traffic increases.Database connectivity is through JDBC.My poolman is managing the database connections with 30 connections set, and is growable.People have started getting error when the rush increases... Error : Communication failure : Bad Handshake. Is there a MySQL server running on the port connectivity error. I suspect the max_connection variable which is default set to 100 reaches and the server refuses further connection.We have probed for any unused database connections hold by the Java.But we have religiously returned the connection back to the poolmanager after its used... I am running my development MySQL server on windows NT. Having gone thru the MySQL manual..for performance tuning the server,we came across many variables which can affect the server performance. So i issue the following commands: c:\ cd mysql\bin start the serverc:\mysqld set the variablec:\mysqld -O back_log=200 --help Looks like it updates the server..but when i see it thru the WinMySQLAdmin ,after restarting the server again ,it does not reflect there and displays the default settings. We have a production server runningCan any experienced people guide me how to update this server variable onto my remote server. Thanx. Vijay Khanna System Analyst www.spsoftware.com Ph: 09120 - 4006154 INDIA --- -- __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Insert if not update command
Is there any database only (currently I'm doing this in perl) solution to update a record or insert it if criteria isn't met: for example, I have a table: mysql describe tblHits; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | iListingID | int(11) | | PRI | 0 | | | iHits | smallint(5) unsigned | | | 0 | | ++--+--+-+-+---+ 2 rows in set (0.00 sec) I want to increment iHits with an update statement if the iListingID exists, otherwise, create a record with one hit. Currently I have perl figure this out with obvious locking issues (which I assume will be cured by my imminent move to innodb). Normally I would use a stored procedure, but I'm not sure if there's a mysql solution to this. If there isn't, I think it would be a very beneficial thing to put on the todo list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: foreign keys to what end?
Using foreign keys is a really, really good idea. Programs can't be trusted (and what about running sql queries ad hoc). Even with a really big database I wouldn't get rid of the keys, just time to move to a bigger better machine/configuration. -Original Message- From: David Felio [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 11:19 AM To: [EMAIL PROTECTED] Subject: foreign keys to what end? I have a MySQL InnoDB table for ACH (checking account) transactions that includes, among other fields, the below: trans_id (PRIMARY KEY) cust_type routing_num status site_id payment_type auth_type trans_type trace_num All of the above fields (except the primary key) are related to other tables that describe the customer type, transaction type, etc. or have valid values for routing number, status, etc. Should all of these be set up as foreign keys, or should some of referential integrity be done programmatically? How do you draw the line between how many foreign keys are too many, or is there no such thing? Should things like this always be handled by foreign keys instead of error checking in the program? The indexing along would seem to get fairly large in the transactions table if I set them all up as foreign keys. David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Porting from MS SQL to MySQL
I have to agree with the below. Clearly they want to get you into mssql and keep you there. There's really no point in switching databases, so (I know, this is too neutral) if you like these guys for some reason (good haircuts or whatnot), then go with mssql. If you can find people with good haircuts who will do it for mysql, all the better (and sometimes even cheaper). Regardless, don't migrate to a different database unless there's some huge reason to do so. -Adam -Original Message- From: Richard Burgmann [mailto:[EMAIL PROTECTED]] Sent: Sunday, January 20, 2002 6:08 AM To: [EMAIL PROTECTED] Subject: Fw: Porting from MS SQL to MySQL - Original Message - From: Richard Burgmann [EMAIL PROTECTED] To: Markus Lervik [EMAIL PROTECTED] Sent: Sunday, January 20, 2002 9:53 PM Subject: Re: Porting from MS SQL to MySQL Hi Markus, I'm an IT Consultant by trade and have been involved in lots of green fields projects and conversion projects over the years. Without knowing the details of your situation I will risk giving an opinion. 1. If the company has a developed/deployed product DESIGNED to run on MS SQL you can pretty much guarantee they are using every proprietary 'feature' of it and the actual industry standard SQL code is in the minority. 2. Given (1), It will be a major effort to undertake a conversion. This is the whole point of adding extra 'features' to data base servers, and every vendor does it. 3. Given (12), I think 18K euros wouldn't even cover the scoping and design phases. So why quote a low ball figure? In my experience I have found this is often done to get the work in the first place. Once you have committed to their product, you will find that the price of conversion just keeps going up until you eventually capitulate and accept their product as is. Alternatively they naively believe they can do the work and go broke trying to do the conversion on a fixed price contract (I have actually seen one software development company go broke this way). - Kind Regards Richard Burgmann Consultant E-Mail: [EMAIL PROTECTED] - Original Message - From: Markus Lervik [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 19, 2002 12:16 AM Subject: Porting from MS SQL to MySQL Hello all! We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
softupdates problem?
These two queries were execute one after the other. I am the only one who updates this table. I have seen this happen before, and people have said that perhaps it's a problem with softupdate. I haven't been able to address it. I am on FreeBSD 4.2 FreeBSD 3.23.35 There is quite a bit of load on the machine, but that table is not user updateable (except by me :-)). Anyone see this problem before? - mysql update tblZips set szURL = '/cgi-bin/showPage.cgi?szNextPage=placead.htmlszAction=NEWszURL=MI2' where szURL = '/cgi-bin/index.cgi?url=MI2szAction=goto+site'; Query OK, 0 rows affected (0.09 sec) mysql update tblZips set szURL = '/cgi-bin/showPage.cgi?szNextPage=placead.htmlszAction=NEWszURL=MI2' where szURL = '/cgi-bin/index.cgi?url=MI2szAction=goto+site'; Query OK, 58 rows affected (2.43 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Why I will stay with Microsoft SQL Server
Perhaps, reconsider you're layout. Even with mssql, you are not talking about an easy or cheap situation (you would need to get the advanced version of mssql I believe, which is mucho dinero x 2). Why can't both computers be at the same location (and even run off the same machine, leaving the other machine as a pure web server, no database). Just suggestions. Beyond that, if you really want the synchronicity, go for Oracle, MSSQL won't run on suse and at least you'll be in the same general technology as what you're using currently if you select oracle. -Original Message- From: whiskyworld.de [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 27, 2001 5:08 AM To: [EMAIL PROTECTED] Subject: WG: Why I will stay with Microsoft SQL Server Hi All, well, since the discussion of the benefits Mysql vs. MSSQL has i've come across a real Problem. Even if its a little bit off-topic (but only a little bit...) - perhaps s.b. knows a solution: Ok, here we are: I'm the webmaster of www.whiskyworld.de - an online Store that sells whisky (over 1600 products in common) - its running now on a LAMPS system based on SuSE 7.2 - really fine - last week we received a new Local Server (Dell 1500SC) - (local here in our small Company) - so now ive got the problem: How can i hold the data on the server (located in another part of Germany) up-to-date ? at least the thing is that there are some Products Tables that are newer here local while some other tables are newer on the server (orders and costumer informations) - i've read about REPLICATION in MySQL but this is only if 1 server is always the more up-to-date one - and i need a via versa solution - - new whisky is in - local is newer than server - new order is in - server is newer than local i thought first of a selfprogrammed PHP solution because it then could connect via SSL - in my opinion a secure solution - or ist there a ,well, more comfortable possibility for this problem ? Greetings Korbinian Bachl www.whiskyworld.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql gui over internet
Has anyone used a mysql gui that works securely over the internet (through ssh?). Currently I use ssh to do command line mysql, but I thought that a gui on my desktop might work well. I can't afford a vpn, so that isn't an option. -Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Zip Code proximity search
Currently, I am using the following query: SELECT DISTINCT o.szZipCode FROM tblZips z,tblZips o WHERE z.szZipCode=$szZip AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((z.dblLat-o.dblLat)*0.017453293)/2),2) + COS(z.dblLat*0.017453293) * COS(o.dblLat*0.017453293) * POWER(SIN(((z.dblLon-o.dblLon)*0.017453293)/2),2) $iRadius where $iRadius is the Radius of search and $szZip is the zip code (char) This runs rather slowly over the 76 thousand zip codes in the US. Here are my three solutions: 1) Use a subquery (not an option on 3.23) 2) Compute the max/min latitude/longitude and then query on that range (proximity becomes a square, but that's not a big deal) 3) Since I am using only 3 radius (10,25,50), compute for those three ranges over the entire table and make a new table (I guess around 1.5 million records will be created, so around 350 MB of space) Does anyone have any recommendations (or a better query that doesn't have to use a function for every row). --+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-- --+---+ | szZipCode | varchar(5) | YES | MUL | NULL | | | szCityName | varchar(28) | YES | | NULL | | | szState| char(2) | YES | | NULL | | | szCounty | varchar(25) | YES | | NULL | | | szURL | varchar(255) | YES | | /cgi-bin/showPage.cgi?szNextPage=placead.htmlszAction=NEW | | | dblLat | decimal(4,2) | YES | MUL | NULL | | | dblLon | decimal(4,2) | YES | MUL | NULL | | ++--+--+-+-- --+---+ 7 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
