created_tmp_tables

[telescopi]

I'm trying to kick some life into a heavily overworked vbb atm, upgrade is planned, but some time off yet :(

Although CPU Idle is 0% for long periods, and load is average 13, pages are loading without error - which is better than before when max_connections was 100 (150 now - has already hit that limit).

I'm just wondering about the buffers I have set - they are all default atm, and that tmp_tables thing has hit 25k after the mysql has been up for about 5 hours - is that too high?

Should I change me mysql buffers, would this speed things up?

Also, I've heard of these PHP cache things - are they to be trusted? Sounds too good to be true imo

For info:

status is:
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 43 |
| Created_tmp_tables | 25879 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 17093 |
| Handler_read_first | 26693 |
| Handler_read_key | 3498576 |
| Handler_read_next | 29006326 |
| Handler_read_rnd | 147078132 |
| Handler_update | 48189 |
| Handler_write | 893088 |
| Key_blocks_used | 7822 |
| Key_read_requests | 17100504 |
| Key_reads | 21097 |
| Key_write_requests | 32350 |
| Key_writes | 27474 |
| Max_used_connections | 150 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 63 |
| Open_files | 56 |
| Open_streams | 0 |
| Opened_tables | 6719 |
| Questions | 489632 |
| Running_threads | 57 |
| Slow_queries | 619 |
| Uptime | 19296 |
+--------------------------+-----------+

And variables are:
+----------------------------+---------------------------+
| Variable_name | Value |
+----------------------------+---------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | / |
| datadir | /var/lib/mysql/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 0 |
| key_buffer | 8388600 |
| language | /usr/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 150 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_write_lock_count | 4294967295 |
| net_buffer_length | 16384 |
| pid_file | /var/lib/mysql/forums.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| skip_locking | ON |
| skip_networking | OFF |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.22.32 |
| wait_timeout | 28800 |
+----------------------------+---------------------------+


and top:

9:08pm up 70 days, 6:02, 1 user, load average: 15.92, 12.30, 12.79
230 processes: 225 sleeping, 4 running, 1 zombie, 0 stopped
CPU states: 77.6% user, 22.3% system, 0.0% nice, 0.0% idle
Mem: 517368K av, 396344K used, 121024K free, 451300K shrd, 18448K buff
Swap: 131532K av, 61560K used, 69972K free 91336K cached

[eva2000]

1. take a read of http://i4net.tv/marticle/get.php?action=getarticle&articleid=3
2. then upgrade mysql from 3.22.32 to 3.23.38
3. depending on your server specs and traffic on your vb you might wanna set your my.cnf file to something like

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=250
set-variable = key_buffer=16M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=6M
set-variable = table_cache=800
set-variable = myisam_sort_buffer_size=32M
set-variable = thread_cache_size=4


and restart mysql :)

your problem seems to be the table_cache=64 is inadequate ;)