pgrep shows 2 processes!

[M.E. Koch]

Dear List,

I have one mysql 4.1.11 running wich just shows
2 Processes.
Other mysql environments I do have show more than
10 Processes.

I don't have no clue where this difference comes from.
The Only significant difference I see on `show variables`
with thees two servers are:

<10 PID`s Server2 PID`s Server
-
server_id = 0  |1
version   = 4.1.11-Debian_4-log  |  4.1.11-Debian_4sarge2
-

I think the two PID`s problem is the reason why this 
server is totally slow. Specially on INSERTS and UPDATES.

Thank you in advance!
yours
Mathias



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

AW: limited threads to two but 25 waiting?!

[M.E. Koch]

Hello:

After haveing a look at the `show processlist`
I realized that querys will be locked if there
is an update or insert statement otherwise
the `STATUS` is as regular "copying to temp",
"sorting" etc. So that not all querys will be
locked automatically. (But when checking there
where lots of insert intos so that every query
seemd to be locked)

some select querys

`SELECT kk2.k_id FROM katalog_katalog kk
LEFT JOIN katalog_katalog kk1 ON k_id=kk1.kat_id
LEFT JOIN katalog_katalog kk2 ON kk1.k_kat_id=kk2.kat_id
WHERE kk.kat_id=34678 AND kk1.k_kategorie_id=56
AND kk2.k_kategorie_id=24;


An insert into/update looks like this:

INSERT INTO katalog
SET kategorie_id=36, titel="foo foo bar",
artikel="bar bar bar foo"

The Update gets an additonal `WHERE id=1234`

about the create statements:
Two of many tables hopefully give an insight
//- snip

CREATE TABLE `katalog` (
  `id` int(11) NOT NULL auto_increment,
  `kategorie_id` int(11) NOT NULL default '0',
  `datum` datetime NOT NULL default '-00-00 00:00:00',
  `titel` varchar(200) collate latin1_german2_ci default NULL,
  `untertitel` text collate latin1_german2_ci,
  `einleitung` text collate latin1_german2_ci,
  `artikel` text collate latin1_german2_ci,
  `quelle` text collate latin1_german2_ci,
  `bild_id` int(11) default NULL,
  `txt1` text collate latin1_german2_ci,
  `txt2` text collate latin1_german2_ci,
  `txt3` text collate latin1_german2_ci,
  `txt4` text collate latin1_german2_ci,
  PRIMARY KEY  (`id`),
  KEY `kategorie_id_idx` (`kategorie_id`),
  KEY `datum_idx` (`datum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1
;

and


CREATE TABLE `katalog_katalog` (
  `kat_id` int(11) NOT NULL default '0',
  `kategorie_id` int(11) NOT NULL default '0',
  `k_id` int(11) NOT NULL default '0',
  `k_kategorie_id` int(11) NOT NULL default '0',
  KEY `kat_id_idx` (`kat_id`),
  KEY `kategorie_id_idx` (`kategorie_id`),
  KEY `k_id_idx` (`k_id`),
  KEY `k_kategorie_id_idx` (`k_kategorie_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci
PACK_KEYS=1;


//- snap

I do have written the skip-dbd in /etc/mysql/my.cnf
The idea with the not working skip-dbd came because
of the numbers shown in `show variables`
bdb_cache_size  | 8388600

May my guess was not right.

Thanx so far for the help!!!

yours
  mathias






>-Ursprüngliche Nachricht-
>Von: Gleb Paharenko [mailto:[EMAIL PROTECTED]
>Gesendet: Sonntag, 16. Oktober 2005 14:45
>An: mysql@lists.mysql.com
>Betreff: Re: limited threads to two but 25 waiting?!
>
>
>Hello.
>
>Please, send to the list your queries and CREATE statements for tables
>which are used by you queries. Include the output of SHOW PROCESSLIST.
>
>> 2. When writng in my.cnf the skip-bdb option still the server
>> reserves memmory for berklyDB aswell with innoDB. But writing
>> somthing wrong into the file will cause mysql to throw an error.
>
>Are you sure that server reserves the memory for BDB? According to
>
>  http://dev.mysql.com/doc/refman/5.0/en/bdb-start.html
>
>it shouldn't. It could show the values of different bdb related
>variables, but isn't using them. To what file are you trying to
>write?
>
>
>
>M.E. Koch wrote:
>> Hi,
>>
>> I have searched and tried and have no clue why the db on
>> 4.1.11-Debian_4sarge2 behaves like this.
>> I have no TABLE LOCK query anywhere in my code!
>> About the server (LAMP/ 2x3Mhz, 4GB RAM)
>>
>> 1. prob.
>> the mysql> show processlist gives me a list of 25 threads
>> waiting for there work.
>> even on heavy load `pgrep mysql` will just show two PIDs
>> doing somthing even if there are just SELECT queries on the
>> DB. therefor the server get's really slow.
>> The case get's even more worse if there is a UPDATE or INSERT
>> statement. (LOCK problem)
>>
>> 2. When writng in my.cnf the skip-bdb option still the server
>> reserves memmory for berklyDB aswell with innoDB. But writing
>> somthing wrong into the file will cause mysql to throw an error.
>>
>> I have no idea anymore where to look or what to check pls help.
>> any tuning-tipps are wellcome!
>>
>> mysql> show variables; gives me that
>>
>> +-+--+
>> | Variable_name   | Value|
>> +-+--+
>> | back_log| 50|
>> | basedir | /usr/ |
>> | bdb_cache_size  | 8388600   |
>> | bdb_home| 

limited threads to two but 25 waiting?!

[M.E. Koch]

Hi,

I have searched and tried and have no clue why the db on
4.1.11-Debian_4sarge2 behaves like this.
I have no TABLE LOCK query anywhere in my code!
About the server (LAMP/ 2x3Mhz, 4GB RAM)

1. prob.
the mysql> show processlist gives me a list of 25 threads
waiting for there work.
even on heavy load `pgrep mysql` will just show two PIDs
doing somthing even if there are just SELECT queries on the
DB. therefor the server get's really slow.
The case get's even more worse if there is a UPDATE or INSERT
statement. (LOCK problem)

2. When writng in my.cnf the skip-bdb option still the server
reserves memmory for berklyDB aswell with innoDB. But writing
somthing wrong into the file will cause mysql to throw an error.

I have no idea anymore where to look or what to check pls help.
any tuning-tipps are wellcome!

mysql> show variables; gives me that

+-+--+
| Variable_name   | Value|
+-+--+
| back_log| 50|
| basedir | /usr/ |
| bdb_cache_size  | 8388600   |
| bdb_home|   |
| bdb_log_buffer_size | 0 |
| bdb_logdir  |   |
| bdb_max_lock| 1 |
| bdb_shared_data | OFF   |
| bdb_tmpdir  |   |
| binlog_cache_size   | 32768 |
| bulk_insert_buffer_size | 8388608   |
| character_set_client| latin1|
| character_set_connection| latin1|
| character_set_database  | latin1|
| character_set_results   | latin1|
| character_set_server| latin1|
| character_set_system| utf8  |
| character_sets_dir  | /usr/share/mysql/charsets/|
| collation_connection| latin1_swedish_ci |
| collation_database  | latin1_german2_ci |
| collation_server| latin1_german2_ci |
| concurrent_insert   | ON|
| connect_timeout | 5 |
| datadir | /var/lib/mysql/   |
| date_format | %Y-%m-%d  |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON|
| delayed_insert_limit| 100   |
| delayed_insert_timeout  | 300   |
| delayed_queue_size  | 1000  |
| expire_logs_days| 0 |
| flush   | OFF   |
| flush_time  | 0 |
| ft_boolean_syntax   | + -><()~*:""&||
| ft_max_word_len | 84|
| ft_min_word_len | 4 |
| ft_query_expansion_limit| 20|
| ft_stopword_file| (built-in)|
| group_concat_max_len| 1024  |
| have_archive| YES   |
| have_bdb| DISABLED  |
| have_blackhole_engine   | NO|
| have_compress   | YES   |
| have_crypt  | YES   |
| have_csv| YES   |
| have_example_engine | NO|
| have_geometry   | YES   |
| have_innodb | DISABLED  |
| have_isam   | YES   |
| have_ndbcluster | DISABLED  |
| have_openssl| NO|
| have_query_cache| YES   |
| have_raid   | YES   |
| have_rtree_keys | YES   |
| have_symlink| YES   |
| init_connect|   |
| init_file   |   |
| init_slave  |   |
| innodb_additional_mem_pool_size | 1048576   |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb   | 0 |
| innodb_buffer_pool_size | 8388608   |
| innodb_data_file_path   |   |
| innodb_data_home_dir|   |
| innodb_fast_shutdown| ON|
| innodb_file_io_threads  | 4 |
| innodb_file_per_table   | OFF   |
| innodb_flush_log_at_trx_commit  | 1 |
| innodb_flush_method |   |
| innodb_force_recovery   | 0

SELECT by LEFT(col,1)="{letter}" ?? improvements and sarge update to 4.1

[M.E. Koch]

Hello dear all-mighty list :)

I do run mysql in 4.0.24 and have the following very slow query.

select tbl3.colname from tbl
.. (some left joins)
where
left(colname,1)="{letter}"

the tbl.col has no index nore will it ever get one because auf
the tbl structre. (this would make no sense becaus only 8tsd.)
rows in the table total 82tsd rows have to get selected like
this.

As you might guess the query is much too slow.

Now I have the chance (and time) to do the following.

Doing a seperate table for this on col like

++-+
| id |   colnames  |
++-+

how would i have to index it? I think colnames should get
a fulltext-field but how would it beccorect? Just one letter
or do the whole field.
colnames type = text (varchar might be ok with 255 chars).

What would you suggest?

==
the other thing is that I would like to update to 4.1.x on 
my debian sarge. Has anybody done this yet?

do I have to just: apg-get remove mysql ?
and apg-get install mysql-4.1

wich packages are needed.. will there be much mor performance?


Bon Vibes and Thank you

mathias















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

select range of matched word in text field

[M.E. Koch]

hello,

I wonder if it is possible to match a word like with instr()
and get as a result just 10 words bevor and ten words after
the matched word. (like google presents the matched query)

I would need a select with a replacement and a fitting where
clause which could be done with isset.

I have isam tables.. and wonder if this is even possible


best regards

  mathias




 _/_/_/  Mathias E. Koch
 _/_/_/  Florastrasse 46
 _/_/_/  47799 Krefeld

 Development+ Communications+ Strategies
 Tel.: +49 (0)2151 15 05 28 / Mob.: +49 (0)171 64 50 137
   -NMA-   
 



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