Does anyone else have this issue with search result long delays?

Hi y'all

For quite a while, I (along with Dr Byte) have been trying to isolate issues with client searches taking a long time for a result. Last weekend, Dr Byte coded the search button so that multiple hits, or hitting the Enter/Return keys will not cause additional sending of the search to the database.

Although that fixed that problem, mysql clogging still persists in this manner.

When 2 or more customers perform a search on my website at nearly the same time, they all must be completed before any result is given to the clients. Therefore clogging the mysql so that search results take well of 10 seconds to 30 seconds (depending on how many search at the same time). Of course, it not EXACTLY at the same time, but as long as the first search has started and then someone else searches before the first is done, both are delayed. This is actually no different than before the 'multi-click' removal code on the search button was created by Dr Byte, but in this instance, it's multi searching from more than one client as the problem.

I always watch the (OSX) activity monitor when I do test searches and I see my 8 core meter only using one core for the search at around 80% until the search is completed (2-3 seconds). BUT, when it's clogged with multiple searches, ALL 8 CORES light up at 20-40% until all the searches are completed (10-25 seconds). Why do you think this is happening with so few clients viewing my website and so few searches causing a problem?

Here is my setup:

ZenCart 1.5.1

OSX Mountain Lion Server 10.8.3 (Apache) with 22 GB of memory (and running on an SSD drive).

HTTP Server: Apache/2.2.22 (Unix) DAV/2 PHP/5.3.15 with Suhosin-Patch mod_ssl/2.2.22 OpenSSL/0.9.8x

Database: MySQL 5.5.29-log

LARGE DATABASE of products - nearly 54,000
over a million attributes

mysql-slow.log enabled

Here is the data from mysql-slow.log with a clogged search this morning.

# Time: 130803 10:22:22
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 13.694394  Lock_time: 0.000128 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375539742;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%heart%'                                          OR p.products_model                                          LIKE 'heart' OR (mtpd.metatags_keywords                         LIKE '%heart%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803 10:22:23
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 14.734967  Lock_time: 0.000107 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375539743;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%love%'                                          OR p.products_model                                          LIKE 'love' OR (mtpd.metatags_keywords                         LIKE '%love%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803 10:22:34
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 22.077050  Lock_time: 0.000114 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375539754;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-06482%'                                          OR p.products_model                                          LIKE 'k-06482' OR (mtpd.metatags_keywords                         LIKE '%k-06482%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803 10:22:44
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 22.086718  Lock_time: 0.000164 Rows_sent: 10  Rows_examined: 437676
SET timestamp=1375539764;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%heart%'                                          OR p.products_model                                          LIKE 'heart' OR (mtpd.metatags_keywords                         LIKE '%heart%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 21.347180  Lock_time: 0.000178 Rows_sent: 10  Rows_examined: 430999
SET timestamp=1375539764;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%love%'                                          OR p.products_model                                          LIKE 'love' OR (mtpd.metatags_keywords                         LIKE '%love%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# Time: 130803 10:22:45
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 10.872626  Lock_time: 0.000180 Rows_sent: 1  Rows_examined: 440230
SET timestamp=1375539765;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%k-06482%'                                          OR p.products_model                                          LIKE 'k-06482' OR (mtpd.metatags_keywords                         LIKE '%k-06482%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;

and

# Time: 130803  9:52:16
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 11.136167  Lock_time: 0.000170 Rows_sent: 1  Rows_examined: 440230
SET timestamp=1375537936;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%t-26460%'                                          OR p.products_model                                          LIKE 't-26460' OR (mtpd.metatags_keywords                         LIKE '%t-26460%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# Time: 130803  9:52:17
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 11.815661  Lock_time: 0.000110 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375537937;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%99094020%'                                          OR p.products_model                                          LIKE '99094020' OR (mtpd.metatags_keywords                         LIKE '%99094020%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803  9:52:35
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 24.010266  Lock_time: 0.000101 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375537955;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%99148047%'                                          OR p.products_model                                          LIKE '99148047' OR (mtpd.metatags_keywords                         LIKE '%99148047%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803  9:52:37
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 24.066022  Lock_time: 0.000201 Rows_sent: 1  Rows_examined: 440231
SET timestamp=1375537957;
select count(distinct p.products_id) as total  FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%kp-06493%'                                          OR p.products_model                                          LIKE 'kp-06493' OR (mtpd.metatags_keywords                         LIKE '%kp-06493%'                         AND mtpd.metatags_keywords !='')) ));
# Time: 130803  9:52:39
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 22.089239  Lock_time: 0.000151 Rows_sent: 1  Rows_examined: 440233
SET timestamp=1375537959;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%99094020%'                                          OR p.products_model                                          LIKE '99094020' OR (mtpd.metatags_keywords                         LIKE '%99094020%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;
# Time: 130803  9:52:45
# User@Host: zencart[zencart] @ localhost [127.0.0.1]
# Query_time: 10.762785  Lock_time: 0.000168 Rows_sent: 1  Rows_examined: 440233
SET timestamp=1375537965;
SELECT DISTINCT p.products_image, p.products_quantity ,  p.products_id, pd.products_name FROM (products p, products_description pd, categories c, products_to_categories p2c )              LEFT JOIN meta_tags_products_description mtpd              ON mtpd.products_id= p2c.products_id              AND mtpd.language_id = 1 WHERE (p.products_status = 1                AND p.products_id = pd.products_id                AND pd.language_id = 1                AND p.products_id = p2c.products_id                AND p2c.categories_id = c.categories_id  AND ((pd.products_name LIKE '%99148047%'                                          OR p.products_model                                          LIKE '99148047' OR (mtpd.metatags_keywords                         LIKE '%99148047%'                         AND mtpd.metatags_keywords !='')) )) order by pd.products_name  limit 10;

Here is my.cnf that has been tweeked with mysqltuner:

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 1000M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer = 16M
# record_buffer = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
query_cache_size = 128M
query_cache_limit = 4096M
query_cache_type = 1
# Try number of CPU's*2 for thread_concurrency
# thread_concurrency = 8

interactive_timeout = 20
wait_timeout = 20

max_connections = 100
max_user_connections = 25
max_connect_errors = 25
connect_timeout = 10
thread_cache_size = 256
tmp_table_size = 1024M
max_heap_table_size = 1024M
open_files_limit = 2000
table_cache = 1024

log-error=/var/log/mysqld.log

slow-query-log=1
slow-query-log-file=/var/log/mysql-slow.log

MySQLTuner results this morning:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3G (Tables: 633)
[--] Data in InnoDB tables: 53M (Tables: 37)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 58

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 20h 45m 3s (2M q [12.678 qps], 17K conn, TX: 14B, RX: 1B)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 2.2G global + 32.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 5.4G (24% of installed RAM)
[OK] Slow queries: 0% (54/2M)
[OK] Highest usage of available connections: 30% (30/100)
[OK] Key buffer size / total MyISAM indexes: 1000.0M/229.8M
[OK] Key buffer hit rate: 100.0% (1B cached / 35K reads)
[OK] Query cache efficiency: 72.3% (1M cached / 1M selects)
[!!] Query cache prunes per day: 171895
[OK] Sorts requiring temporary tables: 0% (66 temp sorts / 66K sorts)
[!!] Joins performed without indexes: 1179
[!!] Temporary tables created on disk: 37% (44K on disk / 117K total)
[OK] Thread cache hit rate: 99% (30 created / 17K connections)
[OK] Table cache hit rate: 43% (955 open / 2K opened)
[OK] Open file limit used: 70% (1K/2K)
[OK] Table locks acquired immediately: 99% (720K immediate / 720K locks)
[!!] Connections aborted: 10%
[OK] InnoDB data size / buffer pool: 53.9M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 16.0M, or always use indexes with joins)

So, my primary question here is why can't mysql perform multiple searches at the same time?

Any help would be greatly appreciated.

thank you,

Hi Jeff ,

How many products do you have on your store ?
Try to increase sort_buffer_size and read_buffer_size to 100M and see if you have better response time .

LARGE DATABASE of products - nearly 54,000
over a million attributes

btw, I have disabled thread_concurrency. From what I read, it's no longer used.
Am I correct?

Here's the updated my.cnf
(100M on the buffers brought it up to over the max ram limit - I adjusted to 50M from 4M instead)

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 1000M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 50M
read_buffer_size = 50M
join_buffer = 64M
# record_buffer = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
query_cache_size = 256M
query_cache_limit = 4096M
query_cache_type = 1
# Try number of CPU's*2 for thread_concurrency
# thread_concurrency = 8

interactive_timeout = 20
wait_timeout = 20

max_connections = 100
max_user_connections = 25
max_connect_errors = 25
connect_timeout = 10
thread_cache_size = 256
tmp_table_size = 1024M
max_heap_table_size = 1024M
open_files_limit = 2000
table_cache = 1024

log-error=/var/log/mysqld.log

slow-query-log=1
slow-query-log-file=/var/log/mysql-slow.log

As you see on advisor window , query joins via indexes are very important at this level . Optimizing / Tuning is not easy , check indexes on join keys , check if tables / indexes are fragmented and test . Can you provide explain plans for those queries ?

Maybe you will need to find a way to suggest to your customers to search based on a category or more categories in order to reduce response query time.

I'm a little lost in your last reply, but I do know most people just search on the product name using the search box. No categories at all. meta_tags_products_description/metatags_keywords looks for a 'song title's artist'. products_description/product_name looks for the song title. (it's a music website btw).
And products_products_model looks up the catalog number of a song we sells.

That is all I want to use within the sidebox search when a customer uses it.

As far as I've looked, all the indexes as created that I need. Do you see any other code that I should remove in my search data to streamline a faster result?

I did a search for "live" or "barbie" on your website and it doesn't seems to be slow :-)

It's not so much that it's slow when one person searches. As mentioned in my first post, everything clogs when 2 people are using the search within the same timeframe. I have 2 internet connections here, so I tested it by creating 2 different searches on each ip into the website and pressed search around the same time (or within a couple seconds). It caused both searches to delay and all the cores of my CPU to be become clogged instead of just 2 cores. With one search occurring, it works correctly, albeit 2.4 seconds or so with this many products.

Do a test also with query cache enabled ..
Take this example : http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html

I believe this is already enabled:

query_cache_size = 256M
query_cache_limit = 4096M
query_cache_type = 1

Sorry , I didn't see it.

My last bullet : skip-thread-priority http://support.apple.com/kb/TA23907 . I see you have more recent version , but who knows .

I put it in. Not really any difference with 2 searches at the same time. Both took a little over 10 seconds to complete. Thanks for your help on everything today :-)

Hi,

Not really sure that query cache optimisation is going to help much. It only really helps where you are replicating exact queries.

I'd be tempeted to increase your key_buffer_size. I realize mysql tuner is suggesting it's OK, but experience suggests that you should be pushing that up to 4gb.

You could also run your settings through the Percona wizard at https://tools.percona.com/wizard

Something else you can do, if you have the latest phpMyAdmin installed on your box isto try running one of the queries in phpMyAdmin with the performance stats box ticked. This will give a breakdown of the time taken to return the query and which parts of the process consume that time. e.g. if the bottleneck is down to slow input/output etc.

I'll try that later today - about to go out for the afternoon.

I'm still so confused as to why I cannot run 2 queries at the same time from different ips without it clogging both of them. Have you had that type of experience?

Hi,

There's a few reasons why it can happen, although have to say I've never seen it happen on just 2 queries.

I'm assuming here that your box is serving as a combined web/db server.

So where you are running shared resources on a server, sometimes getting to the bottom of a problem can be a painful process.

As an example. Asumme a crappy apache config that eats up memory when more than 1 client connects. Suddenly mysql has no live memory for it's caches etc. and your hitting swap for every query. Not saying that's the case here, just an illustration.

Using top here can sometimes help.

The actual queries that the search codes run, aren;t particularly well optimised. :)
But then again I know people with many more products than you who don't have your problem.

Lets consider another problem, what happens if your disk io is not up to scratch. Even though mysql maybe well tuned, then your processes will be clogged while the system tries to transfer all that data. Note, here this has nothing to do with disk speed.

It can be quite a frustrating processes. My lack of hair attests to this.

Hi

Also, was that the full my.cnf that you posted.

If so I would also add some other stuff.

Either
skip-networking
or
bind-address = 127.0.0.1

or even both just to be sure :)

and also

skip-name-resolve

I'm assuming here that your db is only ever accessed as localhost thru port 3306. e.g. you don;t allow external connections to access your db via a hostname, and secondly that you have no access rules that allow/deny access by hostname.

jeffmic:

I'm still so confused as to why I cannot run 2 queries at the same time from different ips without it clogging both of them. Have you had that type of experience?

It's possible that your "tmp_table_size" and "max_heap_table_size" are set too high @1024mb each.

More 'realistic' values are
tmp_table_size = 16M
max_heap_table_size = 32M

or perhaps
tmp_table_size = 32M
max_heap_table_size = 64M

What happens is if you allocate too much memory here, you'll run out of physical memory really quick, so the 'tmp' tables end up being swapped to disk, rather than available from RAM.

Cheers
Rod

ps. It is a bit of a balancing act here. Setting too low will also cause excessive disk swapping, albeit for a different reason.

RodG

thank you, but as soon as I changed it, mysqltuner.pl tells me it's too low.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 633)
[--] Data in InnoDB tables: 54M (Tables: 37)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 57

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 39s (998 q [25.590 qps], 14 conn, TX: 4M, RX: 154K)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 2.8G global + 142.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 16.7G (75% of installed RAM)
[OK] Slow queries: 0% (0/998)
[OK] Highest usage of available connections: 1% (1/100)
[OK] Key buffer size / total MyISAM indexes: 2.3G/229.9M
[OK] Key buffer hit rate: 98.7% (48K cached / 605 reads)
[!!] Query cache efficiency: 15.4% (144 cached / 933 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 71 sorts)
[!!] Temporary tables created on disk: 31% (16 on disk / 51 total)
[OK] Thread cache hit rate: 92% (1 created / 14 connections)
[OK] Table cache hit rate: 92% (92 open / 99 opened)
[OK] Open file limit used: 6% (145/2K)
[OK] Table locks acquired immediately: 100% (947 immediate / 947 locks)
[!!] Connections aborted: 21%
[OK] InnoDB data size / buffer pool: 54.9M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_limit (> 4G, or use smaller result sets)
tmp_table_size (> 32M)
max_heap_table_size (> 64M)

jeffmic:

RodG
thank you, but as soon as I changed it, mysqltuner.pl tells me it's too low.

I think a more important observation would be to see how the change affected the performance :)

Seriously though, the best value to use here is the lowest possible without causing excessive disk swapping. Although it may seem harmless to use the higher values, all it does is reserve memory that will be mostly 'wasted', and when a disk swap is required it needs to swap the entire allocation. Even a setting of 256mb will (in theory) provide a 4x speed boost when a swap is required (compared to a 1024mb file).

Cheers
Rod