About 256K is allocated at once even if thread is idle - they are used by default thread stack, net buffer etc.
if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size of memory might be allocated. But they are only allocated upon the need and freed once given stage of query is done
32bit limits into account and probably limit “mysqld” size to about 2.5GB
Thread buffers can be allocated more than once for each thread.
MyISAM may allocate buffer which is large enough to contain largest record in the given table which is held until table is closed.
For example you may find out increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections increases peak memory consumption just 30MB not 3000MB as you might have counted.
In practice however you would not have more than 2-3 connections doing it at the same time so you happen to be fine.

1Expand

What exactly is read_rnd_buffer_size | MySQL Performance Blog

read_rnd-buffer_size

Tags: MySQL, Optimization on 2008-06-18 and saved by2 people -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

MySQL Performance Presentations | MySQL Performance Blog

Tags: MySQL, Performance, Optimization, Slides on 2008-05-30 and saved by4 people -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

The innotop MySQL and InnoDB monitor at Xaprb

Tags: MySQL, InnoDB, Monitoring, Tools on 2008-05-27 and saved by4 people -All Annotations (0) -About

more fromwww.xaprb.com

1Expand

SHOW INNODB STATUS walk through | MySQL Performance Blog

Tags: MySQL, InnoDB on 2008-05-27 and saved by2 people -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

MySQL :: Tuning MySQL Server 4.0 Query cache.

query cache

Tags: MySQL, Caching on 2008-05-27 -All Annotations (0) -About

more fromwww-it.mysql.com

1Expand

What MySQL buffer cache hit rate should you target | MySQL Performance Blog

what hit ratio is good

Tags: MySQL, Caching, InnoDB on 2008-05-27 -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

How fast can you sort data with MySQL ? | MySQL Performance Blog

file sorting

Tags: MySQL, Tuning, Performance on 2008-05-26 and saved by2 people -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

How MySQL executes OrderBY

how mysql executes Order By

Tags: MySQL, how-to on 2008-05-26 -All Annotations (0) -About

MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 5.1.3 System Variables

mysql 4 system variables

Tags: MySQL, Tuning on 2008-05-22 -All Annotations (0) -About

more fromdev.mysql.com

MySQL :: MySQL 5.0 Reference Manual :: 5.1.3 System Variables

sytem variables

Tags: MySQL, Tuning on 2008-05-22 -All Annotations (0) -About

more fromdev.mysql.com

» 10 tips for optimizing mysql queries

Tags: MySQL, query_optimization, Tips on 2008-05-21 and saved by5 people -All Annotations (0) -About

more fromwww.whenpenguinsattack.com

Covering index and prefix indexes | MySQL Performance Blog

Covering indexes

Tags: MySQL, Index, Performance on 2008-04-16 -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

1Expand

MySQL: Power of covering index : peter_zaitsev:

covering index

Tags: MySQL, Index, Performance on 2008-04-16 -All Annotations (0) -About

more frompeter-zaitsev.livejournal.com

8Expand

How to exploit MySQL index optimizations at Xaprb

index optimizations in innodb
Covering indexes

Tags: MySQL, Optimization, InnoDB, DB_Design, Indexing on 2008-04-16 and saved by2 people -All Annotations (0) -About

more fromwww.xaprb.com

The InnoDB storage engine creates a clustered index for every table.
Once a query navigates to the leaf node of a secondary index, it knows two things: the values it used to navigate the index, and the primary key values of that row in the table.
covering index
f the Extra column includes the text “Using index,” the optimization is being used.
put the columns in the WHERE clause first in the index, then add the columns named in the SELECT clause at the end, unless they are included in the primary key.
If you only select the columns you need, your query might be able to use one of the optimizations
you select every column and the query uses a secondary index, there’s no way to do that, and the query will have to wander around indexes finding the rows it needs, then do other operations to get the actual values from the rows.
InnoDB secondary indexes already contain all columns from the primary key

6Expand

Do you always need index on WHERE column ? | MySQL Performance Blog

The reason why index scan is slower than table scan is that in case of the index scan the MySQL needs to perform more operations like :
read index, get pointer to row, get data from row,
that adds overhead to the operations.
Full table scan takes constant time no matter the cardinality, index traversing speed depends on the index cardinality

Tags: MySQL, Index, Performance on 2008-04-16 -All Annotations (0) -About

more fromwww.mysqlperformanceblog.com

1 - 20 of 53 Next › Last »

List 20 50 100

Notation: * = Private bookmark and comment|… = Clipping [?] | … = Public highlight [?]

Navneet Kumar's Related Tags

Selected tags

MySQL

Related tags

See More Top Contributors

Ken Wei Nicolas Perriault Glenn M humanclone Paul Terry Walhus Peter Smith

Related Groups on Diigo

AdminSysteme

Members (5)

bookmarks (82)
Coders

Members (149)

bookmarks (1082)
MySQL

Members (19)

bookmarks (88)

Navneet Kumar's Bookmarks tagged MySQL → View Popular

Navneet Kumar's Related Tags

See More Top Contributors

Related Groups on Diigo

Diigo - Highlight and Share the Web!