Hello.
In my opinion, your status variables are not so bad-looking. About a half of your queries are taken from the cache and the Key_reads/Key_read_requests ratio is less than 1%. Check that the system doesn't start swapping with high value of Query Cache size. If you run lots of queries with 'group by' clause, increasing the tmp_table_size variable could help. The high load of processor is suspicious for me. What hardware do you have? Tell the OS and MySQL version as well. "Dto. Sistemas de Unitel" <[EMAIL PROTECTED]> wrote: > Hi, > I=92m the administrador of a MySQL Server, it have lot=92s of queries, = > and now > it=92s 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=92s reseting all = > the > time=A0and 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=3D/var/lib/mysql > socket=3D/var/lib/mysql/mysql.sock > old_passwords=3D1 > max_connections =3D 400 > key_buffer =3D 64M > myisam_sort_buffer_size =3D 32M > join_buffer_size =3D 1M > read_buffer_size =3D 4M > sort_buffer_size =3D 4M > table_cache =3D 1024 > thread_cache_size =3D 128 > wait_timeout =3D 1800=20 > connect_timeout =3D 10 > max_allowed_packet =3D 16M > max_connect_errors =3D 10 > query_cache_limit =3D 1M > query_cache_size =3D 128M > query_cache_type =3D 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 > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
