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

[jeffmic]

I believe this is already enabled:

query_cache_size = 256M
query_cache_limit = 4096M
query_cache_type = 1

[solo_400]

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 .

[jeffmic]

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 :-)

[wilt]

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.

[jeffmic]

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?

[wilt]

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.

[wilt]

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.

[RodG]

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.

[jeffmic]

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)

[RodG]

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