AW: Locks on Heap tables

2005-07-07 Thread Hannes Rohde 
   | 4294967295
|
| myisam_data_pointer_size| 4
|
| myisam_max_extra_sort_file_size | 2147483648
|
| myisam_max_sort_file_size   | 2147483647
|
| myisam_recover_options  | OFF
|
| myisam_repair_threads   | 1
|
| myisam_sort_buffer_size | 8388608
|
| ndb_autoincrement_prefetch_sz   | 32
|
| ndb_force_send  | ON
|
| ndb_use_exact_count | ON
|
| ndb_use_transactions| ON
|
| net_buffer_length   | 16384
|
| net_read_timeout| 30
|
| net_retry_count | 10
|
| net_write_timeout   | 60
|
| new | OFF
|
| old_passwords   | ON
|
| open_files_limit| 32000
|
| pid_file| /var/run/mysqld/mysqld.pid
|
| port| 3306
|
| preload_buffer_size | 32768
|
| protocol_version| 10
|
| query_alloc_block_size  | 8192
|
| query_cache_limit   | 1048576
|
| query_cache_min_res_unit| 4096
|
| query_cache_size| 10485760
|
| query_cache_type| DEMAND
|
| query_cache_wlock_invalidate| OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size  | 2048
|
| read_buffer_size| 1044480
|
| read_only   | OFF
|
| read_rnd_buffer_size| 262144
|
| relay_log_purge | ON
|
| rpl_recovery_rank   | 0
|
| secure_auth | OFF
|
| server_id   | 1
|
| skip_external_locking   | ON
|
| skip_networking | OFF
|
| skip_show_database  | OFF
|
| slave_net_timeout   | 3600
|
| slow_launch_time| 2
|
| socket  | /var/run/mysqld/mysqld.sock
|
| sort_buffer_size| 1048568
|
| sql_mode|
|
| storage_engine  | MyISAM
|
| sync_binlog | 0
|
| sync_frm| ON
|
| system_time_zone| CEST
|
| table_cache | 256
|
| table_type  | MyISAM
|
| thread_cache_size   | 64
|
| thread_stack| 131072
|
| time_format | %H:%i:%s
|
| time_zone   | SYSTEM
|
| tmp_table_size  | 33554432
|
| tmpdir  | /tmp
|
| transaction_alloc_block_size| 8192
|
| transaction_prealloc_size   | 4096
|
| tx_isolation| REPEATABLE-READ
|
| version | 4.1.9-Debian_0.dotdeb.0-log
|
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24:
(January 11, 2005) |
| version_comment | Source distribution
|
| version_compile_machine | i386
|
| version_compile_os  | pc-linux-gnu
|
| wait_timeout| 10
|
+-+-
-+


Herzliche Grüße
Hannes Rohde

¯
incoWEB.de - agentur für neue medien
Stapenhorststr. 10
D-45329 Essen

[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
http://www.incoWEB.de

Phone & Fax 0700-0-4626932
0700-0-INCOWEB

Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g.
Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder
Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese
E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich.

This email, its content and any files transmitted with it are intended
solely for the addressee(s). Access, distribution or copying by any other
party is not permitted. If you are not the intended recipient, then please
notify us immediately by returning it to the originator. 


-Ursprüngliche Nachricht-
Von: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 7. Juli 2005 10:35
An: mysql@lists.mysql.com
Betreff: Re: Locks on Heap tables

Hello.

Are you sure that your server doesn't swap? Providing output
of 'SHOW STATUS', 'SHOW VARIABLES' and your table definition
could give more information for suggestions. Also, if you have
a hash index on a MEMORY table that has a high degree of key 
duplication (many index entries containing the same value), 
updates to the table that affect key values and all deletes are 
significantly slower. The degree of slowdown is proportional to the
degree of duplication (or, inversely proportional to the index cardinality).
You can use a BTREE index to avoid this problem.



"Hannes Rohde" <[EMAIL PROTECTED]> wrote:
> Hello everyone,
> 
>We are using MySQL as the database backend on quite a big portal
> page with about 50.000 users and 3 mio. PIs per day. MySQL is as well =
> the
> backend for the (php) session management. We are using a heap for that =
> case
> as we

Re: Locks on Heap tables

2005-07-07 Thread Gleb Paharenko 
Hello.



Are you sure that your server doesn't swap? Providing output

of 'SHOW STATUS', 'SHOW VARIABLES' and your table definition

could give more information for suggestions. Also, if you have

a hash index on a MEMORY table that has a high degree of key 

duplication (many index entries containing the same value), 

updates to the table that affect key values and all deletes are 

significantly slower. The degree of slowdown is proportional to the

degree of duplication (or, inversely proportional to the index cardinality).

You can use a BTREE index to avoid this problem.







"Hannes Rohde" <[EMAIL PROTECTED]> wrote:

> Hello everyone,

> 

>We are using MySQL as the database backend on quite a big portal

> page with about 50.000 users and 3 mio. PIs per day. MySQL is as well =

> the

> backend for the (php) session management. We are using a heap for that =

> case

> as well as for instance phpbb does.=20

> Lately we are experiencing long lasting table locks due to deletes or

> updates on the session table. I know that heap tables only support table

> wide locking, but shouldn't those locks be gone quite fast? I have =

> already

> checked the obvious reasons for this kind of behaviour like swapping but =

> I

> couldn't find anything. Even googling didn't bring anything useful up.

> Hopefully someone got some ideas to solve this problem :-)

> 

> Thank you in advance

> Hannes Rohde

> 

> =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=

> =AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF=AF

> incoWEB.de - agentur f=FCr neue medien

> Stapenhorststr. 10

> D-45329 Essen

> 

> [EMAIL PROTECTED] 

> http://www.incoWEB.de

> 

> Phone & Fax 0700-0-4626932

> 0700-0-INCOWEB

> 

> Diese E-Mail enth=E4lt vertrauliche Informationen, die nur f=FCr den =

> o.g.

> Empf=E4nger bestimmt sind! Jede Kenntnisnahme, Verteilung oder

> Vervielf=E4ltigung durch andere Personen ist nicht zul=E4ssig. Sollten =

> Sie diese

> E-Mail irrt=FCmlich erhalten haben, melden Sie uns dies bitte =

> unverz=FCglich.

> 

> This email, its content and any files transmitted with it are intended

> solely for the addressee(s). Access, distribution or copying by any =

> other

> party is not permitted. If you are not the intended recipient, then =

> please

> notify us immediately by returning it to the originator.=20

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [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]

Locks on Heap tables

2005-07-07 Thread Hannes Rohde 
Hello everyone,

We are using MySQL as the database backend on quite a big portal
page with about 50.000 users and 3 mio. PIs per day. MySQL is as well the
backend for the (php) session management. We are using a heap for that case
as well as for instance phpbb does. 
Lately we are experiencing long lasting table locks due to deletes or
updates on the session table. I know that heap tables only support table
wide locking, but shouldn't those locks be gone quite fast? I have already
checked the obvious reasons for this kind of behaviour like swapping but I
couldn't find anything. Even googling didn't bring anything useful up.
Hopefully someone got some ideas to solve this problem :-)

Thank you in advance
Hannes Rohde

¯
incoWEB.de - agentur für neue medien
Stapenhorststr. 10
D-45329 Essen

[EMAIL PROTECTED] 
http://www.incoWEB.de

Phone & Fax 0700-0-4626932
0700-0-INCOWEB

Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g.
Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder
Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese
E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich.

This email, its content and any files transmitted with it are intended
solely for the addressee(s). Access, distribution or copying by any other
party is not permitted. If you are not the intended recipient, then please
notify us immediately by returning it to the originator. 



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