Populating a from a database

[David Rodman]

Here's a way to do it with PHP:

function field_select($table, $field)
{$result = mysql_query("SELECT $field FROM $table") or
die(mysql_error());
 print "\n";
 $limit = mysql_num_rows($result);
 for($i = 0; $i < $limit; ++$i)
 {list($value) = mysql_fetch_array($result);
  print "$value\n";
 }
 print "\n";
}



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query Help

[Michael Baerwolf]

Hello,
I'm having some problems with a join I've been trying. Here's the table 
structure

CREATE TABLE `business` (
  `b_id` int(5) NOT NULL auto_increment,
  `b_name` varchar(100) default NULL,
  `b_contact` varchar(100) default NULL,
  `b_address` varchar(100) default NULL,
  `b_city` varchar(50) default NULL,
  `b_state` char(2) default NULL,
  `b_zip` varchar(25) default NULL,
  `b_phone` varchar(20) default NULL,
  `b_fax` varchar(20) default NULL,
  `b_dcn` varchar(10) default NULL,
  PRIMARY KEY  (`b_id`)
) TYPE=MyISAM;
CREATE TABLE `packages` (
  `p_id` int(5) NOT NULL auto_increment,
  `p_name` varchar(75) default NULL,
  PRIMARY KEY  (`p_id`)
) TYPE=MyISAM;
CREATE TABLE `records` (
  `r_id` int(5) NOT NULL auto_increment,
  `r_b_id` int(5) default NULL,
  `r_p_id` int(5) default NULL,
  `r_sold` tinyint(1) default NULL,
  `r_date` year(4) default NULL,
  PRIMARY KEY  (`r_id`)
) TYPE=MyISAM;
I've worked out this to retrieve records based on a package id and year.
SELECT * FROM records AS r
LEFT JOIN business AS b on (b.b_id=r.r_b_id)
LEFT JOIN packages AS p on (p.p_id=r.r_p_id)
WHERE p_id='16' AND r_date = '2004';
Here's where I'm having trouble. I also need to retrieve all the 
businesses not in the results of the above query. I've been playing 
around with this for a couple of days now with no luck. Any help would 
be greatly appreciated.

Thanks in advance,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Using SQL query to populate a html form select field

[Kyle Texan]

I want to take 1 field in a mysql table and use that
information to populate an html form select field
instead of writing the html code, that way when data
changes in that mysql field the form will always be in
sync with the table?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: load data into 2 tables and set id

[J S]

Figured it out! Took a gamble and run the below command!
SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN internet_usage iu
   ON iu.urlid=uv.urlid
INNER JOIN url_servers us
   ON us.id=uv.url_server_ID
INNER JOIN url_paths up
   ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
   ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
   ON usc.id=uv.url_scheme_ID
WHERE iu.uid="u752359";
Hi Shawn,
I wondered if you might be able to help me with an SQL query.
I want to list all the internet sites I've surfed in my database.
Here's a query that matches the url with a urlid:
SELECT concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN url_servers us
   ON us.id=uv.url_server_ID
INNER JOIN url_paths up
   ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
   ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
   ON usc.id=uv.url_scheme_ID
AND uv.urlid=10023;
And that works really quickly.
However I want to run this for each urlid matching my uid in table 
internet_usage.

mysql> desc internet_usage;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| uid   | varchar(10)  | YES  | MUL | NULL|   |
| time  | datetime | YES  | | NULL|   |
| ip| bigint(20)   | YES  | | 0   |   |
| urlid | mediumint(9) |  | | 0   |   |
| size  | int(11)  | YES  | | 0   |   |
+---+--+--+-+-+---+
So maybe it's something like:
SELECT iu.time, INET_NTOA(iu.ip), 
concat(usc.scheme,"://",us.server,up.path)
FROM internet_usage us, url_visit uv
WHERE iu.uid="u752352"
INNER JOIN url_servers us
   ON us.id=uv.url_server_ID
INNER JOIN url_paths up
   ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
   ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
   ON usc.id=uv.url_scheme_ID

But I haven't tried this because my tables are very big and I didn't want 
to do the wrong join!

Thanks for any help you can offer.
js.
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: load data into 2 tables and set id

[J S]

Hi Shawn,
I wondered if you might be able to help me with an SQL query.
I want to list all the internet sites I've surfed in my database.
Here's a query that matches the url with a urlid:
SELECT concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN url_servers us
   ON us.id=uv.url_server_ID
INNER JOIN url_paths up
   ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
   ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
   ON usc.id=uv.url_scheme_ID
AND uv.urlid=10023;
And that works really quickly.
However I want to run this for each urlid matching my uid in table 
internet_usage.

mysql> desc internet_usage;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| uid   | varchar(10)  | YES  | MUL | NULL|   |
| time  | datetime | YES  | | NULL|   |
| ip| bigint(20)   | YES  | | 0   |   |
| urlid | mediumint(9) |  | | 0   |   |
| size  | int(11)  | YES  | | 0   |   |
+---+--+--+-+-+---+
So maybe it's something like:
SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,"://",us.server,up.path)
FROM internet_usage us, url_visit uv
WHERE iu.uid="u752352"
INNER JOIN url_servers us
   ON us.id=uv.url_server_ID
INNER JOIN url_paths up
   ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
   ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
   ON usc.id=uv.url_scheme_ID
But I haven't tried this because my tables are very big and I didn't want to 
do the wrong join!

Thanks for any help you can offer.
js.
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Which constraint failed?

[David Rodman]

When an INSERT operation returns an error 1216 (parent row does not 
exist, FK constraint failed), and there are multiple FK constraints in the 
target table, how do you know which one failed?  Brute force method will 
work, of course (do a select on each foreign key in its referenced table).  
But aside from being a little hairy, parsing the DESCRIBE output, if I'm going 
to do all that work why use foreign key constraints in the first place?  
What am I missing? (Please don't say RTFM) (at least, not without a page number) 
:-)
Aloha 
-David
 

Re: Optimising SQL Statements

[Martin Gainty]

Thanks Shawn!
Marty Gainty
(cell) 617-852-7822


From: [EMAIL PROTECTED]
To: "Martin Gainty" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: Optimising SQL Statements
Date: Fri, 25 Jun 2004 12:59:28 -0400
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc3-f2.hotmail.com with 
Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 10:04:59 -0700
Received: (qmail 20525 invoked by uid 109); 25 Jun 2004 17:02:10 -
Received: (qmail 20505 invoked from network); 25 Jun 2004 17:02:09 -
Received: neutral (lists.mysql.com: local policy)
X-Message-Info: JGTYoYF78jEo8BnbnDZRjsg5gtK7dcqN
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: 
Precedence: bulk
List-Help: 
List-Unsubscribe: 

List-Post: 
List-Archive: http://lists.mysql.com/mysql/167912
Delivered-To: mailing list [EMAIL PROTECTED]
X-Mailer: Lotus Notes Release 5.0.8  June 18, 2001
Message-ID: <[EMAIL PROTECTED]>
X-MIMETrack: Serialize by Router on SprucePine/Unimin(Release 5.0.12  
|February 13, 2003) at 06/25/2004 12:59:37 PM
X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 25 Jun 2004 17:05:01.0100 (UTC) 
FILETIME=[8D291EC0:01C45AD6]

Martin,
1) I would insure there is an index on both TABLEY2.y_id and Y_TABLE.y_id
2) I would change the subselect in your insert statement to be a LEFT
JOIN... WHERE xxx IS NULL.
INSERT INTO X(column1)
SELECT Y.y_id
FROM Y_TABLE Y
LEFT JOIN TABLEY2 t2
  ON t2.y_id = Y.y_id
WHERE t2.y_id IS NULL;
2a) If you MUST keep the subselect (due to you reasons beyond your control)
change
  Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
to
  NOT EXISTS(select 1 FROM TABLEY2 y2 where y2=Y.y_id);
3) It may be faster in some circumstances to DROP TABLE/CREATE TABLE than
it would be to TRUNCATE or DELETE. Only testing will prove which works
better for you.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

  "Martin Gainty"
  <[EMAIL PROTECTED]To:   
[EMAIL PROTECTED]
  com> cc:
   Fax to:
  06/25/2004 12:43 Subject:  Optimising SQL 
Statements
  PM



Hello All:
I have a very simple test procedure
PROCEDURE TEST_PROCEDURE AS
BEGIN
DELETE FROM X;
COMMIT;
INSERT INTO X (column1)
SELECT
Y.y_id
FROM
Y_TABLE Y
WHERE
Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
COMMIT;
END;
this very simple procedure takes 5 min 30 sec to complete its very basic
delete and insert operations
Any ideas on how I can optimise
(I used truncate instead of delete and that helped big time)
Vielen Danke,
-Martin
Martin Gainty
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relating to the official
business of Laconia Data Systems (LDS) is proprietary to the company. It is
confidential, legally privileged and protected by law. LDS does not own and
endorse any other content.
(cell) 617-852-7822
(e) [EMAIL PROTECTED]
(http)www.laconiadatasystems.com


>From: "Ron McKeever" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Subject: Location of files
>Date: Fri, 25 Jun 2004 07:47:18 -0700
>MIME-Version: 1.0
>Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com
>with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700
>Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 -
>Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 -
>Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED]
>designates 207.217.120.74 as permitted sender)
>X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m
>Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
>List-ID: 
>Precedence: bulk
>List-Help: 
>List-Unsubscribe:
>
>List-Post: 
>List-Archive: http://lists.mysql.com/mysql/167906
>Delivered-To: mailing list [EMAIL PROTECTED]
>Message-ID: <[EMAIL PROTECTED]>
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
>X-Virus-Checked: Checked
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC)
>FILETIME=[C575A2C0:01C45AC3]
>
>Is there any benefit to having the .MYD files on one drive, and the .MYI
on
>its own dedicated hard drive??
>
>
>rm
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
_
FREE pop-up blocking with the new MSN Toolbar ? get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL

Re: error 27

[Michael Stassen]

J S wrote:
Thanks for your reply. I had to change the IP column to bigint because 
mysql was inserting the wrong value when it was just int.
Are your ips IPv4 (4 byte) or IPv6 (8 byte)?  I'm guessing IPv4.  In that case:
mysql> SELECT INET_ATON('0.0.0.1'), INET_ATON('255.255.255.255');
+--+--+
| INET_ATON('0.0.0.1') | INET_ATON('255.255.255.255') |
+--+--+
|1 |   4294967295 |
+--+--+
1 row in set (0.00 sec)
You need INT UNSIGNED to store an ip.  Using BIGINT, you're wasting 4 bytes 
per row.

If you're using IPv6, then you need BIGINT UNSIGNED to hold them.
Is this the correct command to change to chars ?
ALTER TABLE internet_usage CHANGE uid CHAR(10);
Close.  It's CHANGE old_col_name new_col_name type, so:
  ALTER TABLE internet_usage CHANGE uid uid CHAR(10);
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Using CASE for store values

[SGreen]

Kinda sorta but not really

Your SELECT isn't against a table so I don't what TotalStatus1 and
TotalStatus2 are . Are they fields? Are they variables?

Assuming you have a tables called "stores" and you wanted to update one of
two columns based on a value in a third:

UPDATE stores
SET TotalStatus1 = IF(statusID=1, TotalStatus1+1,TotalStatus1)
  , TotalStatus2 = = IF(statusID=2, TotalStatus2+1,TotalStatus2)

Or if you want to see an incremented value in a SELECT statement:

SELECT statusID
  , IF(statusID=1, TotalStatus1+1,TotalStatus1) as TotalStatus1
  , IF(statusID=2, TotalStatus2+1,TotalStatus2) as TotalStatus2
FROM 

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  ColdFusion Lists 

 cc: 

   Fax to: 

  06/25/2004 05:48 Subject:  Using CASE for store values   

  PM   

   

   





Hi all

it's possible to use the CASE clause in MySQL like this:

SELECT


statusID,


Case(statusID = 1, TotalStatus1=TotalStatus1 + 1)


Case(statusID = 2, TotalStatus2 = TotalStatus2 +1)


It's possible?


How to do that?


Thanx for your time.



Crie seu Yahoo! Mail, agora com 100MB de espaço, anti-spam e antivírus
grátis!
- Message from Alejandro Heyworth <[EMAIL PROTECTED]> on
Fri, 25 Jun 2004 15:48:59 -0400 -
   
  To: [EMAIL PROTECTED]
   
 Subject: Memory to Memory INSERTS 
   

Hi!

I'm looking for a better way to insert large numbers of rows from a client
application that is sampling physical data in real-time.  In our case, we
are using a C "double hipvalues[100]" cyclical array to buffer our
sampled values.

We're currently creating large query strings similar to:

INSERT DELAYED INTO hipjoint VALUES
(hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc...

We would like to continue to insert our values directly from our client app

without first having to dump the data to a temp file and LOAD DATA
INFILEing it periodically.

Any ideas?

Config values of interest:
key_buffer_size = 4G
bulk_insert_buffer_size = 1024M

We are using MySQL 4.1.2.

Thanks.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL Crashing On Index Creation/Select count(*) statement - InnoDB tables

[David Griffiths]

Frank,
We used the 64-bit source to compile 4.0.20, and we used the 32-bit 
binaries.

The problem was tracked down at about 1am - it was the kernel (or the 
SCSI drivers). We put a 3Ware SATA Raid-5 card in, and all the crashes 
went away.

There are 64-bit binaries, but we had some problems with them (the guy 
that initially tried them can't remember the exact issue). You need to 
had a -fPic flag to get them to compile for the Opteron.

The PIC flag is for position-independant code. Google it with Opteron 
and you'll see a bunch of posts on it.

David

Dr. Frank Ullrich wrote:
David,
David Griffiths wrote:
We are in the process of setting up a new MySQL server. It's a 
dual-Opteron (Tyan Thunder K8S motherboard) with 6 gig of DDR333 RAM 
(registered) and an LSI SCSI card with 6 SCSI drives (5 in a RAID-5 
array, with one hot-spare) running SuSE Enterprise 8.1 (64-bit).

I loaded all our data (about 2 gig) into the database back on 
Tuesday, and created the indexes without issue, as a test to see how 
long it would take.

Tonight, we were going to cut over to this new machine. I was setting 
up data as a test run, and started coming across "Database page 
corruption on disk or a failed file read of page" errors.

At first, we were using MySQL 4.0.20 64-bit, compiled from source by 
us (the -fPic option needs to be included in the Makefile, and for 
some reason isn't in the binaries - also, no release notes for the AMD64 

So you can't use the binaries that MySQL provides and therefore you 
didn't test them? Or did you?
Why is this -fPic option important?
I'm curious because we have a dual opteron system too and I wanted to 
install the 64bit binary (4.0.20-standard) from the MySQL web site.

Regards,
   Frank.

platform at http://dev.mysql.com/doc/mysql/en/Linux.html).
I could consistently crash the database by creating an index on a 
column (a varchar(50)). I could also crash it doing a "SELECT 
COUNT(*)..." from a table with 3 million rows. Unfort, I did not save 
the crash-log.

We rolled back to 4.0.18, also 64-bit. Exactly the same issue. Here's 
the output.

- 

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 12244.
InnoDB: You may have to recover from a backup.
040624 17:21:59  InnoDB: Page dump in ascii and hex (16384 bytes):
...
040624 17:21:59  InnoDB: Page checksum 1484130208, 
prior-to-4.0.14-form checksum 1108511089
InnoDB: stored checksum 2958040096, prior-to-4.0.14-form stored 
checksum 1108511089
InnoDB: Page lsn 0 204702464, low 4 bytes of lsn at page end 204702464
InnoDB: Page may be an index page where index id is 0 24
InnoDB: and table yw/boats2 index PRIMARY
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 12244.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: Look also at section 6.1 of
InnoDB: http://www.innodb.com/ibman.html about
InnoDB: forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

- 

InnoDB is robust enough to recover, fortunately.
Then we thought it might be an issue with the 64-bit version, so we 
installed the 32-binary version (we didn't compile it) of 4.0.20.

I managed to make it crash in exactly the same way - adding an index 
to a table, dropping an index, or selecting a count from the same 
large table.
- 

040624 20:29:07  mysqld restarted
040624 20:29:08  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3576655719
InnoDB: Doing recovery: scanned up to log sequence number 0 3576655719
040624 20:29:08  InnoDB: Flushing modified pages from the buffer pool...
040624 20:29:09  InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 23235.
InnoDB: You may have to recover from a backup.
040624 20:29:38  InnoDB: Page dump in ascii and hex (16384 bytes):
040624 20:29:38  InnoDB: Page checksum 1229875638, 
prior-to-4.0.14-form checksum 4263044155
InnoDB: stored checksum 2727822450, prior-to-4.0.14-form stored 
checksum 4263044155
InnoDB: Page lsn 0 748566710, low 4 bytes of lsn at page end 748566710

Using CASE for store values

[ColdFusion Lists]

Hi allit's possible to use the CASE clause in MySQL like this:SELECT
statusID,
Case(statusID = 1, TotalStatus1=TotalStatus1 + 1)
Case(statusID = 2, TotalStatus2 = TotalStatus2 +1)
It's possible?
How to do that?
Thanx for your time.Crie seu Yahoo! Mail, agora com 100MB de espaço, anti-spam e antivírus grátis!--- Begin Message ---
Hi!
I'm looking for a better way to insert large numbers of rows from a client 
application that is sampling physical data in real-time.  In our case, we 
are using a C "double hipvalues[100]" cyclical array to buffer our 
sampled values.

We're currently creating large query strings similar to:
INSERT DELAYED INTO hipjoint VALUES 
(hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc...

We would like to continue to insert our values directly from our client app 
without first having to dump the data to a temp file and LOAD DATA 
INFILEing it periodically.

Any ideas?
Config values of interest:
key_buffer_size = 4G
bulk_insert_buffer_size = 1024M
We are using MySQL 4.1.2.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

--- End Message ---
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: error 27

[J S]

Thanks for your reply. I had to change the IP column to bigint because mysql 
was inserting the wrong value when it was just int.

Is this the correct command to change to chars ?
ALTER TABLE internet_usage CHANGE uid CHAR(10);

Yep, modify the uid from a varchar to a char.  It will make your table
bigger, because char uses all 10 characters.  But it will allow you to get
past the 2 gig limit.  It will take a while for the table to be modified
though.  But it's definitely worth the wait.
Also personally I would change the ip from a bigint to an int, if that is
really an IP address like it seems.
Just my opinion.  No matter what I would make a backup of your data before
making any changes.
Donny
>
> I don't really understand the difference (I need to read up a bit more).
> My
> (default mysql) table internet_usage has the following columns:
>
> uid varchar (10)
> ip   bigint
> time datetime
> urlid int
> size int
>
> Is there something I can do to this to fix it so that it can grow larger
> than 2 GB? The 'uid' is a mix of chars and ints, e.g u752352.
>
>
>
> >
> >Yes, if you are using a dynamic table which means it has varchar's, 
text,
> >or
> >blobs the limit is 2 gigs.  If you are using a fixed table which uses
> chars
> >only, then there is no limit that I have seen.
> >
> >Donny
> >
> > > -Original Message-
> > > From: J S [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, June 25, 2004 9:38 AM
> > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > > Subject: RE: error 27
> > >
> > > Could this problem be due to the size of my tables? Is there a limit
> on
> > > how
> > > big the table can be?
> > > I'm using mysql-standard-4.0.20.
> > >
> > > -rw-rw   1 mysqlmysql2147483647 Jun 25 01:49
> > > internet_usage.MYD
> > > -rw-rw   1 mysqlmysql622724096 Jun 25 01:49
> >internet_usage.MYI
> > >
> > > >
> > > >Hi,
> > > >
> > > >I got an error 27.
> > > >
> > > >DBD::mysql::st execute failed: Got error 27 from table handler at
> > > >/home/u752359/logfile.pl line 144,  line 3079464.
> > > >
> > > >The FAQs say:
> > > >
> > > >Check whether you have hit 2 Gb limit.
> > > >If that is not the case, shutdown MySQL server and repair a table
> with
> > > >(my)isamchk.
> > > >
> > > >How do I check if I have a 2GB limit? I logged on to mysql and ran 
a
> > > select
> > > >from the table successfully. Do I still need to run myisamchk ?
> > > >
> > > >Thanks,
> > > >
> > > >js.
> > > >
> > > >_
> > > >Want to block unwanted pop-ups? Download the free MSN Toolbar now!
> > > >http://toolbar.msn.co.uk/
> > > >
> > > >
> > > >--
> > > >MySQL General Mailing List
> > > >For list archives: http://lists.mysql.com/mysql
> > > >To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> > > _
> > > Stay in touch with absent friends - get MSN Messenger
> > > http://www.msn.co.uk/messenger
> > >
> > >
> > > --
> > > 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]
> >
>
> _
> It's fast, it's easy and it's free. Get MSN Messenger today!
> http://www.msn.co.uk/messenger
>
>
> --
> 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]
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Memory to Memory INSERTS

[Alejandro Heyworth]

Hi!
I'm looking for a better way to insert large numbers of rows from a client 
application that is sampling physical data in real-time.  In our case, we 
are using a C "double hipvalues[100]" cyclical array to buffer our 
sampled values.

We're currently creating large query strings similar to:
INSERT DELAYED INTO hipjoint VALUES 
(hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc...

We would like to continue to insert our values directly from our client app 
without first having to dump the data to a temp file and LOAD DATA 
INFILEing it periodically.

Any ideas?
Config values of interest:
key_buffer_size = 4G
bulk_insert_buffer_size = 1024M
We are using MySQL 4.1.2.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: R: info about MySQL Server

[SGreen]

Alfredo,

It seems funny to me that with 2 servers dealing with 2 sets of data
streams at the same time that you didn't see a performance boost.  This is
leading me to think that it's either the application or the SCSI controller
not keeping up.

It makes sense that if you open up a second server that writes to its own
disk that you should get twice the performance if you were Disk R/W bound
(physical response time from the HD) but your second benchmark didn't show
that.  Since you were never CPU bound, it can't be the servers themselves
acting as the bottleneck.  It has been my experience that if your server is
not maxed out and your disks are not maxed out, that leaves "communication"
and "control" as the remaining suspects.

"Communication" includes the requests from daemon to server and the
server's responses. You already said that your network traffic was not a
factor. "Communication" also involves from the server to the HD (via the
SCSI controller). If you have a single-channel controller, it may be
waiting for one disk to finish writing before telling the other one what to
do. Check to see if your drives each have their own channel or if they
share one. If possible, can you put each drive on their own controller?

"Control" is your daemon application. Can it process enough traffic to
write 35,000+ insert commands per second? If not, there is your culprit.
Have it process your test data again and instead of writing the commands to
the servers, write them to a file. What is the maximum throughput on your
application when writing to a file? Try running top on your daemon server,
too.

I agree with Leonardo's suggestion. Try to determine the max rate of data
inserts for your database servers by using a data file local to the
servers. I would test both of your server instances. Test them alone and
together to see if there is a noticeable difference in the write speed.

Two other things you may be able to try: Modify your daemon application to
use Unix Sockets and run it on the same machine as your database servers.
See if you can compile your application to a stand-alone executable.

I think we are all curious to find out what works for you and what doesn't.
I hope the MySQL developers are monitoring this thread as they could have
better theories than mine.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  "Leonardo

  Francalanci" To:   <[EMAIL PROTECTED]>   

  <[EMAIL PROTECTED]cc:
 
  tel.ie>  Fax to: 

   Subject:  R: info about MySQL Server

  06/25/2004 11:27 

  AM   

   

   





try removing your client for the moment:
can you try a load data infile and see at what speed mysql loads
the rows?


> -Messaggio originale-
> Da: Alfredo Marchini [mailto:[EMAIL PROTECTED]
> Inviato: venerdì 25 giugno 2004 18.21
> A: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED];
> [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Oggetto: Re: info about MySQL Server
>
>
> Hi,
> thank you very much for your reply.
>
> Now I answer you about your additional questions:
>
> Now the daemon is installed on a machine different than DB Server, so I
> use DBServer NIC IP Address for connect to it from the daemon (So I don't
> use Unix Sockets).
>
> But, looking at the Network Bandwidth, there isn't bottleneck (but I have
> read that Unix Socket are more quickly).
>
> My SCSI HD are not in RAID. Actually are fully indipendent and works on a
> RAID controller 160Mb/s (the same speed of the disks).
>
> Probably in the future I'll use RAID tech that improves performance
> (RAID-5, RAID-0+1), but not in this beginning step.
>
> J2SE project source compiled becomes a bytecode (not exe and not source);
> this is runnable only on a Java Virtual Machine (It is like
> half-compiled).
>
> Before decide to develope in J2SE I have made some tests about
performance
> differences between JDBC and MySQL client API
>
> MySQL client API obviously are more quickly, but no so much. Also I have
> read documenta

Re: What is the difference between creating one key with multiple fields, and creating multiple keys, one per field?

[Michael Stassen]

Which is better depends on the sort of queries you will most often run.  The 
short version:

A multi-column index on lname,fname (order matters) would be used for
  SELECT ... WHERE lname='Smith' AND fname = 'Joe';
and
  SELECT ... WHERE lname='Smith';
but could not be used for
  SELECT ... WHERE fname = 'Joe';
That is, the multi-column index works for searches on the column on the left 
(in the index definition) and for searches on both, but not for searches on 
just the column on the right.

You should read the portion of the manual which explains in detail how mysql 
uses indexes:




Michael
Joshua Beall wrote:
Hi All,
I have noticed that in phpMyAdmin, when I create a table and select the
columns I want indexed, if I select two or more columns (say "fname" and
"lname") to be indexed, then when the table is created, instead of two keys,
I have only one key, and it contains both the fname and lname columns.
If I had forgotten to select those fields to be indexed when I created the
table, I would click the "index" link (for both columns) in phpMyAdmin's
structure layout page for that table, and I would then wind up with two
separate keys, one for each column.
As best I can tell, both ways seem to perform the desired task of allowed
faster searching for data in those columns, but I am not sure what the
difference is?  Can anyone enlighten me?  Is one way more "correct?"  What
are the pros and cons?
Thanks for any insights!
Sincerely,
  -Josh

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: OUTFILE to CVS with headers

[Mike Koponick]

Bob, That did the trick!

Thanks!

Mike

-Original Message-
From: Bessares, Bob [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 6:03 PM
To: [EMAIL PROTECTED]; Mike Koponick
Subject: RE: OUTFILE to CVS with headers

well, 
you can easily output into a comma separated file but the INTO OUTFILE
option will not allow you to retain the headers. You will need to build
the logic into a perl script or language of your choice to get the
headers there. Sounds like your fields records are all on one line
becausing you're not ending lines with a new line... try this.
SELECT Fields FROM databasename WHERE (criteria) INTO OUTFILE
'pathtofile/filename.csv' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED
BY '\"' LINES TERMINATED BY '\n'";
i hope this helps...

> --
> From: Mike Koponick
> Sent: Thursday, June 24, 2004 5:51 PM
> To:   [EMAIL PROTECTED]
> Subject:  OUTFILE to CVS with headers
> 
> Hello,
> 
> I'm working on a project where I must export to a text file and the
text
> file needs to be formed into columns so that it can be imported into a
> WORD mail merge document.
> 
> So, the format of the output file has to have the headers for each
> column and the lines must have a CR at the end of each line in order
to
> keep the columns formatted.
> 
> Here is what I'm doing now:
> 
> select created, status,user, comment1,  comment7, comment8, trouble
from
> tbl WHERE customer = 'customer' AND created BETWEEN '2004-05-31' AND
> '2004-06-25' ORDER BY created, status into outfile 'test5.txt' fields
> terminated by ',' optionally enclosed by '"' escaped by '\\';
> 
> All the lines run together so it makes it impossible to import.
> 
> Thanks in advance for your help.
> 
> Mike
> 
> 
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
are
> addressed. Please report errors to [EMAIL PROTECTED] 
> 
> 
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

What is the difference between creating one key with multiple fields, and creating multiple keys, one per field?

[Joshua Beall]

Hi All,

I have noticed that in phpMyAdmin, when I create a table and select the
columns I want indexed, if I select two or more columns (say "fname" and
"lname") to be indexed, then when the table is created, instead of two keys,
I have only one key, and it contains both the fname and lname columns.

If I had forgotten to select those fields to be indexed when I created the
table, I would click the "index" link (for both columns) in phpMyAdmin's
structure layout page for that table, and I would then wind up with two
separate keys, one for each column.

As best I can tell, both ways seem to perform the desired task of allowed
faster searching for data in those columns, but I am not sure what the
difference is?  Can anyone enlighten me?  Is one way more "correct?"  What
are the pros and cons?

Thanks for any insights!

Sincerely,
  -Josh




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Why won't mysql use the index? WAS: strange table speed issue

[MerchantSense]

Hi Sean - thanks for the info, unfortunately, I can't use force index ...too
old a version of mysql

I wonder if there is some way to "trick" mysql into using an index?


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 25, 2004 11:16 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Why won't mysql use the index? WAS: strange table speed issue


Here is what the manual says about the table scan threshold:
(http://dev.mysql.com/doc/mysql/en/Where_optimisations.html)

Each table index is queried, and the best index is used unless the
optimizer believes that it will be more efficient to use a table scan. At
one time, a scan was used based on whether the best index spanned more than
30% of the table. Now the optimizer is more complex and bases its estimate
on additional factors such as table size, number of rows, and I/O block
size, so a fixed percentage no longer determines the choice between using
an index or a scan.

I thought of asking you to try adding the ORG column to your index (to
create a "covering index") but then I read this:

In some cases, MySQL can read rows from the index without even consulting
the data file. If all columns used from the index are numeric, only the
index tree is used to resolve the query.

Since ORG is a varchar , the blurb says it would do a table seek anyway.
Oh, well

from (http://dev.mysql.com/doc/mysql/en/How_to_avoid_table_scan.html):

Use FORCE INDEX for the scanned table to tell MySQL that table scans are
very expensive compared to using the given index.

I can't remember if you tried that yet or not.

Sorry I couldn't be more help,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine,






 

  [EMAIL PROTECTED]

  om   To:
<[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> 
   cc:

  06/25/2004 01:52 Fax to:

  PM   Subject:  RE: Why won't mysql
use the index?   WAS: strange table speed 
issue

 





The general rule of thumb (in Sybase not sure for MySQL), is if using an
index hits more than 10% of the tables total volume it is
faster/cheaper/less evasive on the drive to just table scan the table
opposed to read an index row, get the representing table row pointer and
seek the table for each qualifying row. Your aproximate ration is
.6m/3=20%, again 10% is just a rule of thumb, many other things come
into play...

The reason your "count(*)" used the index is because it doesn't
request/result any data table columns. Since the index and the data
table has the same number of rows and your "where" clause only uses
indexed columns it faster to read/scan the index row because it is "org
varchar(80)" bytes shorter and each disk i/o and can read/buffer more
index rows that table rows in the same size disk read.

My free humble opinion,
Ed

-Original Message-
From: MerchantSense [mailto:[EMAIL PROTECTED]
Sent: Friday, June 25, 2004 10:56 AM
To: [EMAIL PROTECTED]
Subject: Why won't mysql use the index? WAS: strange table speed issue


This is crazy.  If someone can help me out, I'll pay them!

A table:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ip_start | bigint(20)  |  | MUL | 0   |   |
| ip_end   | bigint(20)  |  | | 0   |   |
| org  | varchar(80) |  | | |   |
+--+-+--+-+-+---+

Populated with numbers for the 1st 2 fields,  about 2.9 million records

Indexes as such:
mysql> show index from ip2org;
+++--+--+-+-
--+-
+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+-
--+-
+--++-+
| ip2org |  1 | dex  |1 | ip_start| A
|
2943079 | NULL | NULL   | |
| ip2org |  1 | dex  |2 | ip_end  | A
|
2943079 | NULL | NULL   | |
+++--+-
-+-+---+-+--++--
---+

I do this query:
mysql> explain SELECT org from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++--+---+--+-+--+-+-

---+
| table  | type | possible_keys | key  | key_len | ref  | rows|
Extra
|
++--+---+--+-+--+-+-

---+
| ip2org | ALL  | dex   | NULL |NULL | NULL | 2943079 |
where
used

RE: error 27

[Donny Simonton]

Yep, modify the uid from a varchar to a char.  It will make your table
bigger, because char uses all 10 characters.  But it will allow you to get
past the 2 gig limit.  It will take a while for the table to be modified
though.  But it's definitely worth the wait.

Also personally I would change the ip from a bigint to an int, if that is
really an IP address like it seems.

Just my opinion.  No matter what I would make a backup of your data before
making any changes.

Donny



> -Original Message-
> From: J S [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 25, 2004 1:17 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: error 27
> 
> I don't really understand the difference (I need to read up a bit more).
> My
> (default mysql) table internet_usage has the following columns:
> 
> uid varchar (10)
> ip   bigint
> time datetime
> urlid int
> size int
> 
> Is there something I can do to this to fix it so that it can grow larger
> than 2 GB? The 'uid' is a mix of chars and ints, e.g u752352.
> 
> 
> 
> >
> >Yes, if you are using a dynamic table which means it has varchar's, text,
> >or
> >blobs the limit is 2 gigs.  If you are using a fixed table which uses
> chars
> >only, then there is no limit that I have seen.
> >
> >Donny
> >
> > > -Original Message-
> > > From: J S [mailto:[EMAIL PROTECTED]
> > > Sent: Friday, June 25, 2004 9:38 AM
> > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > > Subject: RE: error 27
> > >
> > > Could this problem be due to the size of my tables? Is there a limit
> on
> > > how
> > > big the table can be?
> > > I'm using mysql-standard-4.0.20.
> > >
> > > -rw-rw   1 mysqlmysql2147483647 Jun 25 01:49
> > > internet_usage.MYD
> > > -rw-rw   1 mysqlmysql622724096 Jun 25 01:49
> >internet_usage.MYI
> > >
> > > >
> > > >Hi,
> > > >
> > > >I got an error 27.
> > > >
> > > >DBD::mysql::st execute failed: Got error 27 from table handler at
> > > >/home/u752359/logfile.pl line 144,  line 3079464.
> > > >
> > > >The FAQs say:
> > > >
> > > >Check whether you have hit 2 Gb limit.
> > > >If that is not the case, shutdown MySQL server and repair a table
> with
> > > >(my)isamchk.
> > > >
> > > >How do I check if I have a 2GB limit? I logged on to mysql and ran a
> > > select
> > > >from the table successfully. Do I still need to run myisamchk ?
> > > >
> > > >Thanks,
> > > >
> > > >js.
> > > >
> > > >_
> > > >Want to block unwanted pop-ups? Download the free MSN Toolbar now!
> > > >http://toolbar.msn.co.uk/
> > > >
> > > >
> > > >--
> > > >MySQL General Mailing List
> > > >For list archives: http://lists.mysql.com/mysql
> > > >To unsubscribe:
> >http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> > > _
> > > Stay in touch with absent friends - get MSN Messenger
> > > http://www.msn.co.uk/messenger
> > >
> > >
> > > --
> > > 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]
> >
> 
> _
> It's fast, it's easy and it's free. Get MSN Messenger today!
> http://www.msn.co.uk/messenger
> 
> 
> --
> 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: Why won't mysql use the index? WAS: strange table speed issue

[SGreen]

Here is what the manual says about the table scan threshold:
(http://dev.mysql.com/doc/mysql/en/Where_optimisations.html)

Each table index is queried, and the best index is used unless the
optimizer believes that it will be more efficient to use a table scan. At
one time, a scan was used based on whether the best index spanned more than
30% of the table. Now the optimizer is more complex and bases its estimate
on additional factors such as table size, number of rows, and I/O block
size, so a fixed percentage no longer determines the choice between using
an index or a scan.

I thought of asking you to try adding the ORG column to your index (to
create a "covering index") but then I read this:

In some cases, MySQL can read rows from the index without even consulting
the data file. If all columns used from the index are numeric, only the
index tree is used to resolve the query.

Since ORG is a varchar , the blurb says it would do a table seek anyway.
Oh, well

from (http://dev.mysql.com/doc/mysql/en/How_to_avoid_table_scan.html):

Use FORCE INDEX for the scanned table to tell MySQL that table scans are
very expensive compared to using the given index.

I can't remember if you tried that yet or not.

Sorry I couldn't be more help,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine,






   

  [EMAIL PROTECTED]

  om   To:   <[EMAIL PROTECTED]>, <[EMAIL 
PROTECTED]> 
   cc: 

  06/25/2004 01:52 Fax to: 

  PM   Subject:  RE: Why won't mysql use the 
index?   WAS: strange table speed 
issue  

   





The general rule of thumb (in Sybase not sure for MySQL), is if using an
index hits more than 10% of the tables total volume it is
faster/cheaper/less evasive on the drive to just table scan the table
opposed to read an index row, get the representing table row pointer and
seek the table for each qualifying row. Your aproximate ration is
.6m/3=20%, again 10% is just a rule of thumb, many other things come
into play...

The reason your "count(*)" used the index is because it doesn't
request/result any data table columns. Since the index and the data
table has the same number of rows and your "where" clause only uses
indexed columns it faster to read/scan the index row because it is "org
varchar(80)" bytes shorter and each disk i/o and can read/buffer more
index rows that table rows in the same size disk read.

My free humble opinion,
Ed

-Original Message-
From: MerchantSense [mailto:[EMAIL PROTECTED]
Sent: Friday, June 25, 2004 10:56 AM
To: [EMAIL PROTECTED]
Subject: Why won't mysql use the index? WAS: strange table speed issue


This is crazy.  If someone can help me out, I'll pay them!

A table:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ip_start | bigint(20)  |  | MUL | 0   |   |
| ip_end   | bigint(20)  |  | | 0   |   |
| org  | varchar(80) |  | | |   |
+--+-+--+-+-+---+

Populated with numbers for the 1st 2 fields,  about 2.9 million records

Indexes as such:
mysql> show index from ip2org;
+++--+--+-+-
--+-
+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+-
--+-
+--++-+
| ip2org |  1 | dex  |1 | ip_start| A
|
2943079 | NULL | NULL   | |
| ip2org |  1 | dex  |2 | ip_end  | A
|
2943079 | NULL | NULL   | |
+++--+-
-+-+---+-+--++--
---+

I do this query:
mysql> explain SELECT org from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++--+---+--+-+--+-+-

---+
| table  | type | possible_keys | key  | key_len | ref  | rows|
Extra
|
++--+---+--+-+--+-+

RE: Optimising SQL Statements

[Matt Chatterley]

Hi Martin,

Speaking more from a general standpoint (I mostly work with MS SQL, but my
home projects are MySQL - these days there is precious little time for
those, though)..

Assuming you have appropriate indexes on tables y and y2:

1. Truncate WILL be faster than delete, as it is a non-logged operation.
Deleting is comparatively slow. If the goal is to remove 'old' records no
longer in the Y set from X, it may be better to just delete those rows no
longer in Y (see below), rather than the whole lot.

2. It may be better to do a left join to tabley (on y_id, and y_id IS NULL),
although I do not know quite how mysql handles these things - for MS, where
not exists / left join where null is quicker than not in.

3. If X is indexed, it may be faster to drop those indexes, bulk-insert
data, and recreate them. This is often better when dealing with large sets
of data, since it is SO much quicker to insert into an un-indexed table and
then create an index, compared with inserting into the table and updating
the index for each row.

Hope this helps!


Matt

> -Original Message-
> From: Martin Gainty [mailto:[EMAIL PROTECTED]
> Sent: 25 June 2004 17:43
> To: [EMAIL PROTECTED]
> Subject: Optimising SQL Statements
> 
> Hello All:
> 
> I have a very simple test procedure
> 
> PROCEDURE TEST_PROCEDURE AS
> BEGIN
> 
> DELETE FROM X;
> COMMIT;
> 
> INSERT INTO X (column1)
> SELECT
> Y.y_id
> FROM
> Y_TABLE Y
> WHERE
> Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
> COMMIT;
> 
> END;
> 
> this very simple procedure takes 5 min 30 sec to complete its very basic
> delete and insert operations
> Any ideas on how I can optimise
> (I used truncate instead of delete and that helped big time)
> 
> Vielen Danke,
> -Martin
> 
> Martin Gainty
> 
> __
> Disclaimer and confidentiality note
> Everything in this e-mail and any attachments relating to the official
> business of Laconia Data Systems (LDS) is proprietary to the company. It
> is
> confidential, legally privileged and protected by law. LDS does not own
> and
> endorse any other content.
> (cell) 617-852-7822
> (e) [EMAIL PROTECTED]
> (http)www.laconiadatasystems.com
> 
> 
> 
> 
> 
> >From: "Ron McKeever" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >Subject: Location of files
> >Date: Fri, 25 Jun 2004 07:47:18 -0700
> >MIME-Version: 1.0
> >Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com
> >with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700
> >Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 -
> >Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 -
> >Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED]
> >designates 207.217.120.74 as permitted sender)
> >X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m
> >Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
> >List-ID: 
> >Precedence: bulk
> >List-Help: 
> >List-Unsubscribe:
> >
> >List-Post: 
> >List-Archive: http://lists.mysql.com/mysql/167906
> >Delivered-To: mailing list [EMAIL PROTECTED]
> >Message-ID: <[EMAIL PROTECTED]>
> >X-MSMail-Priority: Normal
> >X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> >X-Virus-Checked: Checked
> >Return-Path: [EMAIL PROTECTED]
> >X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC)
> >FILETIME=[C575A2C0:01C45AC3]
> >
> >Is there any benefit to having the .MYD files on one drive, and the .MYI
> on
> >its own dedicated hard drive??
> >
> >
> >rm
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> _
> FREE pop-up blocking with the new MSN Toolbar - get it now!
> http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
> 
> 
> --
> 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: error 27

[J S]

I don't really understand the difference (I need to read up a bit more). My 
(default mysql) table internet_usage has the following columns:

uid varchar (10)
ip   bigint
time datetime
urlid int
size int
Is there something I can do to this to fix it so that it can grow larger 
than 2 GB? The 'uid' is a mix of chars and ints, e.g u752352.


Yes, if you are using a dynamic table which means it has varchar's, text, 
or
blobs the limit is 2 gigs.  If you are using a fixed table which uses chars
only, then there is no limit that I have seen.

Donny
> -Original Message-
> From: J S [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 25, 2004 9:38 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: error 27
>
> Could this problem be due to the size of my tables? Is there a limit on
> how
> big the table can be?
> I'm using mysql-standard-4.0.20.
>
> -rw-rw   1 mysqlmysql2147483647 Jun 25 01:49
> internet_usage.MYD
> -rw-rw   1 mysqlmysql622724096 Jun 25 01:49 
internet_usage.MYI
>
> >
> >Hi,
> >
> >I got an error 27.
> >
> >DBD::mysql::st execute failed: Got error 27 from table handler at
> >/home/u752359/logfile.pl line 144,  line 3079464.
> >
> >The FAQs say:
> >
> >Check whether you have hit 2 Gb limit.
> >If that is not the case, shutdown MySQL server and repair a table with
> >(my)isamchk.
> >
> >How do I check if I have a 2GB limit? I logged on to mysql and ran a
> select
> >from the table successfully. Do I still need to run myisamchk ?
> >
> >Thanks,
> >
> >js.
> >
> >_
> >Want to block unwanted pop-ups? Download the free MSN Toolbar now!
> >http://toolbar.msn.co.uk/
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
> _
> Stay in touch with absent friends - get MSN Messenger
> http://www.msn.co.uk/messenger
>
>
> --
> 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]
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Why won't mysql use the index? WAS: strange table speed issue

[emierzwa]

The general rule of thumb (in Sybase not sure for MySQL), is if using an
index hits more than 10% of the tables total volume it is
faster/cheaper/less evasive on the drive to just table scan the table
opposed to read an index row, get the representing table row pointer and
seek the table for each qualifying row. Your aproximate ration is
.6m/3=20%, again 10% is just a rule of thumb, many other things come
into play...

The reason your "count(*)" used the index is because it doesn't
request/result any data table columns. Since the index and the data
table has the same number of rows and your "where" clause only uses
indexed columns it faster to read/scan the index row because it is "org
varchar(80)" bytes shorter and each disk i/o and can read/buffer more
index rows that table rows in the same size disk read.

My free humble opinion,
Ed

-Original Message-
From: MerchantSense [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 25, 2004 10:56 AM
To: [EMAIL PROTECTED]
Subject: Why won't mysql use the index? WAS: strange table speed issue


This is crazy.  If someone can help me out, I'll pay them!

A table:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ip_start | bigint(20)  |  | MUL | 0   |   |
| ip_end   | bigint(20)  |  | | 0   |   |
| org  | varchar(80) |  | | |   |
+--+-+--+-+-+---+

Populated with numbers for the 1st 2 fields,  about 2.9 million records

Indexes as such:
mysql> show index from ip2org;
+++--+--+-+-
--+-
+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+-
--+-
+--++-+
| ip2org |  1 | dex  |1 | ip_start| A
|
2943079 | NULL | NULL   | |
| ip2org |  1 | dex  |2 | ip_end  | A
|
2943079 | NULL | NULL   | |
+++--+-
-+-+---+-+--++--
---+

I do this query:
mysql> explain SELECT org from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++--+---+--+-+--+-+-

---+
| table  | type | possible_keys | key  | key_len | ref  | rows|
Extra
|
++--+---+--+-+--+-+-

---+
| ip2org | ALL  | dex   | NULL |NULL | NULL | 2943079 |
where
used |
++--+---+--+-+--+-+-

---+

And it will not use the index, but if I do this ( a count):
mysql> explain SELECT count(*) from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++---+---+--+-+--++-

+
| table  | type  | possible_keys | key  | key_len | ref  | rows   |
Extra
|
++---+---+--+-+--++-

+
| ip2org | range | dex   | dex  |   8 | NULL | 594025 |
where
used; Using index |
++---+---+--+-+--++-

+

It will use the index.

WHY can't I get it to use the index on a query with siple firlds with
numeric values??  The query takes about 12 seconds   in fact when I
do
the count, it still takes that long (maybe it just *thinks* it's using
the
indexes !).  this should return a value in less than  sec I've
used
tables this big without this problem before... what's up?   No matter
how a
screw around with the indexes, I can't make it work...

Help! :)




-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 11:41 PM
To: Marc Slemko
Cc: MerchantSense; [EMAIL PROTECTED]
Subject: Re: strange table speed issue

I'm not certain, but I don't think a multi-column index will help here.
The

manual is unclear on how a multi-column index is used when you are
comparing

the first key part to a range rather than to a constant, but I get the 
impression it doesn't use the second key part in that case.  For you,
that 
would mean your multi-column index is no better than your single column 
indexes.

The problem is that with either column, the range of matches is large
enough

that the optimizer judges a table scan will be quicker than all those
key 
lookups.  You can see this in the EXPLAIN output, type = ALL and rows =
the 
size of your table.  Both indicate a full table scan.

You may be able to do better if you know something about the ranges
defined 
by ip_start and ip_end, particularly if ip2org is relatively

Re: Optimising SQL Statements

[SGreen]

Martin,

1) I would insure there is an index on both TABLEY2.y_id and Y_TABLE.y_id

2) I would change the subselect in your insert statement to be a LEFT
JOIN... WHERE xxx IS NULL.

INSERT INTO X(column1)
SELECT Y.y_id
FROM Y_TABLE Y
LEFT JOIN TABLEY2 t2
  ON t2.y_id = Y.y_id
WHERE t2.y_id IS NULL;

2a) If you MUST keep the subselect (due to you reasons beyond your control)
change
  Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
to
  NOT EXISTS(select 1 FROM TABLEY2 y2 where y2=Y.y_id);

3) It may be faster in some circumstances to DROP TABLE/CREATE TABLE than
it would be to TRUNCATE or DELETE. Only testing will prove which works
better for you.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  "Martin Gainty"  

  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  com> cc: 

   Fax to: 

  06/25/2004 12:43 Subject:  Optimising SQL Statements 

  PM   

   

   





Hello All:

I have a very simple test procedure

PROCEDURE TEST_PROCEDURE AS
BEGIN

DELETE FROM X;
COMMIT;

INSERT INTO X (column1)
SELECT
Y.y_id
FROM
Y_TABLE Y
WHERE
Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
COMMIT;

END;

this very simple procedure takes 5 min 30 sec to complete its very basic
delete and insert operations
Any ideas on how I can optimise
(I used truncate instead of delete and that helped big time)

Vielen Danke,
-Martin

Martin Gainty

__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relating to the official
business of Laconia Data Systems (LDS) is proprietary to the company. It is

confidential, legally privileged and protected by law. LDS does not own and

endorse any other content.
(cell) 617-852-7822
(e) [EMAIL PROTECTED]
(http)www.laconiadatasystems.com





>From: "Ron McKeever" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Subject: Location of files
>Date: Fri, 25 Jun 2004 07:47:18 -0700
>MIME-Version: 1.0
>Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com
>with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700
>Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 -
>Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 -
>Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED]
>designates 207.217.120.74 as permitted sender)
>X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m
>Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
>List-ID: 
>Precedence: bulk
>List-Help: 
>List-Unsubscribe:
>
>List-Post: 
>List-Archive: http://lists.mysql.com/mysql/167906
>Delivered-To: mailing list [EMAIL PROTECTED]
>Message-ID: <[EMAIL PROTECTED]>
>X-MSMail-Priority: Normal
>X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
>X-Virus-Checked: Checked
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC)
>FILETIME=[C575A2C0:01C45AC3]
>
>Is there any benefit to having the .MYD files on one drive, and the .MYI
on
>its own dedicated hard drive??
>
>
>rm
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

_
FREE pop-up blocking with the new MSN Toolbar ? get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/


--
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]

Why won't mysql use the index? WAS: strange table speed issue

[MerchantSense]

This is crazy.  If someone can help me out, I'll pay them!

A table:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| ip_start | bigint(20)  |  | MUL | 0   |   |
| ip_end   | bigint(20)  |  | | 0   |   |
| org  | varchar(80) |  | | |   |
+--+-+--+-+-+---+

Populated with numbers for the 1st 2 fields,  about 2.9 million records

Indexes as such:
mysql> show index from ip2org;
+++--+--+-+---+-
+--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+++--+--+-+---+-
+--++-+
| ip2org |  1 | dex  |1 | ip_start| A |
2943079 | NULL | NULL   | |
| ip2org |  1 | dex  |2 | ip_end  | A |
2943079 | NULL | NULL   | |
+++--+-
-+-+---+-+--++-+

I do this query:
mysql> explain SELECT org from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++--+---+--+-+--+-+-
---+
| table  | type | possible_keys | key  | key_len | ref  | rows| Extra
|
++--+---+--+-+--+-+-
---+
| ip2org | ALL  | dex   | NULL |NULL | NULL | 2943079 | where
used |
++--+---+--+-+--+-+-
---+

And it will not use the index, but if I do this ( a count):
mysql> explain SELECT count(*) from ip2org use index (dex) where
ip_start<=1094799892 and ip_end>=1094799892;
++---+---+--+-+--++-
+
| table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra
|
++---+---+--+-+--++-
+
| ip2org | range | dex   | dex  |   8 | NULL | 594025 | where
used; Using index |
++---+---+--+-+--++-
+

It will use the index.

WHY can't I get it to use the index on a query with siple firlds with
numeric values??  The query takes about 12 seconds   in fact when I do
the count, it still takes that long (maybe it just *thinks* it's using the
indexes !).  this should return a value in less than  sec I've used
tables this big without this problem before... what's up?   No matter how a
screw around with the indexes, I can't make it work...

Help! :)




-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 11:41 PM
To: Marc Slemko
Cc: MerchantSense; [EMAIL PROTECTED]
Subject: Re: strange table speed issue

I'm not certain, but I don't think a multi-column index will help here.  The

manual is unclear on how a multi-column index is used when you are comparing

the first key part to a range rather than to a constant, but I get the 
impression it doesn't use the second key part in that case.  For you, that 
would mean your multi-column index is no better than your single column 
indexes.

The problem is that with either column, the range of matches is large enough

that the optimizer judges a table scan will be quicker than all those key 
lookups.  You can see this in the EXPLAIN output, type = ALL and rows = the 
size of your table.  Both indicate a full table scan.

You may be able to do better if you know something about the ranges defined 
by ip_start and ip_end, particularly if ip2org is relatively static.  You 
can find the size of the largest range with

   SELECT MAX(ip_end - ip_start) FROM ip2org;

Suppose that comes back with 1500.  Then the matching row will have ip_start

no less than your ip (1094799892) - 1500, and it will have ip_end no more 
than your ip + 1500.  Then

   SELECT org FROM ip2org
   WHERE ip_start BETWEEN 1094799892-1500 AND 1094799892
   AND ip_end BETWEEN 1094799892 AND 1094799892 + 1500;

specifies a small range on each column, enabling use of one index or the 
other for fast lookups.  Note that this will break for ip < 1500 or ip > 
max(ip) - 1500, but those should already use one or the other index with 
your original query.

Michael

Marc Slemko wrote:

> On Thu, 24 Jun 2004, MerchantSense wrote:
> 
> 
>>Seems ok to me...
>>
>>It seems to be checking all the rows in the explain for some reason too...
>>
>>mysql> show index from ip2org;
>>+++--+--+-+---
+-
>>+--++-+
>>| Table  | Non_unique | Key_name

RE: error 27

[Donny Simonton]

Yes, if you are using a dynamic table which means it has varchar's, text, or
blobs the limit is 2 gigs.  If you are using a fixed table which uses chars
only, then there is no limit that I have seen.

Donny

> -Original Message-
> From: J S [mailto:[EMAIL PROTECTED]
> Sent: Friday, June 25, 2004 9:38 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: error 27
> 
> Could this problem be due to the size of my tables? Is there a limit on
> how
> big the table can be?
> I'm using mysql-standard-4.0.20.
> 
> -rw-rw   1 mysqlmysql2147483647 Jun 25 01:49
> internet_usage.MYD
> -rw-rw   1 mysqlmysql622724096 Jun 25 01:49 internet_usage.MYI
> 
> >
> >Hi,
> >
> >I got an error 27.
> >
> >DBD::mysql::st execute failed: Got error 27 from table handler at
> >/home/u752359/logfile.pl line 144,  line 3079464.
> >
> >The FAQs say:
> >
> >Check whether you have hit 2 Gb limit.
> >If that is not the case, shutdown MySQL server and repair a table with
> >(my)isamchk.
> >
> >How do I check if I have a 2GB limit? I logged on to mysql and ran a
> select
> >from the table successfully. Do I still need to run myisamchk ?
> >
> >Thanks,
> >
> >js.
> >
> >_
> >Want to block unwanted pop-ups? Download the free MSN Toolbar now!
> >http://toolbar.msn.co.uk/
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> >
> 
> _
> Stay in touch with absent friends - get MSN Messenger
> http://www.msn.co.uk/messenger
> 
> 
> --
> 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]

Optimising SQL Statements

[Martin Gainty]

Hello All:
I have a very simple test procedure
PROCEDURE TEST_PROCEDURE AS
BEGIN
DELETE FROM X;
COMMIT;
INSERT INTO X (column1)
SELECT
Y.y_id
FROM
Y_TABLE Y
WHERE
Y.y_id NOT IN (select Y.y_id FROM TABLEY2);
COMMIT;
END;
this very simple procedure takes 5 min 30 sec to complete its very basic 
delete and insert operations
Any ideas on how I can optimise
(I used truncate instead of delete and that helped big time)

Vielen Danke,
-Martin
Martin Gainty
__
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relating to the official 
business of Laconia Data Systems (LDS) is proprietary to the company. It is 
confidential, legally privileged and protected by law. LDS does not own and 
endorse any other content.
(cell) 617-852-7822
(e) [EMAIL PROTECTED]
(http)www.laconiadatasystems.com



From: "Ron McKeever" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: Location of files
Date: Fri, 25 Jun 2004 07:47:18 -0700
MIME-Version: 1.0
Received: from lists.mysql.com ([213.136.52.31]) by mc8-f18.hotmail.com 
with Microsoft SMTPSVC(5.0.2195.6824); Fri, 25 Jun 2004 07:50:32 -0700
Received: (qmail 11035 invoked by uid 109); 25 Jun 2004 14:47:44 -
Received: (qmail 11013 invoked from network); 25 Jun 2004 14:47:43 -
Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] 
designates 207.217.120.74 as permitted sender)
X-Message-Info: JGTYoYF78jH0d9Gs+XXJZ4+neItA1A7m
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: 
Precedence: bulk
List-Help: 
List-Unsubscribe: 

List-Post: 
List-Archive: http://lists.mysql.com/mysql/167906
Delivered-To: mailing list [EMAIL PROTECTED]
Message-ID: <[EMAIL PROTECTED]>
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
X-Virus-Checked: Checked
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 25 Jun 2004 14:50:35.0116 (UTC) 
FILETIME=[C575A2C0:01C45AC3]

Is there any benefit to having the .MYD files on one drive, and the .MYI on
its own dedicated hard drive??
rm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

R: info about MySQL Server

[Leonardo Francalanci]

try removing your client for the moment:
can you try a load data infile and see at what speed mysql loads
the rows?


> -Messaggio originale-
> Da: Alfredo Marchini [mailto:[EMAIL PROTECTED]
> Inviato: venerdì 25 giugno 2004 18.21
> A: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED];
> [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Oggetto: Re: info about MySQL Server
>
>
> Hi,
> thank you very much for your reply.
>
> Now I answer you about your additional questions:
>
> Now the daemon is installed on a machine different than DB Server, so I
> use DBServer NIC IP Address for connect to it from the daemon (So I don't
> use Unix Sockets).
>
> But, looking at the Network Bandwidth, there isn't bottleneck (but I have
> read that Unix Socket are more quickly).
>
> My SCSI HD are not in RAID. Actually are fully indipendent and works on a
> RAID controller 160Mb/s (the same speed of the disks).
>
> Probably in the future I'll use RAID tech that improves performance
> (RAID-5, RAID-0+1), but not in this beginning step.
>
> J2SE project source compiled becomes a bytecode (not exe and not source);
> this is runnable only on a Java Virtual Machine (It is like
> half-compiled).
>
> Before decide to develope in J2SE I have made some tests about performance
> differences between JDBC and MySQL client API
>
> MySQL client API obviously are more quickly, but no so much. Also I have
> read documentations about the MySQL JDBC Connector that are the best Java
> Native DB Connector (also better than other famous DBMS JDBC).
>
> Here is MYISAM table structure.
>
> create table pippo
> (
> typevarchar(10) not null,
> time_1  varchar(8)  not null,
> value   decimal(8,2)not null,
> state   char(2) not null,
> primary key (type, time_1)
> )
> type = MyIsam;
>
> How you can see the table structure is very simple
>
> The MotherBoard is a GigaByte GA-7VA.
>
> here is the link about it:
>
> http://tw.giga-byte.com/MotherBoard/Products/Products_GA-7VA.htm
>
> Red Hat 9.B info:
>
> the kernel version is 2.4.20-20.9 i686.
>
> on the os are installed and run only
>
> 2 MySQL server,sshd,syslog,cron,atd,anacron.
>
> When I boot the machine, I have only 40Mb Ram used.
>
> All It seems ok.
>
> I don't have anymore to say...
>
> If you need other info, I am here
> Thank you very much again.
>
> Best Regards
> Alfredo Marchini
> Developer Manager
> SysNet Solutions S.r.l.
> Via Rimini, 49 - Centro Dir. Leonardo da Vinci
> 59100 Prato (PO) - Italy
> Tel. +390574/484822 - Fax +390574/448075
>
> >
> > Alfredo,
> >
> > I have just a few additional questions that you did not already answer
> in
> > your very well-formed post. I normally wouldn't worry about ANY of these
> issues but you seem to be exploring the envelope of performance on your
> system and every little bit counts:
> >
> > Which communication path are you using to connect to your MySQL servers
> from your application (TCP/IP or sockets  or ...)? If TCP/IP are you
> pointing to the IP address of your NIC or to 127.0.0.1?
> >
> > Are your HD's in a RAID configuration? If so which one?
> >
> > Could you post the results of SHOW CREATE TABLE so that we can see what
> indexes, if any, you have on your data?
> >
> > Are there any other applications running on the same machine? I know you
> said your CPU usage didn't pass 40% but am looking for disk I/O
> congestion
> > or data bus congestion or some other resource contention.
> >
> > Is your application compiled or interpreted? I am not familiar with J2SE
> but I do know that some Java-based implementations give you the option
> to
> > compile applications into either a stand-alone executable or to object
> code. Object code is interpreted by the Java runtime engine during
> execution which may be slowing you down.
> >
> > (as a last resort) What is the speed of the data bus on your
> motherboard?
> >
> > Thank you for your patience,
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> >
> >
> >
> >   "Alfredo Marchini"
> >   <[EMAIL PROTECTED]To:
> > [EMAIL PROTECTED]
> >   lutions.it>   cc:
> > [EMAIL PROTECTED]
> > Fax to:
> >   06/25/2004 03:24 AM   Subject:  info
> > about MySQL Server
> >   Please respond to
> >   alfredo.marchini
> >
> >
> >
> >
> >
> >
> > Dear Sir,
> >
> > Sergei Golubchik gives me this e-mail address telling me that you can
> help
> > me about a project that my company is developing.
> >
> > we need to develope a J2SE project that works on N MYSQL 4.0.20 servers
> installed on the same machine.
> >
> > Now I try to explain my problem.
> >
> > The Hardware, actually, is a normal PC:
> >
> > CPU AMD ATHLON XP 2000+
> > RAM

Re: info about MySQL Server

[Alfredo Marchini]

Hi,
thank you very much for your reply.

Now I answer you about your additional questions:

Now the daemon is installed on a machine different than DB Server, so I
use DBServer NIC IP Address for connect to it from the daemon (So I don't
use Unix Sockets).

But, looking at the Network Bandwidth, there isn't bottleneck (but I have
read that Unix Socket are more quickly).

My SCSI HD are not in RAID. Actually are fully indipendent and works on a
RAID controller 160Mb/s (the same speed of the disks).

Probably in the future I'll use RAID tech that improves performance
(RAID-5, RAID-0+1), but not in this beginning step.

J2SE project source compiled becomes a bytecode (not exe and not source);
this is runnable only on a Java Virtual Machine (It is like
half-compiled).

Before decide to develope in J2SE I have made some tests about performance
differences between JDBC and MySQL client API

MySQL client API obviously are more quickly, but no so much. Also I have
read documentations about the MySQL JDBC Connector that are the best Java
Native DB Connector (also better than other famous DBMS JDBC).

Here is MYISAM table structure.

create table pippo
(
typevarchar(10) not null,
time_1  varchar(8)  not null,
value   decimal(8,2)not null,
state   char(2) not null,
primary key (type, time_1)
)
type = MyIsam;

How you can see the table structure is very simple

The MotherBoard is a GigaByte GA-7VA.

here is the link about it:

http://tw.giga-byte.com/MotherBoard/Products/Products_GA-7VA.htm

Red Hat 9.B info:

the kernel version is 2.4.20-20.9 i686.

on the os are installed and run only

2 MySQL server,sshd,syslog,cron,atd,anacron.

When I boot the machine, I have only 40Mb Ram used.

All It seems ok.

I don't have anymore to say...

If you need other info, I am here
Thank you very much again.

Best Regards
Alfredo Marchini
Developer Manager
SysNet Solutions S.r.l.
Via Rimini, 49 - Centro Dir. Leonardo da Vinci
59100 Prato (PO) - Italy
Tel. +390574/484822 - Fax +390574/448075

>
> Alfredo,
>
> I have just a few additional questions that you did not already answer
in
> your very well-formed post. I normally wouldn't worry about ANY of these
issues but you seem to be exploring the envelope of performance on your
system and every little bit counts:
>
> Which communication path are you using to connect to your MySQL servers
from your application (TCP/IP or sockets  or ...)? If TCP/IP are you
pointing to the IP address of your NIC or to 127.0.0.1?
>
> Are your HD's in a RAID configuration? If so which one?
>
> Could you post the results of SHOW CREATE TABLE so that we can see what
indexes, if any, you have on your data?
>
> Are there any other applications running on the same machine? I know you
said your CPU usage didn't pass 40% but am looking for disk I/O
congestion
> or data bus congestion or some other resource contention.
>
> Is your application compiled or interpreted? I am not familiar with J2SE
but I do know that some Java-based implementations give you the option
to
> compile applications into either a stand-alone executable or to object
code. Object code is interpreted by the Java runtime engine during
execution which may be slowing you down.
>
> (as a last resort) What is the speed of the data bus on your
motherboard?
>
> Thank you for your patience,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
>
>
>   "Alfredo Marchini"
>   <[EMAIL PROTECTED]To:
> [EMAIL PROTECTED]
>   lutions.it>   cc:
> [EMAIL PROTECTED]
> Fax to:
>   06/25/2004 03:24 AM   Subject:  info
> about MySQL Server
>   Please respond to
>   alfredo.marchini
>
>
>
>
>
>
> Dear Sir,
>
> Sergei Golubchik gives me this e-mail address telling me that you can
help
> me about a project that my company is developing.
>
> we need to develope a J2SE project that works on N MYSQL 4.0.20 servers
installed on the same machine.
>
> Now I try to explain my problem.
>
> The Hardware, actually, is a normal PC:
>
> CPU AMD ATHLON XP 2000+
> RAM 770Mb DDR
> 2 * HD SCSI 160Mb/s.
>
> With a Red Hat Linux 9.B installed.
>
> Now (the beginning) I use only 2 MYSQL server, one for SCSI disk.
>
> The two MYSQL servers are totally indipendent.
>
> In either of these, I have created a database, and a MYISAM table. The
size of one record is 30 bytes and there is only an index made by two
fields (varchar(8) and varchar(10)).
>
> I need to insert in this table-structrure an amount of data that have to
be, at least, the size of 35.000 records for second, 24 hours for day,
31
> days for month.
>
> I reached, using only one MYSQL server, 20.000 records for second.
>
> Installing two ser

Location of files

[Ron McKeever]

Is there any benefit to having the .MYD files on one drive, and the .MYI on
its own dedicated hard drive??


rm


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ENUM vs TINYINT

[Tim Brody]

If you want to use as little space as possible use char(0) and null/not-null
for your boolean values:

CREATE TEMPORARY TABLE test_bool (i char(5), bool char(0));
INSERT INTO test_bool VALUES
('true',''),('false',null),('true',''),('true',''),('false',null);
SELECT * FROM test_bool WHERE bool is not Null;
SELECT * FROM test_bool WHERE bool is Null;

In a multi-column table that saves you 1 byte per row.

Regards,
Tim.

- Original Message - 
From: "Brian Mansell" <[EMAIL PROTECTED]>
To: "Cemal Dalar" <[EMAIL PROTECTED]>
Cc: "Group MySQL List" <[EMAIL PROTECTED]>
Sent: Friday, June 25, 2004 8:01 AM
Subject: Re: ENUM vs TINYINT


> Cemal,
>
> I recall hearing a similar question mentioned in a previous email
> thread. In fact, here it is:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1&prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1
>
> (thread is titled "enum or tinyint")
>
> I hope that helps!
>
> On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar <[EMAIL PROTECTED]> wrote:
> >
> > Hi all,
> >
> > I need a boolean column and at to this time I always used
ENUM('Y','N')
> > for this. I'am wondering that will there be a performance difference
between
> > using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to
TINYINT
> > column.
> >
> > Best Regards,
> > Cemal Dalar a.k.a Jimmy
> > System Administrator & Web Developer
> > http://www.dalar.net
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: error 27

[J S]

Could this problem be due to the size of my tables? Is there a limit on how 
big the table can be?
I'm using mysql-standard-4.0.20.

-rw-rw   1 mysqlmysql2147483647 Jun 25 01:49 internet_usage.MYD
-rw-rw   1 mysqlmysql622724096 Jun 25 01:49 internet_usage.MYI
Hi,
I got an error 27.
DBD::mysql::st execute failed: Got error 27 from table handler at 
/home/u752359/logfile.pl line 144,  line 3079464.

The FAQs say:
Check whether you have hit 2 Gb limit.
If that is not the case, shutdown MySQL server and repair a table with
(my)isamchk.
How do I check if I have a 2GB limit? I logged on to mysql and ran a select 
from the table successfully. Do I still need to run myisamchk ?

Thanks,
js.
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Stay in touch with absent friends - get MSN Messenger 
http://www.msn.co.uk/messenger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: how to bind mysql to a certain ip-address?

[Sergio Salvi]

bind-address is what you need.

http://dev.mysql.com/doc/mysql/en/Server_options.html

[]s,
Sergio.

On Fri, 25 Jun 2004, Marten Lehmann wrote:

> Hello,
> 
> I was reading through the documentation, but I couldn't find anything on 
> how to bind mysql to a certain ip-address. The only thing I found was 
> how to bind mysql to a different port:
> 
> mysqld_safe --port=port_number
> 
> But I don't want mysql to listen on all interfaces.
> 
> Regards
> Marten
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: info about MySQL Server

[SGreen]

Alfredo,

I have just a few additional questions that you did not already answer in
your very well-formed post. I normally wouldn't worry about ANY of these
issues but you seem to be exploring the envelope of performance on your
system and every little bit counts:

Which communication path are you using to connect to your MySQL servers
from your application (TCP/IP or sockets  or ...)? If TCP/IP are you
pointing to the IP address of your NIC or to 127.0.0.1?

Are your HD's in a RAID configuration? If so which one?

Could you post the results of SHOW CREATE TABLE so that we can see what
indexes, if any, you have on your data?

Are there any other applications running on the same machine? I know you
said your CPU usage didn't pass 40% but am looking for disk I/O congestion
or data bus congestion or some other resource contention.

Is your application compiled or interpreted? I am not familiar with J2SE
but I do know that some Java-based implementations give you the option to
compile applications into either a stand-alone executable or to object
code. Object code is interpreted by the Java runtime engine during
execution which may be slowing you down.

(as a last resort) What is the speed of the data bus on your motherboard?

Thank you for your patience,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




   
 
  "Alfredo Marchini"   
 
  <[EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  lutions.it>   cc:   [EMAIL PROTECTED]
   
Fax to:
 
  06/25/2004 03:24 AM   Subject:  info about MySQL 
Server   
  Please respond to
 
  alfredo.marchini 
 
   
 
   
 




Dear Sir,

Sergei Golubchik gives me this e-mail address telling me that you can help
me about a project that my company is developing.

we need to develope a J2SE project that works on N MYSQL 4.0.20 servers
installed on the same machine.

Now I try to explain my problem.

The Hardware, actually, is a normal PC:

CPU AMD ATHLON XP 2000+
RAM 770Mb DDR
2 * HD SCSI 160Mb/s.

With a Red Hat Linux 9.B installed.

Now (the beginning) I use only 2 MYSQL server, one for SCSI disk.

The two MYSQL servers are totally indipendent.

In either of these, I have created a database, and a MYISAM table. The
size of one record is 30 bytes and there is only an index made by two
fields (varchar(8) and varchar(10)).

I need to insert in this table-structrure an amount of data that have to
be, at least, the size of 35.000 records for second, 24 hours for day, 31
days for month.

I reached, using only one MYSQL server, 20.000 records for second.

Installing two services I hoped that I could reach 40.000 records for
second (or some minus) because MySQL servers should work in paraller mode.

But it isn't the truth, the speed doesn't increment.

the J2SE project use multithreading technology, and is like a MySQL
servers load-balancer.

Looking at the performance (with top), I see that the CPU never goes over
40%, and the RAM is about 40%.
These performance are similar using only one MySQL server or two MySQL
servers.

I tried to correctly set MySQL system variables, I also tried to do an
higher priority to MySQL server processes, but the performance are the
same.
Another strange thing is the thread_concurrencing running on MySQL
servers, looking at the status, threads running value never oltrepass 2,
and I set 8 thread_concurrency.

Now I explain you how the final project should be:

there is a J2SE software that runs as a daemon, it listen to a TCP port.
this daemon is connected (as a client with JConnector 3.0.14) to N MySQL
servers on a DBServer (at least 2 MYSQL services).

the DBServer will be a very powerful server (because my customer needs
more than 35.000 records for second and many clients connected to the
daemon).

A client connected to the daemon can do usually two things:

asks the daemon to do insert data in database, or/and read data from
database.
(So the daeamon uses only SELECT .. FROM ... WHERE ... LIMIT ... UNION and
INSERT INTO ... VALUES (),

how to bind mysql to a certain ip-address?

[Marten Lehmann]

Hello,
I was reading through the documentation, but I couldn't find anything on 
how to bind mysql to a certain ip-address. The only thing I found was 
how to bind mysql to a different port:

mysqld_safe --port=port_number
But I don't want mysql to listen on all interfaces.
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Master Slave Problem.

[Kieran Kelleher]

Pierre,
Myslave working status is different to yours and as you can see there 
are values missing in yours such as Master_Log_File, etc. It seems your 
slave is not establishing successful replication communication with the 
master.

What is your SLAVE IP address?
What does your MASTER my.ini file have for bin-log and server-id 
parameters

What are the privileges for Replikator on the MASTER?
mysql> show slave status\G
*** 1. row ***
  Master_Host: 192.168.1.141
  Master_User: repl
  Master_Port: 3306
Connect_retry: 60
  Master_Log_File: binary-log.002
  Read_Master_Log_Pos: 551
   Relay_Log_File: xmain-relay-bin.010
Relay_Log_Pos: 45
Relay_Master_Log_File: binary-log.002
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
  Replicate_do_db:
  Replicate_ignore_db:
   Last_errno: 0
   Last_error:
 Skip_counter: 0
  Exec_master_log_pos: 551
  Relay_log_space: 45
1 row in set (0.02 sec)
mysql>
On Jun 25, 2004, at 1:41 AM, Rousseau, Pierre wrote:
Re: Master Slave Problem :
mysql> show slave status\G
*** 1. row ***
  Master_Host: 164.23.43.160
  Master_User: Replikator
  Master_Port: 3306
Connect_retry: 60
  Master_Log_File:
  Read_Master_Log_Pos: 1934
   Relay_Log_File: WIH00106-relay-bin.015
Relay_Log_Pos: 4
Relay_Master_Log_File:
 Slave_IO_Running: No
Slave_SQL_Running: Yes
  Replicate_do_db:
  Replicate_ignore_db:
   Last_errno: 0
   Last_error:
 Skip_counter: 0
  Exec_master_log_pos: 0
  Relay_log_space: 4
1 row in set (0.00 sec)
---
and my.ini file :
[mysqld]
master_host=164.23.43.160
master_user=Replikator
master_password=hackmack23
master_port=3306
server_id=10
[WinMySQLAdmin]
Server=D:/databank/bin/mysqld-nt.exe
note : user/pw correct :(
-Pierre Rousseau
-x->-Ursprüngliche Nachricht-
-x->Von: Kieran Kelleher [mailto:[EMAIL PROTECTED]
-x->Gesendet: Donnerstag, 24. Juni 2004 22:05
-x->An: Rousseau, Pierre
-x->Cc: [EMAIL PROTECTED]
-x->Betreff: Re: Master Slave Problem.
-x->
-x->
-x->Log into the slave and do this command
-x->mysql> mysql> show slave status\G
-x->
-x->. what do you see?
-x->
-x->Also, what is in your my.cnf file on the slave?
-x->
-x->FYI, here is instructions on how I set up replication last week
-x->http://homepage.mac.com/kelleherk/iblog/C711669388/E35122010
-x->0/index.html
-x->
-x->-Kieran
-x->
-x->
-x->
-x->Config = OS X 10.3.4 / Java 1.4.2 /  WO 5.2.3 / XCode v1.2 / MySQL
-x->4.0.20 / Connector-J 3.0.11
-x->Blog: http://webobjects.webhop.org/
-x->
-x->
-x->On Jun 24, 2004, at 9:15 AM, Rousseau, Pierre wrote:
-x->
-x->> Hi Bug Team,
-x->>
-x->> I have set up a Master Slave System with MySQL 4.0.20. I've done
-x->> completing every task from "6.4 How to Set Up Replication".
-x->>
-x->> But my problem is :
-x->> 040624 14:44:44  Slave I/O thread: connected to master
-x->> '[EMAIL PROTECTED]:3306',  replication started in
-x->log 'FIRST'
-x->> at position 4
-x->> 040624 14:44:45  While trying to obtain the list of
-x->slaves from the
-x->> master '164.23.43.160:3306', user 'Replikator' got the following
-x->> error: 'the master returned an invalid number of fields
-x->for SHOW SLAVE
-x->> HOSTS'
-x->> 040624 14:44:45  Slave I/O thread exiting, read up to log
-x->'FIRST',
-x->> position 4
-x->>
-x->> I have not found any support for this problem :(
-x->>
-x->> Your Faithfully
-x->> Pierre Rousseau
-x->>
-x->> Deutsche Telekom AG
-x->> T-Com, Technische Infrastruktur Niederlassung Nord
-x->> Pierre Rousseau
-x->> E-Mail: 
-x->> Internet: 
-x->>
-x->> Diese E-Mail könnte  vertrauliche und/oder rechtlich geschützte
-x->> Informationen enthalten. Wenn Sie nicht der richtige
-x->Adressat sind oder
-x->> diese E-Mail irrtümlich erhalten haben, informieren Sie
-x->bitte sofort
-x->> den
-x->> Absender und vernichten Sie diese Mail. Das unerlaubte
-x->Kopieren sowie
-x->> die
-x->> unbefugte Weitergabe dieser Mail sind nicht gestattet.
-x->>
-x->> This e-mail may contain confidential and/or privileged
-x->information. If
-x->> you
-x->> are not the intended recipient (or have received this
-x->e-mail in error)
-x->> please notify the sender immediately and destroy this e-mail. Any
-x->> unauthorised copying, disclosure or distribution of the
-x->material in
-x->> this
-x->> e-mail is strictly forbidden.
-x->>
-x->>
-x->> --
-x->> MySQL General Mailing List
-x->> For list archives: http://lists.mysql.com/mysql
-x->> To unsubscribe:
-x->> http://lists.mysql.com/[EMAIL PROTECTED]
-x->>
-x->
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


___
Kieran Kelleher
SmartleadsUSA,LLC
2656 West Lake R

Re: Repair Table and USE_FRM

[Sergei Golubchik]

Hi!

On Jun 25, Dan Cumpian wrote:
> Is there any harm in using the USE_FRM directive by default whenever
> repairing a table?

Yes.
See http://bugs.mysql.com/4252

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb and backup

[[EMAIL PROTECTED]]

i have been reading..
and i have found 3 ways to have a copy of mysql master datas for initilize the 
mysql slave 
( always having in mind innodb compatibility )

1) 
FLUSH TABLES WITH READ LOCK
see the 'SHOW MASTER STATUS' sentence
shutdown master database
copy all data, log and .frm files
RESTART SERVER

2) 
FLUSH TABLES WITH READ LOCK
see the 'SHOW MASTER STATUS' sentence
shutdown master database
use 'mysqldump ...' command
RESTART SERVER

3)
use 'LOAD DATA FROM MASTER' sentence
( i havent studied this option )


i prefer option number 2..

but my question is:
is it possible to do a 'mysqldump ..' ( remember innodb ) without shutdowning 
the server?

thanks
d2clon


On Thursday 24 June 2004 15:22, Kieran Kelleher wrote:
> The simplest thing to do is set up a replication slave and run a script
> every night that shuts down the MySQL slave, performs the backup, and
> then restarts MySQL. We have a dedicated XServe running MySQL and we
> use another XServe whose primary function is filesharing to also act as
> a replication slave. Each night a system cron script stops MySQL, backs
> up the slave XServe, and then restarts MySQL
>
> On Jun 24, 2004, at 7:01 AM, [EMAIL PROTECTED] wrote:
> > thx thx :D
> >
> > thats work right.
> >
> > the only problem is that mysqldump dont write the 'set
> > FOREIGN_KEY_CHECKS'
> > sentences until version 4.1.1:
> >
> > "To make it easier to reload dump files for tables that have foreign
> > key
> > relationships, mysqldump automatically includes a statement in the dump
> > output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1."
> >
> > :).. but dont worry i write the sentences by hand.
> >
> > thx
> > d2clon
> >
> > On Thursday 24 June 2004 11:44, Egor Egorov wrote:
> >> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> >>> i have read multiple websites and posts.. and this mail list archive
> >>> but i have not found the answer to the question:
> >>>
> >>> how is it possible to do a backup of a innodb table?
> >>>
> >>>
> >>>
> >>> i have read: "try to use mysqldump.." but this does not work right
> >>> i have read: "try to use mysqldump --single-transaction ..." but i
> >>> dont
> >>> see any difference.. and doesnt work right too
> >>> i have read: "use this: http://www.innodb.com/order.php .." but it
> >>> isnt
> >>> free :/
> >>>
> >>>
> >>> the problem is not how to do the backup.. but recovering from it..
> >>> because it complains about the foreign keys.
> >>
> >> How to restore tables that have foreign key relationships is
> >> described at:
> >>http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
> >>
> >> You should set FOREIGN_KEY_CHECKS to 0 before restoring tables.
> >>
> >>> and then i think:
> >>> how is it possible that i cant do backup, and recover it, of my
> >>> innodb
> >>> tables?
> >>
> >> --
> >> For technical support contracts, goto
> >> https://order.mysql.com/?ref=ensita
> >> This email is sponsored by Ensita.net http://www.ensita.net/
> >>__  ___ ___   __
> >>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
> >>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> >> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
> >><___/   www.mysql.com
> >
> > --
> > 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]

Repair Table and USE_FRM

[Dan Cumpian]

Is there any harm in using the USE_FRM directive by default whenever
repairing a table?

 

Thanks,

Dan

 


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.703 / Virus Database: 459 - Release Date: 6/10/2004
 

Re: MySQL Crashing On Index Creation/Select count(*) statement - InnoDB tables

[Dr. Frank Ullrich]

David,
David Griffiths wrote:
We are in the process of setting up a new MySQL server. It's a 
dual-Opteron (Tyan Thunder K8S motherboard) with 6 gig of DDR333 RAM 
(registered) and an LSI SCSI card with 6 SCSI drives (5 in a RAID-5 
array, with one hot-spare) running SuSE Enterprise 8.1 (64-bit).

I loaded all our data (about 2 gig) into the database back on Tuesday, 
and created the indexes without issue, as a test to see how long it 
would take.

Tonight, we were going to cut over to this new machine. I was setting up 
data as a test run, and started coming across "Database page corruption 
on disk or a failed file read of page" errors.

At first, we were using MySQL 4.0.20 64-bit, compiled from source by us 
(the -fPic option needs to be included in the Makefile, and for some 
reason isn't in the binaries - also, no release notes for the AMD64 
So you can't use the binaries that MySQL provides and therefore you 
didn't test them? Or did you?
Why is this -fPic option important?
I'm curious because we have a dual opteron system too and I wanted to 
install the 64bit binary (4.0.20-standard) from the MySQL web site.

Regards,
   Frank.

platform at http://dev.mysql.com/doc/mysql/en/Linux.html).
I could consistently crash the database by creating an index on a column 
(a varchar(50)). I could also crash it doing a "SELECT COUNT(*)..." from 
a table with 3 million rows. Unfort, I did not save the crash-log.

We rolled back to 4.0.18, also 64-bit. Exactly the same issue. Here's 
the output.

- 

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 12244.
InnoDB: You may have to recover from a backup.
040624 17:21:59  InnoDB: Page dump in ascii and hex (16384 bytes):
...
040624 17:21:59  InnoDB: Page checksum 1484130208, prior-to-4.0.14-form 
checksum 1108511089
InnoDB: stored checksum 2958040096, prior-to-4.0.14-form stored checksum 
1108511089
InnoDB: Page lsn 0 204702464, low 4 bytes of lsn at page end 204702464
InnoDB: Page may be an index page where index id is 0 24
InnoDB: and table yw/boats2 index PRIMARY
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 12244.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: Look also at section 6.1 of
InnoDB: http://www.innodb.com/ibman.html about
InnoDB: forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

- 

InnoDB is robust enough to recover, fortunately.
Then we thought it might be an issue with the 64-bit version, so we 
installed the 32-binary version (we didn't compile it) of 4.0.20.

I managed to make it crash in exactly the same way - adding an index to 
a table, dropping an index, or selecting a count from the same large table.
- 

040624 20:29:07  mysqld restarted
040624 20:29:08  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 3576655719
InnoDB: Doing recovery: scanned up to log sequence number 0 3576655719
040624 20:29:08  InnoDB: Flushing modified pages from the buffer pool...
040624 20:29:09  InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-standard-log'  socket: '/tmp/mysql.sock'  port: 3306
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 23235.
InnoDB: You may have to recover from a backup.
040624 20:29:38  InnoDB: Page dump in ascii and hex (16384 bytes):
040624 20:29:38  InnoDB: Page checksum 1229875638, prior-to-4.0.14-form 
checksum 4263044155
InnoDB: stored checksum 2727822450, prior-to-4.0.14-form stored checksum 
4263044155
InnoDB: Page lsn 0 748566710, low 4 bytes of lsn at page end 748566710
InnoDB: Page may be an index page where index id is 0 15
InnoDB: and table yw/boats_clobs2 index PRIMARY
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 23235.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: T

error 27

[J S]

Hi,
I got an error 27.
DBD::mysql::st execute failed: Got error 27 from table handler at 
/home/u752359/logfile.pl line 144,  line 3079464.

The FAQs say:
Check whether you have hit 2 Gb limit.
If that is not the case, shutdown MySQL server and repair a table with
(my)isamchk.
How do I check if I have a 2GB limit? I logged on to mysql and ran a select 
from the table successfully. Do I still need to run myisamchk ?

Thanks,
js.
_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

info about MySQL Server

[Alfredo Marchini]

Dear Sir,

Sergei Golubchik gives me this e-mail address telling me that you can help
me about a project that my company is developing.

we need to develope a J2SE project that works on N MYSQL 4.0.20 servers
installed on the same machine.

Now I try to explain my problem.

The Hardware, actually, is a normal PC:

CPU AMD ATHLON XP 2000+
RAM 770Mb DDR
2 * HD SCSI 160Mb/s.

With a Red Hat Linux 9.B installed.

Now (the beginning) I use only 2 MYSQL server, one for SCSI disk.

The two MYSQL servers are totally indipendent.

In either of these, I have created a database, and a MYISAM table. The
size of one record is 30 bytes and there is only an index made by two
fields (varchar(8) and varchar(10)).

I need to insert in this table-structrure an amount of data that have to
be, at least, the size of 35.000 records for second, 24 hours for day, 31
days for month.

I reached, using only one MYSQL server, 20.000 records for second.

Installing two services I hoped that I could reach 40.000 records for
second (or some minus) because MySQL servers should work in paraller mode.

But it isn't the truth, the speed doesn't increment.

the J2SE project use multithreading technology, and is like a MySQL
servers load-balancer.

Looking at the performance (with top), I see that the CPU never goes over
40%, and the RAM is about 40%.
These performance are similar using only one MySQL server or two MySQL
servers.

I tried to correctly set MySQL system variables, I also tried to do an
higher priority to MySQL server processes, but the performance are the
same.
Another strange thing is the thread_concurrencing running on MySQL
servers, looking at the status, threads running value never oltrepass 2,
and I set 8 thread_concurrency.

Now I explain you how the final project should be:

there is a J2SE software that runs as a daemon, it listen to a TCP port.
this daemon is connected (as a client with JConnector 3.0.14) to N MySQL
servers on a DBServer (at least 2 MYSQL services).

the DBServer will be a very powerful server (because my customer needs
more than 35.000 records for second and many clients connected to the
daemon).

A client connected to the daemon can do usually two things:

asks the daemon to do insert data in database, or/and read data from
database.
(So the daeamon uses only SELECT .. FROM ... WHERE ... LIMIT ... UNION and
INSERT INTO ... VALUES (),(),()).

The client data requests are sent to the daemon that launchs queries on
the N MySQL Servers (as a balancer) and eventually gives the replies.

The data have to be in real time (so I can't use INSERT DELAYED).

the speed of the inserts have to be maximum as possible (at least 35.000
records for second).

During the insert operations another clients, in the same moment, could
ask the daemon to give him the data inserted in a period.

usually the data have about a size of 70Mb (1 day data), and the maximum
request is to give data of one entire month.

More Clients can do these types of requests.

Obviously I will create 4 tables for a day for either MySQL service. Now I
don't know if I'll use MySQL commands (MERGE, RAID or others) or I'll do
all from my daemon (I have to do test to take this decision).

This is all

In your MySQL doc, I read that you have tested MySQL for a big Sweden
company, but you have not mentioned anything about the size of the data
that
this company store, the clients connected, the size of the queries or the
number of inserts.

First I would like to know if MySQL could reach these high performance...

Because my actually problem is the Hardware resource that aren't full
using my little PC.
Why have I to buy a powerful server If this hardware isn't full used? The
bottleneck isn't in the SCSI disk (I installed MYSQL servers on RAMDISK
partitions with the same results).
The bottleneck I think is MySQL, but I also think that it is well
configured.

Here is my.cnf file (I have two equals configurations files, one for MySQL
server)

port= 330[0-1]
socket  = /ptolle/ssc/db/mysql_[0-1].sock
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 70M
read_buffer_size = 70M
query_cache_size = 70M
thread_cache = 8
thread_concurrency = 8 ---> no more than 2 threads run!!!
max-connections = 200
skip-innodb

Finally I tried also to compile the MySQL tarball with the gcc (the
version is 3.2.2-5) compiler parameters that are mentioned in your
documentation without better results.


have you got any parameters that limit MySQL performance (in the header
libraries or others)?

or have you got some informations about MySQL configuration that could
speed up MySQL or bring MySQL services to work in paraller mode and use
all hardware resource avalaible?

Thank you very much.
Best Regards


Alfredo Marchini
Developer Manager
SysNet Solutions S.r.l.
Via Rimini, 49 - Centro Dir. Leonardo da Vinci
59100 Prato (PO)
Tel. 0574/484822 - Fax 0574/448075

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/

Any MySQL + IBM Power CPU Hardware (pSeries, iSeries, JS20, Blade) Users?

[Zak Greant]

Greetings All!
MySQL AB is interested in talking to MySQL users who running MySQL on 
IBM Power CPU server hardware (pSeries, iSeries, JS20
Blade).

If you (or someone you know) are willing to chat with us, please let me 
know at [EMAIL PROTECTED]

Thank you for your help!
Cheers!
--
Zak Greant
MySQL AB Community Advocate
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ENUM vs TINYINT

[Brian Mansell]

Cemal,

I recall hearing a similar question mentioned in a previous email
thread. In fact, here it is:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1&prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1

(thread is titled "enum or tinyint")

I hope that helps! 

On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar <[EMAIL PROTECTED]> wrote:
> 
> Hi all,
> 
> I need a boolean column and at to this time I always used ENUM('Y','N')
> for this. I'am wondering that will there be a performance difference between
> using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to TINYINT
> column.
> 
> Best Regards,
> Cemal Dalar a.k.a Jimmy
> System Administrator & Web Developer
> http://www.dalar.net
> 
> 
> --
> 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]