This only says that you have a 8M query cache configured, it says nothing about how well it is used, that is in the SHOW STATUS variables instead.

But you shouldn't start with the query cache.

You should start with turning on the slow_query_log with a long_query_time=0 including the log_queries_not_using_indexes setting, let it run for a while and use the dumpslow script to find out which queries you should focus on.

Then you can come back here with these queries, the output from EXPLAIN {yourquery} (which will give you the execution plan), and the output from SHOW CREATE TABLE {yourTable(s)} which will show us your structure so that we can help you speed things up.

They don't really say anything, the key_efficiency figure is pretty good, but it only tells you how much the key_buffer is used of the indexes that you _have_. But it doesn't say if you have the correct indexes or not so the figure is still pretty moot.

As for the mem part I don't know what MySQL workbench is measuring since I don't use it myself.

But another thing that we can take a look at:
1. Get the complete SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS and attach them to this thread.

2. Write some info about what kind of hardware you have, disks, amount of RAM, if something else is running on the server apart from MySQL, etc.

I had thought that just one connection was being made, but on inspecting my code I have created different MySQL users for different parts of the site (Horses, Stallions and Products) - so I am making 3 connections.

I've got a feeling that later on in the code execution some data is written back to the DB, with yet another user account - so another connection is being made.

Not sure what I was thinking with these users - maybe security and limiting users to table access. The site has beeen subjected to many attacks over the years

EDIT

The question I was going to ask is:

Is there a way to combine these queries so I am making just one request to MySQL?

Last edited by Northie; February 2nd, 2011 at 04:14 AM.

I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

running "top" in SSH shows that mysqld is at the top with up to 300% CPU

What is the overall load on the machine?
Top does not always report accurate cpu usage for a multi threaded application like MySQL that can have a _lot_ of threads.

The overall summary in the header on the other hand will show the actual load on the server.

But looking at your configuration variables I can say that you still have default configuration which are very small settings.
And looking at your figures it looks like you are performing quite a lot of table_scans so I'm a bit surprised that you didn't get one single query in the slow_query_log.
Did it write anything to the file?
Is the log_queries_not_using_indexes set?

And are you using InnoDB or MyISAM table type?
It greatly affects what advice I should give you regarding which parameters to tune. If you don't know, just run a SHOW CREATE TABLE {yourTable} and you will see the ENGINE=... which is the table type.

Yes that is usually where you get the slow query log if you don't specify the absolute path to some place else.

These:

| Select_full_join | 11 |
| Select_scan | 8395 |

Indicate that you lack indexes.
The first one is more important since it means that you have a join query where you don't have an index on any of the columns part of the join. Which means it has to scan _all_ rows of table b for _every_ row of table a = nr of rows * nr of rows, which can get quite a big number fast.

But looking at your top output we can see that you have a lot of memory available so if you start by setting these in your my.cfg and restart MySQL:

Code:

innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2

(since I don't know how large your database really is I guessed a value of 1G for the pool size, but if the database is larger then you can increase this value to about 75% of available RAM on the server.)

Ok, I've added those indexes (it took nearly 70 seconds to add each index to the `horse` table) and increased the innodb_buffer_pool_size to 0.5G.

I've just looked at the size of the database. The DB size is 1.1G and all databases on the server total 1.2G so I'l up that limit a bit more...but the other databases have tables that are not InnoDB but MyISAM instead

I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

Yes the memory usage is now as it should be which means that basically all data for your InnoDB tables are cached in MySQL for fast retrieval, and only changes are written to the disk.

Originally Posted by Northie

...but the other databases have tables that are not InnoDB but MyISAM instead

That means that we should increase key_buffer_size a bit also.
But since it sounds like your MyISAM tables are very small (100M if i calculate correctly) you can set key_buffer_size to something small like 50M, if it's a bit larger than the indexes actually are it will just not use the extra memory.
This value should in contrast to the innodb_buffer_pool_size be set to about 25% of used memory by the MyISAM tables since it only caches the indexes of the MyISAM tables and _not_ the table itself. The innodb buffer pool caches both indexes and data.