Hello Rick,
thanks for you answer, I'll give some answers to your additionally questions and will get my team to work on the rest.
* Query cache? How much is allocated to it? Is it turned on?
* SHOW VARIABLES LIKE "query%";
* Don't know that it will solve your problem, but please try changing to
* query_cache_type = OFF
mysql> show variables like '%query%';
+------------------------------+---------------------------------------------+
| Variable_name | Value |
+------------------------------+---------------------------------------------+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1073741824 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /local/rg-mysql_master/data/mysqld-slow.log |
+------------------------------+---------------------------------------------+
12 rows in set (0.00 sec)
It's on, we'll try turning it off, I'll report back when we tested that.
* Unrelated notes:
* set('yes','no') -- perhaps you meant ENUM, not SET. Or can it really be both yes and no?
Ups, haven't spotted that one, no of course not, we'll change that.
* How many queries per second are you running? (Questions/Uptime).
| Queries | 521936097 |
| Questions | 492491898 |
| Uptime | 782270 |
~667 queries/sec
~630 question/sec
* I see 6 indexes, each with a single field. Sometimes that indicates the the author does not know the power of "compound indexes".
We have discussed this. We'll probably even drop 4 of those 6 indexes, as they aren't realy good ones (to small variability(?) of values). As the table isn't that big, it shouldn't pose a problem, at least not for further debugging processes.
* Is MySQL 64-bit? Some of the settings might cause trouble in 32-bit.
| basedir | /local/rg-mysql_master/bin/mysql-5.1.36-solaris10-x86_64/ |
I pretty darn hope so, but I'll get my team to double check it.
* Oh!
* | innodb_log_file_size | 67108864 |
* | innodb_log_files_in_group | 48 |
* I've never seen that second value be anything other than 2 or 3. A guess: 48 is not good. Plz try changing it thus:
Ok, we'll do so. I did some research but couldn't find anything precise about that setting. Do I understand this correctly?: This is the number of innodb bin log files MySQL keeps and rotates through? So after each backup they are pretty worthless, aren't they?
Oh well, as I said, we'll go ahead and test your ideas, I'll report back on monday with any results we got.
Regards,
Sebastian