question on loading data and generating uniq table

2009-03-13 Thread Bernd Jagla 
Hi, 

I wanted to ask some more experienced mysql users to give me some advice on
a project I am currently planning.

 

I have a text file with three columns: strName(char6), position(integer),
str(char36)

This file has some 3 billion rows (3,000,000,000). There are some strs
that are duplicated and eventually I want to create two tables: uniqStr and
posIDX, where

uniqStr has the columns ID (integer,primary) and str (uniq,char36,index)

posIDX has the columns uniqStr_ID, strName,position

(maybe the strName can be moved to a separate table as well and just
referenced in posIDX)

 

Now, what I would do is load the text file into a table;

index on str; 

create the table uniqStr using a select statement;

create the table posIDX by joining the first and second table.

 

I am not sure this is the fastest way of doing things. Maybe creating the
uniq sequences first using e.g. uniq on the command line would be faster?
(Thereby skipping loading the first file and creating the index) 

 

The str where created using a sliding window on a few very long strings.

In the very end I want to search for millions of new strs and figure out
if and in which string and at which position they are located. (I am looking
for exact matches)

Maybe this is not even a database problem, but could be solved easier with
different tools?

 

Thanks for any advice/comment.

 

Bernd

RE: MySQL Performance Analysis tools

2007-11-19 Thread Bernd Jagla 
sar will give you some basic information about what happens on the
system... (see e.g.: http://linux.die.net/man/1/sar)...

-B

|-Original Message-
|From: thomas Armstrong [mailto:[EMAIL PROTECTED]
|Sent: Monday, November 19, 2007 6:42 AM
|To: mysql@lists.mysql.com
|Subject: MySQL Performance Analysis tools
|
|Hi.
|
|Using MySQL on Linux, I'd like to analyze the performance and know how
|resources (memory, threads) are used during a period of time.
|
|Do you know any tool to carry it out? Thank you very much.
|
|--
|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]

modify default value

2007-09-15 Thread Bernd Jagla 
Hi,

I am mass-importing A LOT of csv tables. In order to distinguish between
files I change the default value for the table before doing a load data
infile.. from a Perl script:

 

ALTER TABLE `data` MODIFY COLUMN `sample_id` INTEGER UNSIGNED NOT NULL
DEFAULT $sample_id[$idx]

 

After importing a few hundred files I now realize that the table is copied
each time the alter table statement is executed. 

 

Is there a way to avoid this? I think there at least should be a way since
nothing in the structure is altered. 

 

Please advise!

 

Thanks,

 

Bernd

Re: Problem with a complex query

2007-08-14 Thread Bernd Jagla 

what does explain tell you about the query?

I also think you should try a combined index with all three columns  
that are used in one index. As far as I know only one index can be  
used per query...


B

On Aug 14, 2007, at 1:26 PM, Hugo Ferreira da Silva wrote:


hum... I get it.
But in my query, I look in 3 tables

FROM

mensagens m,
mensagenspara mp,
usuarios u,
usuarios up

WHERE

m.codmensagem = mp.codmensagem
AND u.codusaurio = m.codusuario
AND up.codusuario = mp.codusuario

m.codmensagem, u.codusaurio and up.codusuario are primary keys
mp.codusuario, m.codusuario and mp.codmensagem are indexes.

I'm joining the usuario's table twice to get the name of who is  
sending and

who is receiving.

I thought that creating indexes for the others columns will  
optimize the

results, because I'm using them in where clause.

Do you know some way to turn the response time of this query faster?



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

Re: Problem with a complex query

2007-08-14 Thread Bernd Jagla 
From explain you can see that your problem lies within the mensagens  
table (the first entry from your explain query) where it says type:  
ALL and rows 68337. This basically means that it is not using any  
index for this table.


MySQL doesn't seem to be very smart about queries involving OR and  
things like . For me creating temporary tables or writing perl  
scripts to do the job solved my particular problems. But I am working  
with tables that don't change but have some 100,000,000 rows...


I guess I suggest, introducing some redundancy and removing the OR  
statement could help in your case...


B

On Aug 14, 2007, at 2:19 PM, Hugo Ferreira da Silva wrote:


ype: ALL

ERROR 11 (HY000): Can't unlock file (Errcode: 11)

2007-07-20 Thread Bernd Jagla 
Hi there,

 

Is it true that problems related to error 11 are most likely bugs in MySQL? 

I have a query involving a union statement with a group and order by
statement around it. The total data that has to be handled by the union
statement is in the 100,000 or millions rows. I am using Windows XP with
mysql 5.0.37.

This problem doesn't occur when using less data. It has occurred numerous
times in past couple of months with different queries and also myisamchk and
I found some information on the web that made me believe this is related to
a bug in MySQL. Now I am getting to a point where I am getting annoyed by
this and trying to find workarounds.

 

So, my question: is there anything I can do? Has anybody else this problem? 

 

Thanks,

 

Bernd

index not used

2007-05-29 Thread Bernd Jagla 
Hi,

 

I have a problem with a very slow query that should be VERY fast.

 

There are two tables:

CREATE TABLE  `tfbs_mm`.`tfbs_ecr` (

  `tfbs_id` int(10) unsigned NOT NULL,

  `ecr_id` int(10) unsigned NOT NULL,

  KEY `tfbs_id` (`tfbs_id`),

  KEY `ecr_id` (`ecr_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Has ~101 Mio entries

 

 

CREATE TABLE  `tfbs_mm`.`ecr_neighbor_knowngene` (

  `ecr_id` int(10) unsigned NOT NULL,

  `distance_5` int(10) unsigned default NULL,

  `gene_5_id` int(10) unsigned default NULL,

  `distance_3` int(10) unsigned default NULL,

  `gene_3_id` int(10) unsigned default NULL,

  `identity` int(10) unsigned default NULL,

  PRIMARY KEY  (`ecr_id`),

  KEY `5_3_ident` (`gene_5_id`,`gene_3_id`,`identity`),

  KEY `5_dist` (`gene_5_id`,`distance_5`),

  KEY `3_dist` (`gene_3_id`,`distance_3`),

  KEY `5_ident` (`gene_5_id`,`identity`),

  KEY `3_ident` (`gene_3_id`,`identity`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Has ~1.2 Mio entries

 

SELECT ecr_id FROM ecr_neighbor_knowngene e

where gene_5_id = 3 or gene_3_id = 3;

returns 25 entries within a few milliseconds

 

explain select * from tfbs_ecr t

where ecr_id in
(301,302,316,323,345,346,351,362,363,364,380,411,413,451,464,466,470,471,481
,498,506,507,538,541,543);

*  uses key ecr_id

 

select * from tfbs_ecr t

where ecr_id in
(301,302,316,323,345,346,351,362,363,364,380,411,413,451,464,466,470,471,481
,498,506,507,538,541,543);

= returns 1167 rows within a few milli seconds.

 

When I combine them both I get an extremely slow query:

 

explain select * from tfbs_ecr t

where ecr_id in (SELECT ecr_id FROM ecr_neighbor_knowngene e

where gene_5_id = 3 or gene_3_id = 3);

 

1, 'PRIMARY', 't', 'ALL', '', '', '', '', 101129498, 'Using where'

2, 'DEPENDENT SUBQUERY', 'e', 'unique_subquery',
'PRIMARY,5_3_ident,5_dist,3_dist,5_ident,3_ident', 'PRIMARY', '4', 'func',
1, 'Using where'

 

Can somebody please explain to me why this happens and how I can avoid this?

 

Thanks a lot,

 

Bernd

referencing MySQL

2004-09-09 Thread Bernd Jagla 
Anybody knows how to reference MySQL in a scientific paper?

Thanks

Bernd

xml DTD and MySql

2004-03-03 Thread Bernd Jagla 
Hi everyone,

I have XML data with a corresponding DTD, and I want create the necessary tables and 
load the data.
Does anyone has experience with this?
Are there any tools to help me?

Thanks for your advice.

Bernd

no one can log on any more

2004-03-02 Thread Bernd Jagla 
Hi there,

I have some weird sql statements that seem to eat up all resources from mysqld.
Once I have two of them running at the same time no one can log on any more. Simple 
queries involving distinct get a out of memory error.

We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of 
memory on an IRIX system with 8 cpus.
No replication.

Do you have any idea what is happening?

Thanks a lot

Bernd


the sql statements look like this:

insert into rule2 
select id
from text_uniq_bin 
where (substring(rev_rep,1,1)=1) AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) 2 AND 
(substring(replaced,3,1)=0) AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) 5
;

AND 

insert into rule3 
select id
from text_uniq_bin
where (substring(rev_rep,1,1)=1) AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) 2 AND 
(substring(rev_rep,3,1)=0) AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) =5 AND
(substring(rev_rep,2,1)=0) AND 
(
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1) + 
substring(replaced,10,1) + 
substring(replaced,11,1) + 
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) 7

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 
An explain on the select statements reveals that no index is used and that
all 133856002 rows are searched.

Do you think that using a new index would make much of difference?
I think it has something to do with some system/mysql parameters that can be
adjusted.
I also don't understand why no one else can log on to the mysql system.

If you or someone could explain or point me to a documentation on this it
would be pretty close to what I want.

Thanks for your help

Bernd

- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 1:02 PM
Subject: Re: no one can log on any more


What does the explain plan reveal?

 Original Message 

On 3/2/04, 9:05:17 AM, Bernd Jagla [EMAIL PROTECTED] wrote regarding no
one can log on any more:


 Hi there,

 I have some weird sql statements that seem to eat up all resources from
 mysqld.
 Once I have two of them running at the same time no one can log on any
 more. Simple queries involving distinct get a out of memory error.

 We have tried changing the index buffer size from 256M to 2G, no change.
 We have 7G of memory on an IRIX system with 8 cpus.
 No replication.

 Do you have any idea what is happening?

 Thanks a lot

 Bernd


 the sql statements look like this:

 insert into rule2
 select id
 from text_uniq_bin
 where (substring(rev_rep,1,1)=1) AND
 (
 substring(replaced,1,1) +
 substring(replaced,2,1) +
 substring(replaced,3,1) +
 substring(replaced,4,1) +
 substring(replaced,5,1) +
 substring(replaced,6,1) +
 substring(replaced,7,1) +
 substring(replaced,8,1) +
 substring(replaced,9,1)
 ) 2 AND
 (substring(replaced,3,1)=0) AND
 (
 substring(replaced,12,1) +
 substring(replaced,13,1) +
 substring(replaced,14,1) +
 substring(replaced,15,1) +
 substring(replaced,16,1) +
 substring(replaced,17,1) +
 substring(replaced,18,1) +
 substring(replaced,19,1)
 ) 5
 ;

 AND

 insert into rule3
 select id
 from text_uniq_bin
 where (substring(rev_rep,1,1)=1) AND
 (
 substring(replaced,1,1) +
 substring(replaced,2,1) +
 substring(replaced,3,1) +
 substring(replaced,4,1) +
 substring(replaced,5,1) +
 substring(replaced,6,1) +
 substring(replaced,7,1) +
 substring(replaced,8,1) +
 substring(replaced,9,1)
 ) 2 AND
 (substring(rev_rep,3,1)=0) AND
 (
 substring(replaced,12,1) +
 substring(replaced,13,1) +
 substring(replaced,14,1) +
 substring(replaced,15,1) +
 substring(replaced,16,1) +
 substring(replaced,17,1) +
 substring(replaced,18,1) +
 substring(replaced,19,1)
 ) =5 AND
 (substring(rev_rep,2,1)=0) AND
 (
 substring(replaced,6,1) +
 substring(replaced,7,1) +
 substring(replaced,8,1) +
 substring(replaced,9,1) +
 substring(replaced,10,1) +
 substring(replaced,11,1) +
 substring(replaced,12,1) +
 substring(replaced,13,1) +
 substring(replaced,14,1) +
 substring(replaced,15,1) +
 substring(replaced,16,1) +
 substring(replaced,17,1) +
 substring(replaced,18,1) +
 substring(replaced,19,1)
 ) 7



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

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 
There are no indices on the new table.
Top looks normal: approx 100-150% usage (out of 800% [8 cpus])
There is no major i/o problem: sar -d says that less than 2% of io are used.

The only problems in this direction might be connected with memory and the
associated parameters in mysql.
The file system cache is 5GB. I hope this is no problem. mysqld uses about
54619 K (gmemusage)
Top shows the actual size as 1794M and Res=1609

The problem only occurs when I do two similar queries at the same time. Both
queries access the same table for read (not write). Might this be the
problem? How would I solve this one?

Bernd

- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 1:17 PM
Subject: Re: no one can log on any more


When the queries in question are running, what does top look like? With
an insert that large it may be beneficial to disable any keys that may be
active on the target table and re-enable them after the data has been
loaded.

 Original Message 

On 3/2/04, 12:08:05 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
no one can log on any more:


 An explain on the select statements reveals that no index is used and
that
 all 133856002 rows are searched.

 Do you think that using a new index would make much of difference?
 I think it has something to do with some system/mysql parameters that can
be
 adjusted.
 I also don't understand why no one else can log on to the mysql system.

 If you or someone could explain or point me to a documentation on this it
 would be pretty close to what I want.

 Thanks for your help

 Bernd

 - Original Message -
 From: [EMAIL PROTECTED]
 To: Bernd Jagla [EMAIL PROTECTED]
 Cc: mysql [EMAIL PROTECTED]
 Sent: Tuesday, March 02, 2004 1:02 PM
 Subject: Re: no one can log on any more


 What does the explain plan reveal?

  Original Message 

 On 3/2/04, 9:05:17 AM, Bernd Jagla [EMAIL PROTECTED] wrote regarding no
 one can log on any more:


  Hi there,

  I have some weird sql statements that seem to eat up all resources from
  mysqld.
  Once I have two of them running at the same time no one can log on any
  more. Simple queries involving distinct get a out of memory error.

  We have tried changing the index buffer size from 256M to 2G, no change.
  We have 7G of memory on an IRIX system with 8 cpus.
  No replication.

  Do you have any idea what is happening?

  Thanks a lot

  Bernd


  the sql statements look like this:

  insert into rule2
  select id
  from text_uniq_bin
  where (substring(rev_rep,1,1)=1) AND
  (
  substring(replaced,1,1) +
  substring(replaced,2,1) +
  substring(replaced,3,1) +
  substring(replaced,4,1) +
  substring(replaced,5,1) +
  substring(replaced,6,1) +
  substring(replaced,7,1) +
  substring(replaced,8,1) +
  substring(replaced,9,1)
  ) 2 AND
  (substring(replaced,3,1)=0) AND
  (
  substring(replaced,12,1) +
  substring(replaced,13,1) +
  substring(replaced,14,1) +
  substring(replaced,15,1) +
  substring(replaced,16,1) +
  substring(replaced,17,1) +
  substring(replaced,18,1) +
  substring(replaced,19,1)
  ) 5
  ;

  AND

  insert into rule3
  select id
  from text_uniq_bin
  where (substring(rev_rep,1,1)=1) AND
  (
  substring(replaced,1,1) +
  substring(replaced,2,1) +
  substring(replaced,3,1) +
  substring(replaced,4,1) +
  substring(replaced,5,1) +
  substring(replaced,6,1) +
  substring(replaced,7,1) +
  substring(replaced,8,1) +
  substring(replaced,9,1)
  ) 2 AND
  (substring(rev_rep,3,1)=0) AND
  (
  substring(replaced,12,1) +
  substring(replaced,13,1) +
  substring(replaced,14,1) +
  substring(replaced,15,1) +
  substring(replaced,16,1) +
  substring(replaced,17,1) +
  substring(replaced,18,1) +
  substring(replaced,19,1)
  ) =5 AND
  (substring(rev_rep,2,1)=0) AND
  (
  substring(replaced,6,1) +
  substring(replaced,7,1) +
  substring(replaced,8,1) +
  substring(replaced,9,1) +
  substring(replaced,10,1) +
  substring(replaced,11,1) +
  substring(replaced,12,1) +
  substring(replaced,13,1) +
  substring(replaced,14,1) +
  substring(replaced,15,1) +
  substring(replaced,16,1) +
  substring(replaced,17,1) +
  substring(replaced,18,1) +
  substring(replaced,19,1)
  ) 7



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

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 
table: text_data
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows 133856002
Extra: Using where

No temp tables created, no i/o problem, no swapping.


B
- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 2:34 PM
Subject: Re: no one can log on any more


Can you post the explain plan? I would be interested to see if temp table
tables are being created or if the system is swapping or if the the temp
tables are so large that the entire system begins to act poorly.

 Original Message 

On 3/2/04, 12:35:20 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
no one can log on any more:


 There are no indices on the new table.
 Top looks normal: approx 100-150% usage (out of 800% [8 cpus])
 There is no major i/o problem: sar -d says that less than 2% of io are
used.

 The only problems in this direction might be connected with memory and
the
 associated parameters in mysql.
 The file system cache is 5GB. I hope this is no problem. mysqld uses
about
 54619 K (gmemusage)
 Top shows the actual size as 1794M and Res=1609

 The problem only occurs when I do two similar queries at the same time.
Both
 queries access the same table for read (not write). Might this be the
 problem? How would I solve this one?

 Bernd

 - Original Message -
 From: [EMAIL PROTECTED]
 To: Bernd Jagla [EMAIL PROTECTED]
 Cc: mysql [EMAIL PROTECTED]
 Sent: Tuesday, March 02, 2004 1:17 PM
 Subject: Re: no one can log on any more


 When the queries in question are running, what does top look like? With
 an insert that large it may be beneficial to disable any keys that may be
 active on the target table and re-enable them after the data has been
 loaded.

  Original Message 

 On 3/2/04, 12:08:05 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
 no one can log on any more:


  An explain on the select statements reveals that no index is used and
 that
  all 133856002 rows are searched.

  Do you think that using a new index would make much of difference?
  I think it has something to do with some system/mysql parameters that
can
 be
  adjusted.
  I also don't understand why no one else can log on to the mysql system.

  If you or someone could explain or point me to a documentation on this
it
  would be pretty close to what I want.

  Thanks for your help

  Bernd

  - Original Message -
  From: [EMAIL PROTECTED]
  To: Bernd Jagla [EMAIL PROTECTED]
  Cc: mysql [EMAIL PROTECTED]
  Sent: Tuesday, March 02, 2004 1:02 PM
  Subject: Re: no one can log on any more


  What does the explain plan reveal?

   Original Message 

  On 3/2/04, 9:05:17 AM, Bernd Jagla [EMAIL PROTECTED] wrote regarding no
  one can log on any more:


   Hi there,

   I have some weird sql statements that seem to eat up all resources
from
   mysqld.
   Once I have two of them running at the same time no one can log on any
   more. Simple queries involving distinct get a out of memory error.

   We have tried changing the index buffer size from 256M to 2G, no
change.
   We have 7G of memory on an IRIX system with 8 cpus.
   No replication.

   Do you have any idea what is happening?

   Thanks a lot

   Bernd


   the sql statements look like this:

   insert into rule2
   select id
   from text_uniq_bin
   where (substring(rev_rep,1,1)=1) AND
   (
   substring(replaced,1,1) +
   substring(replaced,2,1) +
   substring(replaced,3,1) +
   substring(replaced,4,1) +
   substring(replaced,5,1) +
   substring(replaced,6,1) +
   substring(replaced,7,1) +
   substring(replaced,8,1) +
   substring(replaced,9,1)
   ) 2 AND
   (substring(replaced,3,1)=0) AND
   (
   substring(replaced,12,1) +
   substring(replaced,13,1) +
   substring(replaced,14,1) +
   substring(replaced,15,1) +
   substring(replaced,16,1) +
   substring(replaced,17,1) +
   substring(replaced,18,1) +
   substring(replaced,19,1)
   ) 5
   ;

   AND

   insert into rule3
   select id
   from text_uniq_bin
   where (substring(rev_rep,1,1)=1) AND
   (
   substring(replaced,1,1) +
   substring(replaced,2,1) +
   substring(replaced,3,1) +
   substring(replaced,4,1) +
   substring(replaced,5,1) +
   substring(replaced,6,1) +
   substring(replaced,7,1) +
   substring(replaced,8,1) +
   substring(replaced,9,1)
   ) 2 AND
   (substring(rev_rep,3,1)=0) AND
   (
   substring(replaced,12,1) +
   substring(replaced,13,1) +
   substring(replaced,14,1) +
   substring(replaced,15,1) +
   substring(replaced,16,1) +
   substring(replaced,17,1) +
   substring(replaced,18,1) +
   substring(replaced,19,1)
   ) =5 AND
   (substring(rev_rep,2,1)=0) AND
   (
   substring(replaced,6,1) +
   substring(replaced,7,1) +
   substring(replaced,8,1) +
   substring(replaced,9,1) +
   substring(replaced,10,1) +
   substring(replaced,11,1) +
   substring(replaced,12,1) +
   substring(replaced,13,1) +
   substring(replaced,14,1) +
   substring(replaced,15,1) +
   substring(replaced,16,1) +
   substring(replaced,17,1

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 
The system is working fine.
There is no error message when logging on from the command line.
You just don't get to the prompt.
On the web you obviously get a time-out error.
We are running 4.0.12-standard-log

I guess the substring function doesn't make any use of temp tables etc since
it is working on a per record basis.

When we are using a moderate table when using select count(distinct
field_name) from table while running the two queries
we get the following error message:

Error5 Out of memory
= i/o error.

Other sql statements run fine. Of course you had be logged on to the mysql
server already.



B

- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 2:51 PM
Subject: Re: no one can log on any more


What error message, if any is thrown when someone attempts to log in when
the two queries are running? Is the system as a whole performing slowly?
What version of MySQL are you running?


 Original Message 

On 3/2/04, 1:37:34 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
no one can log on any more:


 table: text_data
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows 133856002
 Extra: Using where

 No temp tables created, no i/o problem, no swapping.


 B
 - Original Message -
 From: [EMAIL PROTECTED]
 To: Bernd Jagla [EMAIL PROTECTED]
 Cc: mysql [EMAIL PROTECTED]
 Sent: Tuesday, March 02, 2004 2:34 PM
 Subject: Re: no one can log on any more


 Can you post the explain plan? I would be interested to see if temp table
 tables are being created or if the system is swapping or if the the temp
 tables are so large that the entire system begins to act poorly.

  Original Message 

 On 3/2/04, 12:35:20 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
 no one can log on any more:


  There are no indices on the new table.
  Top looks normal: approx 100-150% usage (out of 800% [8 cpus])
  There is no major i/o problem: sar -d says that less than 2% of io are
 used.

  The only problems in this direction might be connected with memory and
 the
  associated parameters in mysql.
  The file system cache is 5GB. I hope this is no problem. mysqld uses
 about
  54619 K (gmemusage)
  Top shows the actual size as 1794M and Res=1609

  The problem only occurs when I do two similar queries at the same time.
 Both
  queries access the same table for read (not write). Might this be the
  problem? How would I solve this one?

  Bernd

  - Original Message -
  From: [EMAIL PROTECTED]
  To: Bernd Jagla [EMAIL PROTECTED]
  Cc: mysql [EMAIL PROTECTED]
  Sent: Tuesday, March 02, 2004 1:17 PM
  Subject: Re: no one can log on any more


  When the queries in question are running, what does top look like? With
  an insert that large it may be beneficial to disable any keys that may
be
  active on the target table and re-enable them after the data has been
  loaded.

   Original Message 

  On 3/2/04, 12:08:05 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding
Re:
  no one can log on any more:


   An explain on the select statements reveals that no index is used and
  that
   all 133856002 rows are searched.

   Do you think that using a new index would make much of difference?
   I think it has something to do with some system/mysql parameters that
 can
  be
   adjusted.
   I also don't understand why no one else can log on to the mysql
system.

   If you or someone could explain or point me to a documentation on this
 it
   would be pretty close to what I want.

   Thanks for your help

   Bernd

   - Original Message -
   From: [EMAIL PROTECTED]
   To: Bernd Jagla [EMAIL PROTECTED]
   Cc: mysql [EMAIL PROTECTED]
   Sent: Tuesday, March 02, 2004 1:02 PM
   Subject: Re: no one can log on any more


   What does the explain plan reveal?

Original Message 

   On 3/2/04, 9:05:17 AM, Bernd Jagla [EMAIL PROTECTED] wrote regarding
no
   one can log on any more:


Hi there,

I have some weird sql statements that seem to eat up all resources
 from
mysqld.
Once I have two of them running at the same time no one can log on
any
more. Simple queries involving distinct get a out of memory error.

We have tried changing the index buffer size from 256M to 2G, no
 change.
We have 7G of memory on an IRIX system with 8 cpus.
No replication.

Do you have any idea what is happening?

Thanks a lot

Bernd


the sql statements look like this:

insert into rule2
select id
from text_uniq_bin
where (substring(rev_rep,1,1)=1) AND
(
substring(replaced,1,1) +
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)
) 2 AND
(substring(replaced,3,1)=0) AND
(
substring(replaced,12,1) +
substring(replaced,13,1) +
substring(replaced

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 

- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 3:14 PM
Subject: Re: no one can log on any more


The database server becomes unresponsive to new threads?
What is the result of the following equation?
key_buffer_size + ( (read_buffer_size + sort_buffer_size) * max
_connections )

256 +((512 +512)*100)=100.25Gb

Yup, thats bigger that our physicall memory (100 Gb  8 Gb)!!!
So I guess that might be the problem ;-)

Does the i/o error also relate to this problem?

Is this larger than you physicall memory?

 Original Message 

On 3/2/04, 2:04:37 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
no one can log on any more:


 The system is working fine.
 There is no error message when logging on from the command line.
 You just don't get to the prompt.
 On the web you obviously get a time-out error.
 We are running 4.0.12-standard-log

 I guess the substring function doesn't make any use of temp tables etc
since
 it is working on a per record basis.

 When we are using a moderate table when using select count(distinct
 field_name) from table while running the two queries
 we get the following error message:

 Error5 Out of memory
 = i/o error.

 Other sql statements run fine. Of course you had be logged on to the
mysql
 server already.



 B

 - Original Message -
 From: [EMAIL PROTECTED]
 To: Bernd Jagla [EMAIL PROTECTED]
 Cc: mysql [EMAIL PROTECTED]
 Sent: Tuesday, March 02, 2004 2:51 PM
 Subject: Re: no one can log on any more


 What error message, if any is thrown when someone attempts to log in when
 the two queries are running? Is the system as a whole performing slowly?
 What version of MySQL are you running?


  Original Message 

 On 3/2/04, 1:37:34 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re:
 no one can log on any more:


  table: text_data
  type: ALL
  possible_keys: NULL
  key: NULL
  key_len: NULL
  ref: NULL
  rows 133856002
  Extra: Using where

  No temp tables created, no i/o problem, no swapping.


  B
  - Original Message -
  From: [EMAIL PROTECTED]
  To: Bernd Jagla [EMAIL PROTECTED]
  Cc: mysql [EMAIL PROTECTED]
  Sent: Tuesday, March 02, 2004 2:34 PM
  Subject: Re: no one can log on any more


  Can you post the explain plan? I would be interested to see if temp
table
  tables are being created or if the system is swapping or if the the temp
  tables are so large that the entire system begins to act poorly.

   Original Message 

  On 3/2/04, 12:35:20 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding
Re:
  no one can log on any more:


   There are no indices on the new table.
   Top looks normal: approx 100-150% usage (out of 800% [8 cpus])
   There is no major i/o problem: sar -d says that less than 2% of io are
  used.

   The only problems in this direction might be connected with memory and
  the
   associated parameters in mysql.
   The file system cache is 5GB. I hope this is no problem. mysqld uses
  about
   54619 K (gmemusage)
   Top shows the actual size as 1794M and Res=1609

   The problem only occurs when I do two similar queries at the same
time.
  Both
   queries access the same table for read (not write). Might this be the
   problem? How would I solve this one?

   Bernd

   - Original Message -
   From: [EMAIL PROTECTED]
   To: Bernd Jagla [EMAIL PROTECTED]
   Cc: mysql [EMAIL PROTECTED]
   Sent: Tuesday, March 02, 2004 1:17 PM
   Subject: Re: no one can log on any more


   When the queries in question are running, what does top look like?
With
   an insert that large it may be beneficial to disable any keys that may
 be
   active on the target table and re-enable them after the data has been
   loaded.

Original Message 

   On 3/2/04, 12:08:05 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding
 Re:
   no one can log on any more:


An explain on the select statements reveals that no index is used
and
   that
all 133856002 rows are searched.

Do you think that using a new index would make much of difference?
I think it has something to do with some system/mysql parameters
that
  can
   be
adjusted.
I also don't understand why no one else can log on to the mysql
 system.

If you or someone could explain or point me to a documentation on
this
  it
would be pretty close to what I want.

Thanks for your help

Bernd

- Original Message -
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 1:02 PM
Subject: Re: no one can log on any more


What does the explain plan reveal?

 Original Message 

On 3/2/04, 9:05:17 AM, Bernd Jagla [EMAIL PROTECTED] wrote
regarding
 no
one can log on any more:


 Hi there,

 I have some weird sql statements that seem to eat up all resources
  from
 mysqld.
 Once I have two of them running at the same time no one can log on
 any
 more

Re: no one can log on any more

2004-03-02 Thread Bernd Jagla 

- Original Message - 
From: [EMAIL PROTECTED]
To: Bernd Jagla [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, March 02, 2004 3:43 PM
Subject: Re: no one can log on any more


Are you sure you allotted 512MB for both read_buffer and 
sort_buffer_size? Try reducing these values so that the total is roughly 
80 percent of your total physical memory.

 Original Message 

On 3/2/04, 2:26:31 PM, Bernd Jagla [EMAIL PROTECTED] wrote regarding Re: 
no one can log on any more:


 - Original Message -
 From: [EMAIL PROTECTED]
 To: Bernd Jagla [EMAIL PROTECTED]
 Cc: mysql [EMAIL PROTECTED]
 Sent: Tuesday, March 02, 2004 3:14 PM
 Subject: Re: no one can log on any more


 The database server becomes unresponsive to new threads?
 What is the result of the following equation?
 key_buffer_size + ( (read_buffer_size + sort_buffer_size) * max
 _connections )

 256 +((512 +512)*100)=100.25Gb

 Yup, thats bigger that our physicall memory (100 Gb  8 Gb)!!!
 So I guess that might be the problem ;-)

 Does the i/o error also relate to this problem?

 Is this larger than you physicall memory?



So I changed the variables now:

512M + (8M +8M)*100 = 2.1Gb

Still same problem.
But now the distinct works.

I still can't log on when both queries are running

B



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

complicated select statements and out of memory errors

2004-03-01 Thread Bernd Jagla 
Hi there,

I have some rather complicated sql statements that seem to eat up all resources from 
mysqld.
Once I have two of them running at the same time no one can log on any more. Simple 
queries involving distinct get a out of memory error.

We have tried changing the index buffer size from 256M to 2G, no change. We have 7G of 
memory on an IRIX system with 8 cpus.
No replication.

Do you have any idea what is happening?

Thanks a lot

Bernd


the sql statements look like this:

insert into rule2 
select id
from text_uniq_bin 
where (substring(rev_rep,1,1)=1) AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) 2 AND 
(substring(replaced,3,1)=0) AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) 5
;

AND 

insert into rule3 
select id
from text_uniq_bin
where (substring(rev_rep,1,1)=1) AND 
(
substring(replaced,1,1) + 
substring(replaced,2,1) +
substring(replaced,3,1) +
substring(replaced,4,1) +
substring(replaced,5,1) +
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1)  
) 2 AND 
(substring(rev_rep,3,1)=0) AND 
(
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) =5 AND
(substring(rev_rep,2,1)=0) AND 
(
substring(replaced,6,1) +
substring(replaced,7,1) +
substring(replaced,8,1) +
substring(replaced,9,1) + 
substring(replaced,10,1) + 
substring(replaced,11,1) + 
substring(replaced,12,1) + 
substring(replaced,13,1) +
substring(replaced,14,1) +
substring(replaced,15,1) +
substring(replaced,16,1) +
substring(replaced,17,1) +
substring(replaced,18,1) +
substring(replaced,19,1)  
) 7

large insert with load

2004-02-23 Thread Bernd Jagla 
Hi,

we have a big problem with inserting large amounts of data (2GB) into our MyISAM 
databank (version 4.0.12).
The dataset  is loaded in using 
LOAD DATA infile 'bla' ignore into table tab fields terminated by ' ' optionally 
enclosed by '' (int1, int2, int3, date);

The format of the text file is...
int1 int2 int3 'date' 

...with white spaces between each field.  After loading the data, there seems to be 
inconsistencies between the text-input data and the data in the mySQL table.

The problem seems to be localized to int3 and date, which shows integers that have 20 
appended to the original data.  (Ie.  if int3 is 15, it would be in the database as 
2015)  and date is '-00-00'.

Any suggestions?

Thanks

Bernd

User defined under SGI-IRIS64 6.5

2003-10-09 Thread Bernd Jagla 
Hi,

I wanted to add some new user defined functions (UDF) to mysql 4.0.12-standard-log.
I read in the manual that it is possible if you can start with the option 
--with-mysqld-ldflags=-rdynamic.
Then I found that my distribution is most likely build with --disable-shared, which 
makes me believe that I cant use any UDFs with the current version and that it is 
probably not save to recompile without the  --disable-shared.

So, my questions are: 
Has anyone done this already on IRIX 6.5? 
Can you give me some hints on how to start? 
If I have to recompile, what do I have to do? 
Can you give me some examples?

Thanks a lot for your time.

Bernd

 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.

processlist state = null

2003-08-14 Thread Bernd Jagla 
Dear list,

does anyone know what it means when it says state = NULL doing the show processlist?

Thanks

Bernd

 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.

sort-records

2003-07-03 Thread Bernd Jagla 
Hi there,

I wanted to sort the records in one of my tables (205,000,000 entries) to test if this 
will give me any speed increase.
It is an MyISAM table, and was trying to use myisamchk -R 2 user.
But what I get in return is a core dump and that the index file is not working 
anymore...

I am using MySQL 4.0.12
on IRIX 6.5 release 15m

create statement:
CREATE TABLE `user` (
  `e mail` varchar(100) NOT NULL default '',
  `phoneNr` varchar(20) NOT NULL default '',
  `id` smallint(5) unsigned NOT NULL auto increment,
  `username` varchar(10) NOT NULL default '',
  `password` varchar(10) NOT NULL default '',
  `firstname` varchar(100) default '',
  `lastname` varchar(100) default '',
  `group ref` tinyint(3) unsigned default NULL,
  `comment` varchar(20) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
) TYPE=MyISAM; 

Unfortunately I can't find a way to get around this problem.

Can you help me?

Thanks a lot

Bernd

 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.

Fw: sort-records

2003-07-03 Thread Bernd Jagla 

- Original Message -
From: Bernd Jagla [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 1:14 PM
Subject: Re: sort-records


 Yup, I guess I set parameters too high...

 Thanks

 Bernd

 - Original Message -
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Thursday, July 03, 2003 1:09 PM
 Subject: Re: sort-records


 I forgot to ask you how big machine you use. Because mysql makes sort in
 temp table. If you set the parameters about tmp, heap, sort size are not
 proper, if will core dump. Your memory size and tmp(mysql tmp, not /tmp)
 disk size also can make core dump.


 - Original Message -
 From: Bernd Jagla [EMAIL PROTECTED]
 To: mysql [EMAIL PROTECTED]
 Sent: Thursday, July 03, 2003 12:49 PM
 Subject: sort-records


 Hi there,

 I wanted to sort the records in one of my tables (205,000,000 entries) to
 test if this will give me any speed increase.
 It is an MyISAM table, and was trying to use myisamchk -R 2 user.
 But what I get in return is a core dump and that the index file is not
 working anymore...

 I am using MySQL 4.0.12
 on IRIX 6.5 release 15m

 create statement:
 CREATE TABLE `user` (
   `e mail` varchar(100) NOT NULL default '',
   `phoneNr` varchar(20) NOT NULL default '',
   `id` smallint(5) unsigned NOT NULL auto increment,
   `username` varchar(10) NOT NULL default '',
   `password` varchar(10) NOT NULL default '',
   `firstname` varchar(100) default '',
   `lastname` varchar(100) default '',
   `group ref` tinyint(3) unsigned default NULL,
   `comment` varchar(20) default NULL,
   PRIMARY KEY  (`id`),
   UNIQUE KEY `username` (`username`)
 ) TYPE=MyISAM;

 Unfortunately I can't find a way to get around this problem.

 Can you help me?

 Thanks a lot

 Bernd


  =

  Please note that this e-mail and any files transmitted with it may be
  privileged, confidential, and protected from disclosure under
  applicable law. If the reader of this message is not the intended
  recipient, or an employee or agent responsible for delivering this
  message to the intended recipient, you are hereby notified that any
  reading, dissemination, distribution, copying, or other use of this
  communication or any of its attachments is strictly prohibited.  If
  you have received this communication in error, please notify the
  sender immediately by replying to this message and deleting this
  message, any attachments, and all copies and backups from your
  computer.







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

Re: RAID hardware suggestions/experience

2003-06-23 Thread Bernd Jagla 
Thanks to everybody for the nice discussion.

Just to let you know about  my (not necessary final) decisions:
We will upgrade our SCSI -II controller to an Ultra SCSI 160 controller
(always a good idea).
Next we are looking into buying a RAID-5 system from RAIDking.
While we do this we hope for the best

Thanks again for you kind help.

Bernd



 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.



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

Excel(2002) and mysql4

2003-06-23 Thread Bernd Jagla 
My costomers are using Excel, so I am looking into connecting Excel with MySql.
Does anyone know how to do this? 
Do you have a small example?

Thank you very much for your help

Bernd

 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.

RAID hardware suggestions/experience

2003-06-17 Thread Bernd Jagla 
Hi there,

Our databank with all tables and idices is about 130GB big. The biggest
limitations we encounter are on the I/O side.
Therefore we are willing to update our data storage system to a RAID system
(RAID 0+1, RAID 5, or RAID 10).

Has anyone experience with such RAID systems?
What should we buy?
From whom should we buy (We are located in New York City)?
Do you have any experience you want to share?

Thank you very much for your help and support!

Bernd



 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.



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

RAID hardware suggestions/experience

2003-06-17 Thread Bernd Jagla 
Sorry I forgot to mention:

We are using IRIS on an Origion2000, 7GB memory, 8 CPUs. I was thinking of
spending up to $10K.
I also wanted the redundant data for speeding up the seeks, I also need to
speed up the writes.

Bernd

 
 =
 
 Please note that this e-mail and any files transmitted with it may be 
 privileged, confidential, and protected from disclosure under 
 applicable law. If the reader of this message is not the intended 
 recipient, or an employee or agent responsible for delivering this 
 message to the intended recipient, you are hereby notified that any 
 reading, dissemination, distribution, copying, or other use of this 
 communication or any of its attachments is strictly prohibited.  If 
 you have received this communication in error, please notify the 
 sender immediately by replying to this message and deleting this 
 message, any attachments, and all copies and backups from your 
 computer.