Hi, I’m the administrador of a MySQL Server, it have lot’s of queries, and now it’s getting high load of processor, I try to increase the size of MySQL Query Cache, but if I put more than 128 Mb the cache it’s reseting all the time and the performance is worst. The server have 1,2 Mb of RAM and I can assign 256 or more MB to Query Cache for reduce processor load. My actual my.cnf config is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 max_connections = 400 key_buffer = 64M myisam_sort_buffer_size = 32M join_buffer_size = 1M read_buffer_size = 4M sort_buffer_size = 4M table_cache = 1024 thread_cache_size = 128 wait_timeout = 1800 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1
And my show status info: mysql> show status; +--------------------------+------------+ | Variable_name | Value | +--------------------------+------------+ | Aborted_clients | 8378 | | Aborted_connects | 307 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 114459945 | | Bytes_sent | 1355402553 | | Com_admin_commands | 49554 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 178087 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 49499 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 49288 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 3 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 96 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 61263 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 250306 | | Com_set_option | 2560 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 0 | | Com_show_column_types | 0 | | Com_show_create_db | 102 | | Com_show_create_table | 2550 | | Com_show_databases | 10 | | Com_show_errors | 0 | | Com_show_fields | 3006 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 10 | | Com_show_storage_engines | 0 | | Com_show_tables | 2664 | | Com_show_variables | 0 | | Com_show_warnings | 0 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 102 | | Com_update | 71170 | | Com_update_multi | 0 | | Connections | 480457 | | Created_tmp_disk_tables | 49082 | | Created_tmp_files | 24 | | Created_tmp_tables | 51080 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Flush_commands | 3 | | Handler_commit | 90 | | Handler_delete | 8153 | | Handler_discover | 0 | | Handler_read_first | 25054 | | Handler_read_key | 86229424 | | Handler_read_next | 263941374 | | Handler_read_prev | 0 | | Handler_read_rnd | 32185584 | | Handler_read_rnd_next | 2097383672 | | Handler_rollback | 36 | | Handler_update | 1903485 | | Handler_write | 36862596 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 46113 | | Key_blocks_used | 14220 | | Key_read_requests | 313698833 | | Key_reads | 659980 | | Key_write_requests | 37597479 | | Key_writes | 85543 | | Max_used_connections | 71 | | Not_flushed_delayed_rows | 0 | | Open_files | 899 | | Open_streams | 0 | | Open_tables | 478 | | Opened_tables | 478 | | Qcache_free_blocks | 1372 | | Qcache_free_memory | 125980376 | | Qcache_hits | 273207 | | Qcache_inserts | 205788 | | Qcache_lowmem_prunes | 58583 | | Qcache_not_cached | 44244 | | Qcache_queries_in_cache | 2616 | | Qcache_total_blocks | 6841 | | Questions | 3840664 | | Rpl_status | NULL | | Select_full_join | 30314 | | Select_full_range_join | 0 | | Select_range | 30959 | | Select_range_check | 0 | | Select_scan | 89145 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 181 | | Sort_merge_passes | 0 | | Sort_range | 7829 | | Sort_rows | 32670418 | | Sort_scan | 58967 | | Table_locks_immediate | 567213 | | Table_locks_waited | 1773 | | Threads_cached | 32 | | Threads_connected | 39 | | Threads_created | 147 | | Threads_running | 1 | | Uptime | 333444 | +--------------------------+------------+ 156 rows in set (0.00 sec) In the show status I attach have just reset the Query Cache, can any body help me to configure my my.cnf for get lower CPU usage increasing the RAM memory usage (the server actually have 1.2 Gb of RAM but I can add another module of 512 mb for get better performance). Thanks for all and best regards, Roberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
