#1
  1. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123

    Increasing MySQL Performance


    I have come a surprisingly long way in web development without knowing much about how MySQL works.

    I've built a site that is now getting a lot of traffic and amd looking at ways of speeding it up.

    I've heard about the query cache and had a look at my cache variables

    Code:
    mysql> SHOW VARIABLES LIKE '%cache%';
    +------------------------------+------------+
    | Variable_name                | Value      |
    +------------------------------+------------+
    | bdb_cache_size               | 8388600    |
    | binlog_cache_size            | 32768      |
    | have_query_cache             | YES        |
    | key_cache_age_threshold      | 300        |
    | key_cache_block_size         | 1024       |
    | key_cache_division_limit     | 100        |
    | max_binlog_cache_size        | 4294967295 |
    | query_cache_limit            | 1048576    |
    | query_cache_min_res_unit     | 4096       |
    | query_cache_size             | 8388608    |
    | query_cache_type             | ON         |
    | query_cache_wlock_invalidate | OFF        |
    | table_cache                  | 64         |
    | thread_cache_size            | 0          |
    +------------------------------+------------+
    14 rows in set (0.00 sec)
    Does it look like the query cache is in use?
    Can I increase the size of the cache ? (would this work?)
    How long are queries cached for?

    I'm only at the start of the optimising process, and am also looking for PHP bottlenecks and profiling
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.
    Last edited by sr; February 1st, 2011 at 02:30 AM.
    /Stefan
  4. #3
  5. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Cool, thanks for that.

    I've made those conf changes and will let it run for a day, i think, (the site is getting a lot of hits so a day should give some reasonable stats).

    This may or may not be of any relevance, but in MySQL workbench this is what I see on server status

    Load: 0.69 (was 12.3 yesterday before I restarted MySQL)
    Mem:9%
    Query cache hitrate: 12%
    Key Efficiency: 98%
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.
    /Stefan
  8. #5
  9. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Code:
    mysql> SHOW GLOBAL VARIABLES;
    +---------------------------------+---------------------------------------------------------+
    | Variable_name                   | Value                                                   |
    +---------------------------------+---------------------------------------------------------+
    | auto_increment_increment        | 1                                                       |
    | auto_increment_offset           | 1                                                       |
    | automatic_sp_privileges         | ON                                                      |
    | back_log                        | 50                                                      |
    | basedir                         | /usr/                                                   |
    | bdb_cache_size                  | 8388600                                                 |
    | bdb_home                        |                                                         |
    | bdb_log_buffer_size             | 0                                                       |
    | bdb_logdir                      |                                                         |
    | bdb_max_lock                    | 10000                                                   |
    | bdb_shared_data                 | OFF                                                     |
    | bdb_tmpdir                      |                                                         |
    | binlog_cache_size               | 32768                                                   |
    | bulk_insert_buffer_size         | 8388608                                                 |
    | character_set_client            | latin1                                                  |
    | character_set_connection        | latin1                                                  |
    | character_set_database          | latin1                                                  |
    | character_set_filesystem        | binary                                                  |
    | character_set_results           | latin1                                                  |
    | character_set_server            | latin1                                                  |
    | character_set_system            | utf8                                                    |
    | character_sets_dir              | /usr/share/mysql/charsets/                              |
    | collation_connection            | latin1_swedish_ci                                       |
    | collation_database              | latin1_swedish_ci                                       |
    | collation_server                | latin1_swedish_ci                                       |
    | completion_type                 | 0                                                       |
    | concurrent_insert               | 1                                                       |
    | connect_timeout                 | 5                                                       |
    | datadir                         | /var/lib/mysql/                                         |
    | date_format                     | %Y-%m-%d                                                |
    | datetime_format                 | %Y-%m-%d %H:%i:%s                                       |
    | default_week_format             | 0                                                       |
    | delay_key_write                 | ON                                                      |
    | delayed_insert_limit            | 100                                                     |
    | delayed_insert_timeout          | 300                                                     |
    | delayed_queue_size              | 1000                                                    |
    | div_precision_increment         | 4                                                       |
    | engine_condition_pushdown       | OFF                                                     |
    | expire_logs_days                | 0                                                       |
    | flush                           | OFF                                                     |
    | flush_time                      | 0                                                       |
    | ft_boolean_syntax               | + -><()~*:""&|                                          |
    | ft_max_word_len                 | 84                                                      |
    | ft_min_word_len                 | 4                                                       |
    | ft_query_expansion_limit        | 20                                                      |
    | ft_stopword_file                | (built-in)                                              |
    | group_concat_max_len            | 1024                                                    |
    | have_archive                    | NO                                                      |
    | have_bdb                        | DISABLED                                                |
    | have_blackhole_engine           | NO                                                      |
    | have_compress                   | YES                                                     |
    | have_crypt                      | YES                                                     |
    | have_csv                        | NO                                                      |
    | have_dynamic_loading            | YES                                                     |
    | have_example_engine             | NO                                                      |
    | have_federated_engine           | NO                                                      |
    | have_geometry                   | YES                                                     |
    | have_innodb                     | YES                                                     |
    | have_isam                       | NO                                                      |
    | have_merge_engine               | YES                                                     |
    | have_ndbcluster                 | NO                                                      |
    | have_openssl                    | DISABLED                                                |
    | have_ssl                        | DISABLED                                                |
    | have_query_cache                | YES                                                     |
    | have_raid                       | NO                                                      |
    | have_rtree_keys                 | YES                                                     |
    | have_symlink                    | YES                                                     |
    | hostname                        | xeneco.co.uk                                            |
    | init_connect                    |                                                         |
    | init_file                       |                                                         |
    | init_slave                      |                                                         |
    | innodb_additional_mem_pool_size | 524288                                                  |
    | innodb_autoextend_increment     | 8                                                       |
    | innodb_buffer_pool_awe_mem_mb   | 0                                                       |
    | innodb_buffer_pool_size         | 2097152                                                 |
    | innodb_checksums                | ON                                                      |
    | innodb_commit_concurrency       | 0                                                       |
    | innodb_concurrency_tickets      | 500                                                     |
    | innodb_data_file_path           | ibdata1:10M:autoextend                                  |
    | innodb_data_home_dir            |                                                         |
    | innodb_doublewrite              | ON                                                      |
    | innodb_fast_shutdown            | 1                                                       |
    | innodb_file_io_threads          | 4                                                       |
    | innodb_file_per_table           | OFF                                                     |
    | innodb_flush_log_at_trx_commit  | 1                                                       |
    | innodb_flush_method             |                                                         |
    | innodb_force_recovery           | 0                                                       |
    | innodb_lock_wait_timeout        | 50                                                      |
    | innodb_locks_unsafe_for_binlog  | OFF                                                     |
    | innodb_log_arch_dir             |                                                         |
    | innodb_log_archive              | OFF                                                     |
    | innodb_log_buffer_size          | 512000                                                  |
    | innodb_log_file_size            | 5242880                                                 |
    | innodb_log_files_in_group       | 2                                                       |
    | innodb_log_group_home_dir       | ./                                                      |
    | innodb_max_dirty_pages_pct      | 90                                                      |
    | innodb_max_purge_lag            | 0                                                       |
    | innodb_mirrored_log_groups      | 1                                                       |
    | innodb_open_files               | 300                                                     |
    | innodb_rollback_on_timeout      | OFF                                                     |
    | innodb_support_xa               | ON                                                      |
    | innodb_sync_spin_loops          | 20                                                      |
    | innodb_table_locks              | ON                                                      |
    | innodb_thread_concurrency       | 2                                                       |
    | innodb_thread_sleep_delay       | 10000                                                   |
    | interactive_timeout             | 28800                                                   |
    | join_buffer_size                | 131072                                                  |
    | key_buffer_size                 | 8388600                                                 |
    | key_cache_age_threshold         | 300                                                     |
    | key_cache_block_size            | 1024                                                    |
    | key_cache_division_limit        | 100                                                     |
    | language                        | /usr/share/mysql/english/                               |
    | large_files_support             | ON                                                      |
    | large_page_size                 | 0                                                       |
    | large_pages                     | OFF                                                     |
    | lc_time_names                   | en_US                                                   |
    | license                         | GPL                                                     |
    | local_infile                    | OFF                                                     |
    | locked_in_memory                | OFF                                                     |
    | log                             | OFF                                                     |
    | log_bin                         | OFF                                                     |
    | log_bin_trust_function_creators | OFF                                                     |
    | log_error                       |                                                         |
    | log_queries_not_using_indexes   | ON                                                      |
    | log_slave_updates               | OFF                                                     |
    | log_slow_queries                | ON                                                      |
    | log_warnings                    | 1                                                       |
    | long_query_time                 | 1                                                       |
    | low_priority_updates            | OFF                                                     |
    | lower_case_file_system          | OFF                                                     |
    | lower_case_table_names          | 0                                                       |
    | max_allowed_packet              | 1048576                                                 |
    | max_binlog_cache_size           | 4294967295                                              |
    | max_binlog_size                 | 1073741824                                              |
    | max_connect_errors              | 10                                                      |
    | max_connections                 | 100                                                     |
    | max_delayed_threads             | 20                                                      |
    | max_error_count                 | 64                                                      |
    | max_heap_table_size             | 16777216                                                |
    | max_insert_delayed_threads      | 20                                                      |
    | max_join_size                   | 4294967295                                              |
    | max_length_for_sort_data        | 1024                                                    |
    | max_prepared_stmt_count         | 16382                                                   |
    | max_relay_log_size              | 0                                                       |
    | max_seeks_for_key               | 4294967295                                              |
    | max_sort_length                 | 1024                                                    |
    | max_sp_recursion_depth          | 0                                                       |
    | max_tmp_tables                  | 32                                                      |
    | max_user_connections            | 0                                                       |
    | max_write_lock_count            | 4294967295                                              |
    | multi_range_count               | 256                                                     |
    | myisam_data_pointer_size        | 6                                                       |
    | myisam_max_sort_file_size       | 2147483647                                              |
    | myisam_recover_options          | OFF                                                     |
    | myisam_repair_threads           | 1                                                       |
    | myisam_sort_buffer_size         | 8388608                                                 |
    | myisam_stats_method             | nulls_unequal                                           |
    | net_buffer_length               | 16384                                                   |
    | net_read_timeout                | 30                                                      |
    | net_retry_count                 | 10                                                      |
    | net_write_timeout               | 60                                                      |
    | new                             | OFF                                                     |
    | old_passwords                   | ON                                                      |
    | open_files_limit                | 1024                                                    |
    | optimizer_prune_level           | 1                                                       |
    | optimizer_search_depth          | 62                                                      |
    | pid_file                        | /var/run/mysqld/mysqld.pid                              |
    | port                            | 3306                                                    |
    | preload_buffer_size             | 32768                                                   |
    | profiling                       | OFF                                                     |
    | profiling_history_size          | 15                                                      |
    | protocol_version                | 10                                                      |
    | query_alloc_block_size          | 8192                                                    |
    | query_cache_limit               | 1048576                                                 |
    | query_cache_min_res_unit        | 4096                                                    |
    | query_cache_size                | 16777216                                                |
    | query_cache_type                | ON                                                      |
    | query_cache_wlock_invalidate    | OFF                                                     |
    | query_prealloc_size             | 8192                                                    |
    | range_alloc_block_size          | 2048                                                    |
    | read_buffer_size                | 131072                                                  |
    | read_only                       | OFF                                                     |
    | read_rnd_buffer_size            | 262144                                                  |
    | relay_log_purge                 | ON                                                      |
    | relay_log_space_limit           | 0                                                       |
    | rpl_recovery_rank               | 0                                                       |
    | secure_auth                     | OFF                                                     |
    | secure_file_priv                |                                                         |
    | server_id                       | 0                                                       |
    | skip_external_locking           | ON                                                      |
    | skip_networking                 | OFF                                                     |
    | skip_show_database              | OFF                                                     |
    | slave_compressed_protocol       | OFF                                                     |
    | slave_load_tmpdir               | /tmp/                                                   |
    | slave_net_timeout               | 3600                                                    |
    | slave_skip_errors               | OFF                                                     |
    | slave_transaction_retries       | 10                                                      |
    | slow_launch_time                | 2                                                       |
    | socket                          | /var/lib/mysql/mysql.sock                               |
    | sort_buffer_size                | 2097144                                                 |
    | sql_big_selects                 | ON                                                      |
    | sql_mode                        |                                                         |
    | sql_notes                       | ON                                                      |
    | sql_warnings                    | OFF                                                     |
    | ssl_ca                          |                                                         |
    | ssl_capath                      |                                                         |
    | ssl_cert                        |                                                         |
    | ssl_cipher                      |                                                         |
    | ssl_key                         |                                                         |
    | storage_engine                  | MyISAM                                                  |
    | sync_binlog                     | 0                                                       |
    | sync_frm                        | ON                                                      |
    | system_time_zone                | GMT                                                     |
    | table_cache                     | 64                                                      |
    | table_lock_wait_timeout         | 50                                                      |
    | table_type                      | MyISAM                                                  |
    | thread_cache_size               | 0                                                       |
    | thread_stack                    | 196608                                                  |
    | time_format                     | %H:%i:%s                                                |
    | time_zone                       | SYSTEM                                                  |
    | timed_mutexes                   | OFF                                                     |
    | tmp_table_size                  | 33554432                                                |
    | tmpdir                          | /tmp/                                                   |
    | transaction_alloc_block_size    | 8192                                                    |
    | transaction_prealloc_size       | 4096                                                    |
    | tx_isolation                    | REPEATABLE-READ                                         |
    | updatable_views_with_limit      | YES                                                     |
    | version                         | 5.0.45-log                                              |
    | version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (July  4, 2007) |
    | version_comment                 | Source distribution                                     |
    | version_compile_machine         | i686                                                    |
    | version_compile_os              | redhat-linux-gnu                                        |
    | wait_timeout                    | 28800                                                   |
    +---------------------------------+---------------------------------------------------------+
    233 rows in set (0.00 sec)
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  10. #6
  11. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Code:
    mysql> SHOW GLOBAL STATUS;
    +-----------------------------------+------------+
    | Variable_name                     | Value      |
    +-----------------------------------+------------+
    | Aborted_clients                   | 4          |
    | Aborted_connects                  | 7          |
    | Binlog_cache_disk_use             | 0          |
    | Binlog_cache_use                  | 0          |
    | Bytes_received                    | 42111496   |
    | Bytes_sent                        | 124546253  |
    | Com_admin_commands                | 38         |
    | Com_alter_db                      | 0          |
    | Com_alter_table                   | 0          |
    | Com_analyze                       | 0          |
    | Com_backup_table                  | 0          |
    | Com_begin                         | 144189     |
    | Com_call_procedure                | 0          |
    | Com_change_db                     | 269        |
    | Com_change_master                 | 0          |
    | Com_check                         | 0          |
    | Com_checksum                      | 0          |
    | Com_commit                        | 144189     |
    | Com_create_db                     | 0          |
    | Com_create_function               | 0          |
    | Com_create_index                  | 0          |
    | Com_create_table                  | 0          |
    | Com_create_user                   | 0          |
    | Com_dealloc_sql                   | 0          |
    | Com_delete                        | 1282       |
    | 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                        | 6793       |
    | Com_insert_select                 | 0          |
    | Com_kill                          | 0          |
    | Com_load                          | 0          |
    | Com_load_master_data              | 0          |
    | Com_load_master_table             | 0          |
    | Com_lock_tables                   | 0          |
    | 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                       | 26792      |
    | 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                        | 99540      |
    | Com_set_option                    | 982        |
    | 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                | 0          |
    | Com_show_create_table             | 0          |
    | Com_show_databases                | 1          |
    | Com_show_errors                   | 0          |
    | Com_show_fields                   | 0          |
    | Com_show_grants                   | 0          |
    | Com_show_innodb_status            | 0          |
    | Com_show_keys                     | 0          |
    | Com_show_logs                     | 0          |
    | Com_show_master_status            | 0          |
    | Com_show_ndb_status               | 0          |
    | Com_show_new_master               | 0          |
    | Com_show_open_tables              | 0          |
    | Com_show_privileges               | 0          |
    | Com_show_processlist              | 10         |
    | Com_show_slave_hosts              | 0          |
    | Com_show_slave_status             | 0          |
    | Com_show_status                   | 1282       |
    | Com_show_storage_engines          | 0          |
    | Com_show_tables                   | 0          |
    | Com_show_triggers                 | 0          |
    | Com_show_variables                | 351        |
    | Com_show_warnings                 | 0          |
    | Com_slave_start                   | 0          |
    | Com_slave_stop                    | 0          |
    | Com_stmt_close                    | 0          |
    | Com_stmt_execute                  | 0          |
    | Com_stmt_fetch                    | 0          |
    | Com_stmt_prepare                  | 0          |
    | Com_stmt_reset                    | 0          |
    | Com_stmt_send_long_data           | 0          |
    | Com_truncate                      | 0          |
    | Com_unlock_tables                 | 0          |
    | Com_update                        | 2069       |
    | Com_update_multi                  | 0          |
    | Com_xa_commit                     | 0          |
    | Com_xa_end                        | 0          |
    | Com_xa_prepare                    | 0          |
    | Com_xa_recover                    | 0          |
    | Com_xa_rollback                   | 0          |
    | Com_xa_start                      | 0          |
    | Compression                       | OFF        |
    | Connections                       | 38548      |
    | Created_tmp_disk_tables           | 169        |
    | Created_tmp_files                 | 5          |
    | Created_tmp_tables                | 60703      |
    | Delayed_errors                    | 0          |
    | Delayed_insert_threads            | 0          |
    | Delayed_writes                    | 0          |
    | Flush_commands                    | 2          |
    | Handler_commit                    | 4251       |
    | Handler_delete                    | 2016       |
    | Handler_discover                  | 0          |
    | Handler_prepare                   | 0          |
    | Handler_read_first                | 214        |
    | Handler_read_key                  | 1749388    |
    | Handler_read_next                 | 5615520    |
    | Handler_read_prev                 | 2062636    |
    | Handler_read_rnd                  | 416382     |
    | Handler_read_rnd_next             | 3643195643 |
    | Handler_rollback                  | 0          |
    | Handler_savepoint                 | 0          |
    | Handler_savepoint_rollback        | 0          |
    | Handler_update                    | 165509     |
    | Handler_write                     | 757364     |
    | Innodb_buffer_pool_pages_data     | 126        |
    | Innodb_buffer_pool_pages_dirty    | 0          |
    | Innodb_buffer_pool_pages_flushed  | 51         |
    | Innodb_buffer_pool_pages_free     | 0          |
    | Innodb_buffer_pool_pages_latched  | 0          |
    | Innodb_buffer_pool_pages_misc     | 2          |
    | Innodb_buffer_pool_pages_total    | 128        |
    | Innodb_buffer_pool_read_ahead_rnd | 0          |
    | Innodb_buffer_pool_read_ahead_seq | 3112       |
    | Innodb_buffer_pool_read_requests  | 124962     |
    | Innodb_buffer_pool_reads          | 2295       |
    | Innodb_buffer_pool_wait_free      | 0          |
    | Innodb_buffer_pool_write_requests | 104        |
    | Innodb_data_fsyncs                | 127        |
    | Innodb_data_pending_fsyncs        | 0          |
    | Innodb_data_pending_reads         | 0          |
    | Innodb_data_pending_writes        | 0          |
    | Innodb_data_read                  | 140414976  |
    | Innodb_data_reads                 | 4076       |
    | Innodb_data_writes                | 147        |
    | Innodb_data_written               | 1717248    |
    | Innodb_dblwr_pages_written        | 51         |
    | Innodb_dblwr_writes               | 30         |
    | Innodb_log_waits                  | 0          |
    | Innodb_log_write_requests         | 28         |
    | Innodb_log_writes                 | 37         |
    | Innodb_os_log_fsyncs              | 66         |
    | Innodb_os_log_pending_fsyncs      | 0          |
    | Innodb_os_log_pending_writes      | 0          |
    | Innodb_os_log_written             | 31232      |
    | Innodb_page_size                  | 16384      |
    | Innodb_pages_created              | 0          |
    | Innodb_pages_read                 | 8437       |
    | Innodb_pages_written              | 51         |
    | Innodb_row_lock_current_waits     | 0          |
    | Innodb_row_lock_time              | 0          |
    | Innodb_row_lock_time_avg          | 0          |
    | Innodb_row_lock_time_max          | 0          |
    | Innodb_row_lock_waits             | 0          |
    | Innodb_rows_deleted               | 6          |
    | Innodb_rows_inserted              | 5          |
    | Innodb_rows_read                  | 27578      |
    | Innodb_rows_updated               | 1          |
    | Key_blocks_not_flushed            | 0          |
    | Key_blocks_unused                 | 0          |
    | Key_blocks_used                   | 7248       |
    | Key_read_requests                 | 4347641    |
    | Key_reads                         | 37770      |
    | Key_write_requests                | 113789     |
    | Key_writes                        | 73204      |
    | Last_query_cost                   | 0.000000   |
    | Max_used_connections              | 22         |
    | Not_flushed_delayed_rows          | 0          |
    | Open_files                        | 89         |
    | Open_streams                      | 0          |
    | Open_tables                       | 64         |
    | Opened_tables                     | 348        |
    | Prepared_stmt_count               | 0          |
    | Qcache_free_blocks                | 485        |
    | Qcache_free_memory                | 13840448   |
    | Qcache_hits                       | 13117      |
    | Qcache_inserts                    | 40148      |
    | Qcache_lowmem_prunes              | 0          |
    | Qcache_not_cached                 | 61026      |
    | Qcache_queries_in_cache           | 1002       |
    | Qcache_total_blocks               | 2558       |
    | Questions                         | 479402     |
    | Rpl_status                        | NULL       |
    | Select_full_join                  | 11         |
    | Select_full_range_join            | 0          |
    | Select_range                      | 403        |
    | Select_range_check                | 0          |
    | Select_scan                       | 8395       |
    | Slave_open_temp_tables            | 0          |
    | Slave_retried_transactions        | 0          |
    | Slave_running                     | OFF        |
    | Slow_launch_threads               | 0          |
    | Slow_queries                      | 8033       |
    | Sort_merge_passes                 | 0          |
    | Sort_range                        | 559        |
    | Sort_rows                         | 418699     |
    | Sort_scan                         | 59140      |
    | Ssl_accept_renegotiates           | 0          |
    | Ssl_accepts                       | 0          |
    | Ssl_callback_cache_hits           | 0          |
    | Ssl_cipher                        |            |
    | Ssl_cipher_list                   |            |
    | Ssl_client_connects               | 0          |
    | Ssl_connect_renegotiates          | 0          |
    | Ssl_ctx_verify_depth              | 0          |
    | Ssl_ctx_verify_mode               | 0          |
    | Ssl_default_timeout               | 0          |
    | Ssl_finished_accepts              | 0          |
    | Ssl_finished_connects             | 0          |
    | Ssl_session_cache_hits            | 0          |
    | Ssl_session_cache_misses          | 0          |
    | Ssl_session_cache_mode            | NONE       |
    | Ssl_session_cache_overflows       | 0          |
    | Ssl_session_cache_size            | 0          |
    | Ssl_session_cache_timeouts        | 0          |
    | Ssl_sessions_reused               | 0          |
    | Ssl_used_session_cache_entries    | 0          |
    | Ssl_verify_depth                  | 0          |
    | Ssl_verify_mode                   | 0          |
    | Ssl_version                       |            |
    | Table_locks_immediate             | 208887     |
    | Table_locks_waited                | 142        |
    | Tc_log_max_pages_used             | 0          |
    | Tc_log_page_size                  | 0          |
    | Tc_log_page_waits                 | 0          |
    | Threads_cached                    | 0          |
    | Threads_connected                 | 3          |
    | Threads_created                   | 38547      |
    | Threads_running                   | 1          |
    | Uptime                            | 9091       |
    | Uptime_since_flush_status         | 9091       |
    +-----------------------------------+------------+
    249 rows in set (0.00 sec)
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  12. #7
  13. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    Server:

    Media Temple DV Extreme (VPS)

    60GB Hard Disk (spec unknown)
    Dedicated RAM 2GB

    This VPS is really only powering this one site

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

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  14. #8
  15. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    UPDATE

    No slow queries have been logged

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

    I've profiled my PHP scripts that are powering the site and they're all running pretty quickly.

    I think part of the cause is the number of queries made by each page, and the ajax-based rotating adverts (which are supposed to be targeted) that are upping the load on mysqld.

    Restarting mysql brings things back under control for about 12 hours, but the server load - from mysql - continues to build and eventually gets to a point where the service needs to be restarted.
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  16. #9
  17. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    A bit more information on what the website does and how I'm doing it.

    The site is thehorseexchange.com and apart from the blog (separate DB) and users, I have 3 main tables:

    Horses (37137 rows, 2400 not 'deleted')
    Stallions (800 rows, 794 not 'deleted')
    Products (9495 rows, 2478 not 'Deleted')

    And these queries running on the home page

    sql Code:
     
    SELECT
    	COUNT(id) AS qty,
    	Breed
    FROM
    	Horses
    WHERE
    	deleted = 0
    GROUP BY
    	Breed,
    ORDER BY
    	Breed ASC

    sql Code:
     
    SELECT
    	COUNT(id) AS qty,
    	Colour
    FROM
    	Horses
    WHERE
    	deleted = 0
    GROUP BY
    	Colour,
    ORDER BY
    	Colour ASC

    sql Code:
     
    SELECT
    	COUNT(id) AS qty,
    	Defined_Use
    FROM
    	Horses
    WHERE
    	deleted = 0
    GROUP BY
    	Defined_Use,
    ORDER BY
    	Defined_Use ASC

    sql Code:
     
    SELECT
    	COUNT(id) AS qty,
    	Breed
    FROM
    	Stallions
    WHERE
    	deleted = 0
    GROUP BY
    	Breed,
    ORDER BY
    	Breed ASC

    sql Code:
     
    SELECT
    	COUNT(id) AS qty,
    	Category
    FROM
    	Products
    WHERE
    	deleted = 0
    GROUP BY
    	Category,
    ORDER BY
    	Category ASC


    Each query is executed separately within PHP using PDO.

    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'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  18. #10
  19. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by Northie
    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.
    /Stefan
  20. #11
  21. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    All tables are InnoDB

    I'm not using transactions because there are very few relationships - just users and the adverts they put on the site and i donlt see that as a transactional statement

    I was expecting to find the log file in /var/lib/mysql/ (where all the data is stored for the databases) but it wasn't there

    Sever load:

    the top of top looks like this today

    Code:
    top - 08:43:46 up 1 day, 11:20,  1 user,  load average: 5.94, 6.29, 6.30
    Tasks:  48 total,   2 running,  46 sleeping,   0 stopped,   0 zombie
    Cpu(s): 90.5% us,  4.9% sy,  0.0% ni,  4.3% id,  0.3% wa,  0.0% hi,  0.0% si
    Mem:   3170584k total,   343356k used,  2827228k free,        0k buffers
    Those load average figures were getting to over 30 until I started restarting MySQL every day - I'm about to restart it now

    EDIT

    top of top 10 minutes after restarting MySQL


    Code:
    top - 08:55:36 up 1 day, 11:32,  1 user,  load average: 0.31, 1.38, 3.70
    Tasks:  40 total,   1 running,  39 sleeping,   0 stopped,   0 zombie
    Cpu(s):  0.3% us,  0.1% sy,  0.0% ni, 99.7% id,  0.0% wa,  0.0% hi,  0.0% si
    Mem:   3170584k total,   255736k used,  2914848k free,        0k buffers
    Swap:        0k total,        0k used,        0k free,        0k cached
    Last edited by Northie; February 3rd, 2011 at 02:56 AM.
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  22. #12
  23. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Hmm, yes that is not a rounding error.

    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.)

    Then you run these:
    Code:
    ALTER TABLE horses ADD INDEX horses_ix_deleted_breed_id (deleted, breed, id);
    ALTER TABLE horses ADD INDEX horses_ix_deleted_colour_id (deleted, colour, id);
    ALTER TABLE horses ADD INDEX horses_ix_deleted_defineduse_id (deleted, defined_use, id);
    ALTER TABLE stallions ADD INDEX stallions_ix_deleted_breed_id (deleted, breed, id);
    ALTER TABLE products ADD INDEX products_ix_deleted_category_id (deleted, category, id);
    Which will create these five indexes that will make those 5 queries to lightning fast.

    Then we have a bunch of other stuff we can do but this will probably go a long way.

    Comments on this post

    • Northie agrees : Thanks for all your help so far on this
    • elef agrees
    /Stefan
  24. #13
  25. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    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'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  26. #14
  27. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    memory increased to 1.25 G (62.5% of available RAM) and with a bit of proper indexing the site is lightening quick!!!!

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

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  28. #15
  29. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Nice to hear!

    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.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo