Use of MySQL with large tables
We are encountering two issues when using MySQL with large tables (by large, we're talking 1 million rows). Our application is written in Java and we are using the mm.mysql JDBC driver. We run our applications using both Oracle and MySQL. Below are issues that we have experienced. 1. The mm.mysql driver caches all results from a large selection in memory before returning the ResultSet to the calling method. This means that any select returning a lot of rows runs the risk of getting an OutOfMemoryError. Oracle doesn't have this problem, as the ResultSet only contains some of the rows matching the query, and more are retrieved as ResultSet.next() is called. One of our developers has already discussed the issue with Mark Matthews, and the impression seems to be that MySQL does not support true cursors. Once you start a query, you must read the entire result set before issuing any other queries. The lack of cursors seems to really limit what types of applications can be implemented using MySQL as a backend. Are there any discussions regarding the implementation of cursors which would support canceling and scrolling, or is the solution to just keep adding memory (we're running on a PII 450 w/ 128MB RAM)? 2. After we incurred problem number 1, we decided to work around it by using the LIMIT directive and retrieving the entire table in chunks of 25000 rows by issuing a series of queries. Unfortunately, each LIMIT query takes longer and longer to return results, which means the overall select proceeds in exponential time. As an example, we tried to query all of the rows for a table containing 18 columns with 1.8 million rows (on a PII 450 w/ 128M). A partial summary of the results is in the following table: Selected rows Time for this selectCumulative Time 0-24999 00:00:2900:00:29 25000-4 00:00:3000:00:59 5-74999 00:00:3200:01:31 ... 50-524999 00:05:5201:01:04 525000-54 00:06:0301:07:07 ... 100-1024999 00:11:1003:50:07 ... 1775000-179 0:27:50 14:15:34 Is this phenomena expected behavior and will it be addressed sometime in the future, or is it just the way it goes? Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. - 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: BDB table corruption when inserting/retrieving a BLOB into a BDB table
We have also experienced the same exact behavior, also using the mm.mysql
driver, on Linux. However, our problem is intermittent. There are times
(though these times cannot be accurately predicted) where the data is
retrieved correctly, which implies that the data is stored correclty. We
have verified that the data is being stored correctly by using mysql to
select the data into a dumpfile. We did not try to recreate the problem
with a table handler other than BDB.
Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]
This email is intended for the named recipient(s) only and may contain
information that is privileged and/or confidential. Nothing in this
email is intended to constitute a waiver of any privilege or the
confidentiality of this message. If you have received this email in
error, please notify me immediately by reply and delete this message.
-Original Message-
From: Scott McCool [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 13, 2001 1:01 PM
To: '[EMAIL PROTECTED]'
Subject: BDB table corruption when inserting/retrieving a BLOB into a
BDB table
I'm getting a very strange results all of the sudden when dealing with BLOBs
in a mysql 3.23.32 on a Solaris 7 (SPARC) machine (inserts/deletes handled
with the JDBC driver from http://mmmysql.sourceforge.net/)
If I do the following:
1) Create a BDB table with a blob column:
CREATE TABLE test4(
id INTEGER not null PRIMARY KEY,
ablob BLOB null
) TYPE=BDB;
2) Insert a particular blob into that column (I'm storing XSLT stylesheets
in the real table, most of them work fine but one in particular is all of
the sudden causing problems):
---code snip---
String fn="badblob.txt";// Filename of the blob I'm loading that
causes problems
FileInputStream style_fis=new FileInputStream(fn);
byte[] style_bytes=new byte[style_fis.available()];
style_fis.read(style_bytes);
String dQuery="delete from ttest4";
String iQuery="insert into ttest4(id,ablob) values(1,null);";
conn=cp.getConnection();// Gets a JDBC connection from a pool I
keep elsewhere
Statement stmt=conn.createStatement();
stmt.executeUpdate(dQuery);
stmt.executeUpdate(iQuery);
stmt.close();
String uQuery="update test4 set ablob=? where id=1";
pstmt=conn.prepareStatement(uQuery);
pstmt.setBytes(1,style_bytes);
pstmt.execute();
pstmt.close();
cp.releaseConnection(conn);
---end code snip---
(I've modified this some, but basically my code will insert an empty row
then update it with the blob, this is to deal with some issues we've had
with other RDBMs' implementations of JDBC).
This seems to go just fine.
3) Retrieve that blob with the following code:
--code snip--
conn=cp.getConnection();
Statement stmt=conn.createStatement();
String query="select ablob from ttest4 where id=1";
ResultSet rs=stmt.executeQuery(query);
while(rs.next()) {
Blob b=rs.getBlob(1);
byte[] blobBytes=b.getBytes(0,(int)b.length());
System.out.println(new String(b));
}
--end code snip--
At this point my output ends up very garbled. The output usually starts off
with some very strange ASCII characters (seemingly binary data) with things
like filesystem names thrown in the mix... Then at some point parts of my
actual data (from the "badblob.txt" file in step 2) appears... Then the end
of it is usually overwritten with more strange ASCII characters.
In attempting to fix this problem, I dropped and recreated the entire
database a few times, restarted the server daemon, etc. I finally ended up
changing the table to TYPE=MyISAM and the problem went away. Unfortunately,
I need transaction support for this project.
I've been using this code, data, server version, bdb table, etc for a few
weeks now with no problems, but suddenly when regenerating the schema last
night this started to occur and is now happening regularly.
I'd like to blame this on the particular blob I'm inserting (badblob.txt)
but that doesn't seem to be the problem as it hasn't changed. My other
thought was a corrupt disk, but that doesn't seem likely... I'm open to any
suggestions as this problem seems potentially very serious.
Any help is greatly appreciated!
-Scott
-
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?
BDB tables on Linux
I've installed 3.23.33 on both Solaris and Linux RH 6.2 using the tarball for Solaris and the RPM for Linux. The BDB tables work as expected on Solaris, but they did not not work under Linux. What I'm seeing is that I can create a BDB just fine. However, attempting a rollback results in: ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back When I do a mysqldump, the ddl for the table shows a type of MyISAM. I've seen posts in the mail-list that people have used BDB tables under Linux, so I know they should work. I read the installation notes and did not see anything special about post-installation for Linux and BDB support. I found in the documentation that the binary version for Windows does not have BDB tables pre-compiled in, but did not find a similar note regarding Linux. Is there something I need to do (short of building from source) to get BDB tables to work under Linux? Thanks Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. - 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: BDB tables on Linux
Yes, I specified that the table should use the BDB table handler, but it does not appear that the table was created as a BDB table. The following are the results of my sessions on Solaris and Linux: Solaris: mysql create table test (x int) type = BDB; Query OK, 0 rows affected (0.46 sec) mysql set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.00 sec) mysql select * from test; Empty set (0.06 sec) mysql Linux: mysql create table test (x int) type = BDB; Query OK, 0 rows affected (0.01 sec) mysql set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql insert into test values (1); Query OK, 1 row affected (0.00 sec) mysql rollback; ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled b ack mysql select * from test; +--+ | x| +--+ |1 | +--+ 1 row in set (0.00 sec) mysql Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:29 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 11:27:54AM -0600, Stephen Faustino wrote: I've installed 3.23.33 on both Solaris and Linux RH 6.2 using the tarball for Solaris and the RPM for Linux. The BDB tables work as expected on Solaris, but they did not not work under Linux. What I'm seeing is that I can create a BDB just fine. However, attempting a rollback results in: ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back When I do a mysqldump, the ddl for the table shows a type of MyISAM. This suggestst that they weren't created as BDB tables. Can you verify that they're created properly? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: BDB tables on Linux
show variables showed that have_bdb is no, and none of the bdb variables are there. So does this mean that BDB is NOT compiled into the binary included in the Linux RPM, or does it mean that I need to add the necesary BDB-related variables to /etc/my.cnf? Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. -Original Message- From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 05, 2001 3:51 PM To: Stephen Faustino Cc: '[EMAIL PROTECTED]' Subject: Re: BDB tables on Linux On Mon, Mar 05, 2001 at 03:44:20PM -0600, Stephen Faustino wrote: Yes, I specified that the table should use the BDB table handler, but it does not appear that the table was created as a BDB table. Are you *sure* you're running a version with BDB support compiled in? The output of SHOW VARIBLES will probably tell you. Having not used them, though, it's hard to say... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
Temporary tables, BDB tables, and Innobase questions
1. Can temporary tables be of type BDB? Now before the question is asked as to why I need transactional support on a temporary table, let me first say that I really don't need it; this is just something we stumbled across during development. If we create a temporary table of type BDB and attempt to rollback a transaction, we get an error during rollback and the data is not rolled back. However, doing the exact same transaction on a BDB table that is not specified as being temporary work as expected, ie, a rollback undoes the updates. 2. What is the status of BDB tables on Win32? Is there a timeframe as to when a version that runs on Win32 will be made available? 3. I've read several posts regarding the Innobase tables. Where can I download the source from to start trying to use Innobase rather than BDB tables? Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] This email is intended for the named recipient(s) only and may contain information that is privileged and/or confidential. Nothing in this email is intended to constitute a waiver of any privilege or the confidentiality of this message. If you have received this email in error, please notify me immediately by reply and delete this message. - 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 Generate The SELECT statement I need
A small correction: if you want "Jo" to match John or LittleJohn, you need to do: SELECT * FROM customers WHERE name LIKE "%Jo%" Stephen L. Faustino Senior Software Engineer SecureLogix Corporation -Original Message- From: Craig Atkins [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 18, 2001 9:13 AM To: '[EMAIL PROTECTED]' Subject: How To Generate The SELECT statement I need Hi, I need to generate a select statement that has a comparison in it. I have checked the FAQ to no avail. I want to write something like this: SELECT * FROM customers WHERE name =~ 'Jo' which uses the =~ comparison (used in Perl) to return any records that contain the letters 'Jo', which could mean "John" or "LittleJohn" How can I do this in MySQL? How can I make it case sensitive or insensitive. Thank You For Your Help, Craig Atkins * This e-mail and its contents are confidential and are for the use of the intended recipient only. If you are not the intended recipient, please notify the sender immediately. The opinions, statements and thoughts expressed in this email are only those of the individual sender. * - 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
