Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a number of websites with session tables on a web server, these are used by Joomla CMS and Symfony framework systems. With slow_query_log=0.6, the update queries for the session tables come up regularly in the slow log.

The Joomla session table definition is

    CREATE TABLE IF NOT EXISTS `jos_session` (
      `username` varchar(150) DEFAULT '',
      `time` varchar(14) DEFAULT '',
      `session_id` varchar(200) NOT NULL DEFAULT '0',
      `guest` tinyint(4) DEFAULT '1',
      `userid` int(11) DEFAULT '0',
      `usertype` varchar(50) DEFAULT '',
      `gid` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `client_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
      `data` longtext,
      PRIMARY KEY (`session_id`(64)),
      KEY `whosonline` (`guest`,`usertype`),
      KEY `userid` (`userid`),
      KEY `time` (`time`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I have tried changing the storage engine to InnoDB with no effect, I've tried optimize table and also removed all index, but the primary one. There are records in this table with storage space

Data    12,352.0    KiB
Index   144.0   KiB
Total   12,496.0    KiB

My Server configuration

        auto_increment_increment                   1
    auto_increment_offset                      1
    autocommit                       ON         
    automatic_sp_privileges          ON         
    back_log                                  50
    basedir                          /usr       
    big_tables                       OFF        
    binlog_cache_size                      32768
    binlog_direct_non_transactional_uOFF        
    binlog_format                    STATEMENT  
    binlog_stmt_cache_size                 32768
    bulk_insert_buffer_size              8388608
    character_set_client             utf8       
    character_set_connection         utf8       
    character_set_database           latin1     
    character_set_filesystem         binary     
    character_set_results            utf8       
    character_set_server             latin1     
    character_set_system             utf8       
    character_sets_dir               /usr/share/
    collation_connection             utf8_genera
    collation_database               latin1_swed
    collation_server                 latin1_swed
    completion_type                  NO_CHAIN   
    concurrent_insert                AUTO       
    connect_timeout                           10
    datadir                          /var/lib/my
    date_format                      %Y-%m-%d   
    datetime_format                  %Y-%m-%d %H
    default_storage_engine           InnoDB     
    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        ON         
    error_count                                0
    event_scheduler                  OFF        
    expire_logs_days                          10
    external_user                    
    flush                            OFF        
    flush_time                                 0
    foreign_key_checks               ON         
    ft_boolean_syntax                + -><()~*:"
    ft_max_word_len                           84
    ft_min_word_len                            4
    ft_query_expansion_limit                  20
    ft_stopword_file                 (built-in) 
    general_log                      OFF        
    general_log_file                 /var/lib/my
    group_concat_max_len                    1024
    have_compress                    YES        
    have_crypt                       YES        
    have_csv                         YES        
    have_dynamic_loading             YES        
    have_geometry                    YES        
    have_innodb                      YES        
    have_ndbcluster                  NO         
    have_openssl                     DISABLED   
    have_partitioning                YES        
    have_profiling                   YES        
    have_query_cache                 YES        
    have_rtree_keys                  YES        
    have_ssl                         DISABLED   
    have_symlink                     YES        
    hostname                         nzs2.automa
    identity                                   0
    ignore_builtin_innodb            OFF        
    init_connect                     
    init_file                        
    init_slave                       
    innodb_adaptive_flushing         ON         
    innodb_adaptive_hash_index       ON         
    innodb_additional_mem_pool_size      8388608
    innodb_autoextend_increment                8
    innodb_autoinc_lock_mode                   1
    innodb_buffer_pool_instances               1
    innodb_buffer_pool_size            268435456
    innodb_change_buffering          all        
    innodb_checksums                 ON         
    innodb_commit_concurrency                  0
    innodb_concurrency_tickets               500
    innodb_data_file_path            ibdata1:10M
    innodb_data_home_dir             
    innodb_doublewrite               ON         
    innodb_fast_shutdown                       1
    innodb_file_format               Antelope   
    innodb_file_format_check         ON         
    innodb_file_format_max           Antelope   
    innodb_file_per_table            OFF        
    innodb_flush_log_at_trx_commit             1
    innodb_flush_method              
    innodb_force_load_corrupted      OFF        
    innodb_force_recovery                      0
    innodb_io_capacity                       200
    innodb_large_prefix              OFF        
    innodb_lock_wait_timeout                  50
    innodb_locks_unsafe_for_binlog   OFF        
    innodb_log_buffer_size               8388608
              Variable_name             Value   
    innodb_log_file_size                 5242880
    innodb_log_files_in_group                  2
    innodb_log_group_home_dir        ./         
    innodb_max_dirty_pages_pct                75
    innodb_max_purge_lag                       0
    innodb_mirrored_log_groups                 1
    innodb_old_blocks_pct                     37
    innodb_old_blocks_time                     0
    innodb_open_files                        300
    innodb_purge_batch_size                   20
    innodb_purge_threads                       0
    innodb_random_read_ahead         OFF        
    innodb_read_ahead_threshold               56
    innodb_read_io_threads                     4
    innodb_replication_delay                   0
    innodb_rollback_on_timeout       OFF        
    innodb_rollback_segments                 128
    innodb_spin_wait_delay                     6
    innodb_stats_method              nulls_equal
    innodb_stats_on_metadata         ON         
    innodb_stats_sample_pages                  8
    innodb_strict_mode               OFF        
    innodb_support_xa                ON         
    innodb_sync_spin_loops                    30
    innodb_table_locks               ON         
    innodb_thread_concurrency                  0
    innodb_thread_sleep_delay              10000
    innodb_use_native_aio            OFF        
    innodb_use_sys_malloc            ON         
    innodb_version                   1.1.8      
    innodb_write_io_threads                    4
    insert_id                                  0
    interactive_timeout                    28800
    join_buffer_size                      262144
    keep_files_on_create             OFF        
    key_buffer_size                    268435456
    key_cache_age_threshold                  300
    key_cache_block_size                    1024
    key_cache_division_limit                 100
    large_files_support              ON         
    large_page_size                            0
    large_pages                      OFF        
    last_insert_id                             0
    lc_messages                      en_US      
    lc_messages_dir                  /usr/share/
    lc_time_names                    en_US      
    license                          GPL        
    local_infile                     ON         
    lock_wait_timeout                   31536000
    locked_in_memory                 OFF        
    log                              OFF        
    log_bin                          OFF        
    log_bin_trust_function_creators  OFF        
    log_error                        
    log_output                       FILE       
    log_queries_not_using_indexes    OFF        
    log_slave_updates                OFF        
    log_slow_queries                 ON         
    log_warnings                               1
    long_query_time                          0.6
    low_priority_updates             OFF        
    lower_case_file_system           OFF        
    lower_case_table_names                     0
    max_allowed_packet                  16777216
    max_binlog_cache_size                    ###
    max_binlog_size                    104857600
    max_binlog_stmt_cache_size               ###
    max_connect_errors                        10
    max_connections                          100
    max_delayed_threads                       20
    max_error_count                           64
    max_heap_table_size                 25165824
    max_insert_delayed_threads                20
    max_join_size                            ###
    max_length_for_sort_data                1024
    max_long_data_size                  16777216
    max_prepared_stmt_count                16382
    max_relay_log_size                         0
    max_seeks_for_key                        ###
    max_sort_length                         1024
    max_sp_recursion_depth                     0
    max_tmp_tables                            32
    max_user_connections                       0
    max_write_lock_count                     ###
    metadata_locks_cache_size               1024
    min_examined_row_limit                     0
    multi_range_count                        256
    myisam_data_pointer_size                   6
    myisam_max_sort_file_size                ###
    myisam_mmap_size                         ###
    myisam_recover_options           BACKUP     
    myisam_repair_threads                      1
    myisam_sort_buffer_size              8388608
    myisam_stats_method              nulls_unequ
    myisam_use_mmap                  OFF        
    net_buffer_length                      16384
    net_read_timeout                          30
    net_retry_count                           10
    net_write_timeout                         60
    new                              OFF        
              Variable_name             Value   
    old                              OFF        
    old_alter_table                  OFF        
    old_passwords                    OFF        
    open_files_limit                        2158
    optimizer_prune_level                      1
    optimizer_search_depth                    62
    optimizer_switch                 index_merge
    performance_schema               OFF        
    performance_schema_events_waits_h      10000
    performance_schema_events_waits_h         10
    performance_schema_max_cond_class         80
    performance_schema_max_cond_insta       1000
    performance_schema_max_file_class         50
    performance_schema_max_file_handl      32768
    performance_schema_max_file_insta      10000
    performance_schema_max_mutex_clas        200
    performance_schema_max_mutex_inst    1000000
    performance_schema_max_rwlock_cla         30
    performance_schema_max_rwlock_ins    1000000
    performance_schema_max_table_hand     100000
    performance_schema_max_table_inst      50000
    performance_schema_max_thread_cla         50
    performance_schema_max_thread_ins       1000
    pid_file                         /var/run/my
    plugin_dir                       /usr/lib/my
    port                                    3306
    preload_buffer_size                    32768
    profiling                        OFF        
    profiling_history_size                    15
    protocol_version                          10
    proxy_user                       
    pseudo_thread_id                      401326
    query_alloc_block_size                  8192
    query_cache_limit                    1048576
    query_cache_min_res_unit                4096
    query_cache_size                    25165824
    query_cache_type                 ON         
    query_cache_wlock_invalidate     OFF        
    query_prealloc_size                     8192
    rand_seed1                                 0
    rand_seed2                                 0
    range_alloc_block_size                  4096
    read_buffer_size                      131072
    read_only                        OFF        
    read_rnd_buffer_size                  262144
    relay_log                        
    relay_log_index                  
    relay_log_info_file              relay-log.i
    relay_log_purge                  ON         
    relay_log_recovery               OFF        
    relay_log_space_limit                      0
    report_host                      
    report_password                  
    report_port                             3306
    report_user                      
    rpl_recovery_rank                          0
    secure_auth                      OFF        
    secure_file_priv                 
    server_id                                  0
    skip_external_locking            ON         
    skip_name_resolve                OFF        
    skip_networking                  OFF        
    skip_show_database               OFF        
    slave_compressed_protocol        OFF        
    slave_exec_mode                  STRICT     
    slave_load_tmpdir                /tmp       
    slave_max_allowed_packet          1073741824
    slave_net_timeout                       3600
    slave_skip_errors                OFF        
    slave_transaction_retries                 10
    slave_type_conversions           
    slow_launch_time                           2
    slow_query_log                   ON         
    slow_query_log_file              /var/log/my
    socket                           /var/run/my
    sort_buffer_size                     2097152
    sql_auto_is_null                 OFF        
    sql_big_selects                  ON         
    sql_big_tables                   OFF        
    sql_buffer_result                OFF        
    sql_log_bin                      ON         
    sql_log_off                      OFF        
    sql_low_priority_updates         OFF        
    sql_max_join_size                        ###
    sql_mode                         
    sql_notes                        ON         
    sql_quote_show_create            ON         
    sql_safe_updates                 OFF        
    sql_select_limit                         ###
    sql_slave_skip_counter                     0
    sql_warnings                     OFF        
    ssl_ca                           
    ssl_capath                       
    ssl_cert                         
    ssl_cipher                       
    ssl_key                          
    storage_engine                   InnoDB     
    stored_program_cache                     256
    sync_binlog                                0
    sync_frm                         ON         
              Variable_name             Value   
    sync_master_info                           0
    sync_relay_log                             0
    sync_relay_log_info                        0
    system_time_zone                 NZDT       
    table_definition_cache                  1024
    table_open_cache                        1024
    thread_cache_size                          8
    thread_concurrency                        10
    thread_handling                  one-thread-
    thread_stack                          196608
    time_format                      %H:%i:%s   
    time_zone                        SYSTEM     
    timed_mutexes                    OFF        
    timestamp                         1361481911
    tmp_table_size                      16777216
    tmpdir                           /tmp       
    transaction_alloc_block_size            8192
    transaction_prealloc_size               4096
    tx_isolation                     REPEATABLE-
    unique_checks                    ON         
    updatable_views_with_limit       YES        
    version                          5.5.29-0ubu
    version_comment                  (Ubuntu)   
    version_compile_machine          x86_64     
    version_compile_os               debian-linu
    wait_timeout                           28800
    warning_count                              0

How can I improve the speed of these queries?

A typical query is of the style

UPDATE `jos_session` 
SET `time`='1361469848',`userid`='0',`usertype`='',`username`='',
    `gid`='0',`guest`='1',`client_id`='0',
    `data`='__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1361469848;s:18:\"session.timer.last\";i:1361469848;s:17:\"session.timer.now\";i:1361469848;s:24:\"session.client.forwarded\";s:28:\"121.205.249.235, 23.15.14.22\";s:22:\"session.client.browser\";s:69:\"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.1.4322)\";s:8:\"registry\";O:9:\"JRegistry\":3:{s:17:\"_defaultNameSpace\";s:7:\"session\";s:9:\"_registry\";a:2:{s:7:\"session\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}s:11:\"application\";a:1:{s:4:\"data\";O:8:\"stdClass\":1:{s:4:\"lang\";s:5:\"en-GB\";}}}s:7:\"_errors\";a:0:{}}s:4:\"user\";O:5:\"JUser\":19:{s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:3:\"gid\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:3:\"aid\";i:0;s:5:\"guest\";i:1;s:7:\"_params\";O:10:\"JParameter\":7:{s:4:\"_raw\";s:0:\"\";s:4:\"_xml\";N;s:9:\"_elements\";a:0:{}s:12:\"_elementPath\";a:1:{i:0;s:69:\"/var/www/www.seleconlight.com/libraries/joomla/html/parameter/element\";}s:17:\"_defaultNameSpace\";s:8:\"_default\";s:9:\"_registry\";a:1:{s:8:\"_default\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:9:\"_errorMsg\";N;s:7:\"_errors\";a:0:{}}}country_cc|N;' 
WHERE session_id='k3fuukvh8lrfq244u6ic4it605';
share|improve this question
2  
How can I improve the speed of these queries? Can you also provide the queries which you want to improve? –  edze Feb 21 '13 at 22:27
    
varchar(200) utf8 as a primary key? Why are you using a 600-bytes wide primary key? –  ypercube Feb 21 '13 at 22:54
    
@edze have added query –  automatem Feb 22 '13 at 2:24
    
@ypercube - this is the Joomla 1.5 session table, not my idea. –  automatem Feb 22 '13 at 2:24
    
I bet an SELECT and UPDATE on jos_session is done on every page view. Followed by a DELETE all where time is lesser than NOW() minus some time. –  edze Feb 22 '13 at 9:42
show 1 more comment

1 Answer 1

up vote 3 down vote accepted

First things Set query_cache_type=0 and query_cache_size=0 in my.cnf For multi-core systems the cache will create contention issues.

Important Questions How much free RAM is showing on your server (use top command)? What is the particular statement that is showing up in the slow query log?

If you are going to keep the table database engine as InnoDB which I recommend Make innodb_log_file_size = innodb_buffer_pool_size/2 This will reduce IOs. Be sure to follow instructions found on here: http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html in the place starting with "If you want to change the ".

If you want to keep it as MyISAM This table will get locked for every update. Change the varchars and longtext to chars so it will be a fixed-length table (see http://dev.mysql.com/doc/refman/5.5/en/myisam-table-formats.html

Change the primary key to use a hash index:

alter table jos_session drop primary key;
alter table jos_session add primary key (`session_id`(64)) using hash;

On query cache Long story on query cache; if you have the same select query running over and over then yeah it can help, but there's lots of overhead to have it run. General rule, turn it off, and then turn it on if you need to. (see http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/)

Speeding up UPDATE * Thanks for the slow query log statement. The biggest thing that slows updates is updating the indexes. Since you've tried dropping the indexes the next step is to convert the table to InnoDB Engine. MyISAM is not very good with concurrent updates. If there still is no satisfaction, the next step to see if you cannot partition the table across different devices, but make sure the extra administrative burden is worth the benefit. Do you have any extra hard disk devices available to the server not being used?

Joomla session table Upon investigation, this table gets deletes which means it will have holes. If you still want to stick with MyISAM, try setting concurrent_insert=2. You can do this without requiring a maintenance window. (more info: http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html). INSERTS get higher priority over updates so speeding those up may speed up your UPDATE statement.

Memory and InnoDB If you can set the innodb_buffer_pool_size to the total size of the database + indexes of all of the InnoDB tables, then updates will happen in RAM first instead of disk. This is the ideal for perormance. Since I see you are using around 2.7GB on the server, there's probably other apps contending for disk--all the more to go to innodb with the innodb buffer settings.

share|improve this answer
    
Uhm ok thanks, I'm a bit confused about query_cache, which I need for my read queries, don't I? I also have lots of MyIsam tables. Top: 3068968k total, 2798312k used, 270656k free, 541936k buffers. Statement see above. For your final comment will have to find a maintenance window. I will also try the hash key. –  automatem Feb 22 '13 at 2:29
    
Hash key by itself doesn't seem to have any effect –  automatem Feb 22 '13 at 2:32
    
cache will create contention issues I think this depends on your workload and is not generally true. My server runs with a query_cache_size=2048M and 6 cores a lot faster than without caching. –  edze Feb 22 '13 at 10:03
    
@Adam C. Scott The table is InnoDB. I'll try turning the query cache off. Also will investigate other session storage options –  automatem Feb 23 '13 at 21:41
1  
@automatem Glad this worked for you. Came across this as well that might be worth looking at: stackoverflow.com/questions/2897765/… –  Adam C. Scott Feb 26 '13 at 16:32
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.