Re: Performance over a network
Another issue that is easy to overlook is your indexing. If you have a lot of indexing and keep increasing the database, expect to see a significant slowdown during inserts into a well populated db, compared to an empty one. Since you were trying on local machine first, and networked later, it could be the case. niclas On Saturday 26 October 2002 13:11, [EMAIL PROTECTED] wrote: 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. Ooops, my bad. The inserts are bulked up into chunks of 1000 (it was 100, but I increased the number to see if it cured the problem - doesn't, but the insertions are faster as I would have expected), and there are up to 100,000 of them. Agree about the full/half duplex issue. I believe that they're both full, but will check. 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. That's true, I hadn't thought that option through completely. Using import files would help fix another problem with the current design that I've managed to produce in testing with lots of data (150,000 inserts per 30 seconds). If the child process doesn't complete in 30 seconds, children back up on the server, and eventually the HEAP table gets full. The system takes care of that automatically, but there are previously created children trying to use a full table who don't realise that there is another available. The design is a little flaky at that point. Paul Wilson Chime Communications - 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: MySQL processes..
On Thursday 17 October 2002 19:33, Sandeep Murphy wrote: Our JSP application has connections to both Oracle and MySQL... In each page there are nearly a dozen queries executing on a MySQL db. at the begining of each page, a connection is opened and at the end they are close.. Ideally, if the user waits for the page to load completely and then hit the button Next or Cancel, the connection is freed.. thnx for any suggestions.. Give up JSP ... Move the DB code to servlets, where you have better control. - 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: JDBC problems
Expanding the zip into the lib/ext/ is perhaps not the best idea.
Expand it elsewhere, and copy the JAR file to the lib/ext/ folder.
Also, you don't need to make an instance of the Driver. The static initializer
will register the class with the DriverManager.
Niclas
On Thursday 17 October 2002 21:25, Lee Denny wrote:
I'm trying to use the mm.mysql JDBC drivers on windows NT
I've just downloaded the mysql-connector-java-2.0.14.zip file and extracted
it into my jdk1.2.2/jre/lib/ext folder.
I try to connect with
import java.sql.*;
public class dbconnect
{
public static void main(String[] args)
{
String url = jdbc:mysql://mydomain.co.uk/mydatabase;
String username = ;
String password = ;
// Load database driver if not already loaded
try
{
Class.forName(org.gjt.mm.mysql.Driver).newInstance() ;
Connection connection = DriverManager.getConnection(url,
username, password);
...
And get the error 'ClassNotFoundException'.
I've tried the class 'com.mysql.jdbc.Driver' as suggested by the
documentation in the .zip file but this gives the same error. I'm obviously
putting the class files in the wrong place - but this is my first crack at
Java so need help.
Thanks in advance,
Lee
-
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: Wierd login problem
On Thursday 17 October 2002 05:36, Bill Leath wrote: I am trying to use mysql. I use the line mysql -u username -p and then enter the correct password The error message I get is error 1045 access denied for user: username@localhost (Using password: YES) I know that I am using the correct username and password, because an application using jdbc uses the same username and password and can login. mysql probably tries to use UNIX sockets, while the JDBC app uses TCP/IP sockets, and you have the former not valid for connections. try mysql -h localhost -u niclas -p and if that doesn't work, you probably only have the FQDN as the hostname in the database, so mysql -h niclas.hedhman.org -u niclas -p Either one should do it. Also, does the JDBC app run from the same host/IP as the mysql client? If not, then you may have client host connection limits. Niclas - 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: a mysql question
Sorry, but it is hard to understand what you are actually asking, but I'll try. If you are returning too large resultset, it is possibly a JDBC driver problem, since it will retrieve all into Java objects, which could take forever. If you know your SELECT statement, just issue it in the mysql client, and you will know the number of records returned as well as how long it takes. But I think you are trying to say something about multi-threading on connections. If I am not mistaken, you are not allowed to re-use the same connection with more than one query at a time. You either need an exclusion mechanism or a connection pool to distribute available connections among users, i.e. different threads. Niclas On Thursday 17 October 2002 13:36, µÑ ¶Ì wrote: I'm a chinese software engineer, and I has use mysql for three years ago.Now,I get a matter from it. recently,I'm deal big data use mysql first.every table's data is one million,so user search the data will use long time.now ,if some user search one table togeter,the table is dead , and I find the connection to the mysql cann't close,my God,I only restart it. my develop language is JAVA.But I can't think the error is in JAVA code.my question is wheather mysql can't support a lot of connection search one table together? I want get a help or an advance for you ,thanks. _ Do You Yahoo!? ÐÂÏʵ½µ×,ÓéÀÖµ½¼Ò - ÑÅ»¢ÍƳöÃâ·ÑÓéÀÖµç×ÓÖܱ¨! http://cn.ent.yahoo.com/newsletter/index.html - 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
Fwd: Custom Indexing
Where can I find info about HOW-TO write my own indexing subsystem for MySQL?? Or perhaps an example, or even the full thing. I need a sphere indexer, which finds spheres overlapping a search sphere. Niclas = QUERY - 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: query help
Maybe I'm way out here, but this happens to me (or similar) quite often. My solution; SELECT DISTINCT And a single row will be returned for each. Niclas On Tuesday 08 October 2002 21:48, Michael Knauf/Niles wrote: Here's a query for ya: SELECT products.product_name, products.fg_number, products.product_description, specs.spec_name, specs.spec_value FROM products LEFT JOIN specs ON products.fg_number = specs.fg_number WHERE products.fg_number='fg00914'; and here's the result: +--+---++-- +--- + | product_name | fg_number | product_description| spec_name| spec_value +--+---++-- +--- + | | fg00914 || Woofer | 8 injection-molded TCC (talc) test | | fg00914 || Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | | fg00914 || Recommended amplifier power | 10 to 150 watts | | fg00914 || Frequency | 40Hz-21kHz +/- 3dB | | fg00914 || Sensitivity | 90dB For 2.83V pink noise | | fg00914 || Impedance | 4 or 8 ohm selectable | | fg00914 || Dimensions | 10-5/8 diameter | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Woofer | 8 injection-molded TCC (talc) test | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Recommended amplifier power | 10 to 150 watts | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Frequency | 40Hz-21kHz +/- 3dB | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Sensitivity | 90dB For 2.83V pink noise | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Impedance | 4 or 8 ohm selectable | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Dimensions | 10-5/8 diameter +--+---++-- +--- + 14 rows in set (0.00 sec) I get 7 rows of data I don't want, then the 7 rows of data I do want... and after scratching my head for awhile, I've come to the conclusion that I do not understand the query structure. (the query came from multiple suggestions in response to my last posting on this list, so clearly it makes sense to some of you...) Can somebody point me to a resource on complicated multi-table select statements? I have the Paul DuBois MySQL book, which got me from 0 to where I am now, but isn't getting me past this particular query. Or maybe just explain how to get the last 7 rows without the first? --it's duplicating the same data with and without the product_name and product_description fields. Here's the table structure: mysql describe products; +-+--+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra +-+--+--+-+-+-- --+ | id | int(6) | | PRI | NULL| auto_increment | | fg_number | varchar(9) | | MUL | 0 | | | product_name| varchar(64) | | MUL | | | | product_description | varchar(255) | | | | +-+--+--+-+-+-- --+ 4 rows in set (0.00 sec) mysql describe specs; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(12) | | PRI | NULL| auto_increment | |
Re: information about the tables in the database
On Wednesday 09 October 2002 21:43, Inbal Ovadia wrote: Hi all, There is a way to know if I have table x in my db? Let's say some catalog tables that hold information about the db like tables name etc. and i can do query like select tables from catalogName and get all the tables in the db Talking about programmatically? What client are you using? Since the JDBC driver can pick up all the database, table and column information, it should be available reasonably easy in other clients as well. - 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: Creating a DataSource and Connection in Tomcat for MYsql Database
On Tuesday 08 October 2002 22:29, Arul wrote: Hi All I had created a DataSource in Tomcat Admin Console. But i am not getting a connection ds.getConnection() results in error. The error is java.sql.SQLException : cannot load JDBC Driver class null But while creating the datasource i had given the driverclassname as org.gjt.mm.mysql.Driver You say name, are you talking about a String name, or the class itself? What could be going wrong If you can load the class explicitly, Class c = org.gjt.mm.mysql.Driver.class; You probably have a security issue. But the error message is pointing in the more like direction that the class is not available to the classloader. It depends on your Tomcat setup where you should place it. If you have a permanent use of MySQL in your Tomcat applications, you should perhaps keep a centralized copy in $TOMCAT_HOME/lib/. If not, it should be inside the WAR file's WEB-INF/lib/ directory. Good luck. - 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: RES: Performance Question.
On Monday 07 October 2002 11:32, Robert H.R. Restad wrote: Thanks for doing the test :) Don't forget that MySQL is the fastest thing on the planet for your (and mine) kind of simple query and insert/updates. We measured 12 times faster performance in MySQL than Oracle in one part of our application, and I strongly believe that in the MySQL case the Java program overhead became significant, but that was never measured. Niclas - 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't connect to local MySql server through socket '/var/lib/mysql/mysql.sock' (111)
On Monday 07 October 2002 12:04, Edwin Raj wrote: When I type mysql in the command prompt it is giving the error ERROR 2002: Can't connect to local MySql server through socket '/var/lib/mysql/mysql.sock' (111) There is no file mysql.sock at that location??? Exactly. What is the problem amd what should I do to make it run? I didn't install MySql on my own and it was a default built on the OS installation. I don't know your installation in details, but I assume it is probably configured for TCP/IP connections only, so you would need the -h option. mysql -h my.computer.com -u me -pSecret If you try to connect with localhost, it again depends on your setup of the OS if that will work properly. Finally, the admin tables in the mysql database needs to be correctly setup, and again it should be done immediately at installation. If you follow the installation section in the MySQL manual, and connect over the full hostname, you will probably be ok. Niclas - 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: need a better book
On Monday 07 October 2002 15:53, David Lloyd wrote: Please let me know the name of a book(s) that teaches how these complex select statements work. How about the MySQL reference manual??? Keep on listening to the statements made here, especially the ones you don't understand. And try to answer them (not necessarily posting the answer to the list) and it will help you understand the queries. Also called Learning by Teaching., great concept. - 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: need a better book
On Monday 07 October 2002 11:09, John Jacques wrote: Hello, I have a few SQL books, but they only have simple SQL queries in them. I signed up with the list a few days ago and I have seen select statements that do amazing things. I went to the book store and they don't have any books with these types of select statements in them. Please let me know the name of a book(s) that teaches how these complex select statements work. How about the MySQL reference manual??? Honestly, the variety of complex statements makes it near impossible to have a how-to book in the subject. My approach is two-fold; 1. Understand the reference manual of how the SQL statements can build up. 2. Look at real code, either handcoded, or better yet, query building tools. Niclas - 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
Fwd: Re: OT: Reply-Adress in this list
I did it again!!! -- Forwarded Message -- Subject: Re: OT: Reply-Adress in this list Date: Fri, 4 Oct 2002 10:31:28 +0800 From: Niclas Hedhman [EMAIL PROTECTED] To: Dan Nelson [EMAIL PROTECTED] On Thursday 03 October 2002 22:23, Dan Nelson wrote: In the last episode (Oct 03), Franz Alt said: When I reply, some lists set their list-adress automatically, some the adress of the person originally posting. The second version may not so good IMHO, because I think so many replies are not going to the lists. mysql++ and mysql do not automatically reply to the list mysql-de does. Use the reply-to-all or List-reply button, whichever Outlook has. Easier to say than remember if you have (like I do) 15 mailing lists and a couple of different behaviours. I don't care which becomes industry standard, as long as the behaviours converge, reducing what I have to remember. There is actually one good thing about replying to the list, instead of the individual; The list can manage to remove invalid email addresses and out-of-office replies (not that all lists do that, but could). Niclas --- - 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: Reply-to munging
On Thursday 03 October 2002 22:33, Michael T. Babcock wrote: Franz Alt wrote: From one site: Reply-To munging does not benefit the user with a reasonable mailer. People want to munge Reply-To headers to make ``reply back to the list'' easy. But it already is easy. Reasonable mail programs have two separate ``reply'' commands: one that replies directly to the author of a message, and another that replies to the author plus all of the list recipients. Even the lowlyBerkeleyMail http://www.bsdi.com/bsdi-man/?Mail%281%29 command has had this for about a decade. Also: It, in fact, /decreases/functionality. Reply-To munging destroys the ``reply-to-author'' capability. Munging makes this command act effectively the same as the ``reply-to-group'' function. We haven't added anything new, we've only taken away. Reply-To munging is not merely benign, it is harmful. It renders a useful mail capability inoperative. I don't by the less functionality argument any more than if you have a good emailer. I think I have a good emailer, and on the munging lists, I press Reply-All, it will also send to the original author, but 95% of the case I want the list, and original author doesn't want two replies. PS, this has nothing to do with SQL or QUERIES ... :) Completely agree, have to change the implementation of the OT here. INSERT INTO MailingList ( Features ) VALUES ( 'Munge the Reply-To header.' ); My question is, can this made work at the lists.mysql.com DB? ;o) Better? - 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
WHERE clause efficiency
If I have a table, Name VARCHAR(100) L DOUBLE a DOUBLE b DOUBLE and execute the incredible SELECT * FROM Colors WHERE SQRT( SQ( ABS( L - 45.5 ) ) + SQ( ABS( a - 13.2 ) ) + SQ( ABS( b + 23.4 ) ) ) 2.5 ; Could MySQL benefit in any way by indexing the L,a,b fields? Or will it just plough through all the records one by one, anyway? Niclas - 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: WHERE clause efficiency
On Friday 04 October 2002 11:04, Paul DuBois wrote: At 10:43 +0800 10/4/02, Niclas Hedhman wrote: If I have a table, Name VARCHAR(100) L DOUBLE a DOUBLE b DOUBLE and execute the incredible SELECT * FROM Colors WHERE SQRT( SQ( ABS( L - 45.5 ) ) + SQ( ABS( a - 13.2 ) ) + SQ( ABS( b + 23.4 ) ) ) 2.5 ; Could MySQL benefit in any way by indexing the L,a,b fields? Or will it just plough through all the records one by one, anyway? It has to read each row in order to evaluate the expression. An index will do you no good. How could it be otherwise? Well, in WHERE L 2.5 an index can help, which is equal to WHERE L - 2.5 0, so how much formula reversion does it manage? None, some or a lot? SQRT( a + b + c ) 2.5 a + b + c 6.25 SQ( a' ) + SQ( b' ) + SQ( c' ) 6.25 is only true if and only if -2.5 a' 2.5, and -2.5 b' 2.5, and -2.5 c' 2.5 -2.5 ABS( a ) 2.5, means that a must be 0 a 2.5, and so on I can apply logic, since I am a thinking being, but I was wondering if MySQL was smart enough to do this in a generic formula reversal/optimization thing. I realize that I probably have to resort to custom code hooked into MySQL to do what I want in a really optimized way, but... Niclas - 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
