I have the next table:
CREATE TABLE `sessions` (
`session_id` char(32) COLLATE utf8_unicode_ci NOT NULL,
`session_expires` int(11) NOT NULL DEFAULT '0',
`session_data` varchar(10000) COLLATE utf8_unicode_ci NOT NULL,
`session_current_application` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'okn',
PRIMARY KEY (`session_id`),
KEY `INDEX_SessionExpires` (`session_expires`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
This table used to be MEMORY and recently I changed it because I had a lot of Table locks. Every time a user start session, I write lots of data into session_data field.
Right now is working well and I do not have any lock but sometimes, when I had more traffic (not much, 20 user or so) then the queries start to running very very slow this is an example:
| 337877 | db_user| localhost| main_db | Query | 2 | update| INSERT INTO sessions (session_id, session_current_application) VALUES ('e708694ba826b1063290c16e44ff |
| 337906 | db_user| localhost| main_db | Query | 15 | query end | UPDATE sessions SET session_id = '5f38668cd5de8be84ba530f53611f37f', session_current_application = ' |
| 337907 | db_user| localhost| main_db | Query | 14 | Updating | UPDATE sessions SET session_expires = '1400242016', session_data = 'screen_resolution|s:8:\"1280x800 |
| 337937 | db_user| localhost| main_db | Query | 53 | query end | UPDATE sessions SET session_expires = '1400241977', session_data = 'screen_resolution|s:8:\"1024x768 |
| 337940 | db_user| localhost| main_db | Query | 3 | update| INSERT INTO sessions (session_id, session_current_application) VALUES ('3afa3b2e3e5f9a65830c12945dbe |
| 337941 | db_user| localhost| main_db | Query | 0 | query end | UPDATE users SET user_unreads = '22' WHERE user_id = '981' |
| 337953 | db_user| localhost| main_db | Query | 2 | query end | UPDATE users SET user_unreads = '15' WHERE user_id = '1098' |
| 337962 | db_user| localhost| main_db | Query | 1 | query end | UPDATE sessions SET session_id = '7c0ea9ecdbd926cfdc79154b298dac2f', session_current_application = ' |
| 337964 | db_user| localhost| main_db | Query | 40 | Updating | UPDATE sessions SET session_expires = '1400241990', session_data = 'screen_resolution|s:8:\"1024x768 |
ie.:
UPDATE sessions
SET session_expires = '1400241990',
session_data = 'screen_resolution|s:8:\"1024x768` ... (long string) ...'
WHERE session_id = ? ;
where ? is the session_id
These queries normally are very fast (less than one second), but in this case it was running during 40 seconds...
I read a lot of about innoDB, Memory and MyISAM and slow queries but nothing seems to fit to my current problem.
Can somebody guide me in the right way?
Innodb values:
mysql> show variables like '%innodb%'; +---------------------------------+------------------------+ | Variable_name | Value | +---------------------------------+------------------------+ | have_innodb | YES | | ignore_builtin_innodb | OFF | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 2097152 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 1073741824 | | innodb_change_buffering | all | | 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_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 | | 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 | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.8 | | innodb_write_io_threads | 4 | +---------------------------------+------------------------+
Query_cache values:
mysql> show variables like '%Query_cache%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | have_query_cache | YES | | query_cache_limit | 67108864 | | query_cache_min_res_unit | 4096 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+
Qcache status:
mysql> show global status like 'Qc%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 28485 | | Qcache_free_memory | 328808320 | | Qcache_hits | 12447954 | | Qcache_inserts | 1777879 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1712890 | | Qcache_queries_in_cache | 115237 | | Qcache_total_blocks | 259609 | +-------------------------+-----------+
The queries that are slow:
UPDATE sessions SET session_id = '01f89492cef14aefbff5d35dd0e71585', session_current_application = 'custom' WHERE session_id = '01f89492cef14aefbff5d35dd0e71585'
UPDATE sessions SET session_expires = '1400491985', session_data = 'LOTS_OF_DATA' WHERE session_id = '1ac445f29164be475921a260528eeba4'
EDIT:
After lot of research still not knows what happens, i tried to repeat the behaviour on a test server but no luck, it seems is only in the production server. The test server is very little compared to the production server (8GB vs 32 GB) and i use the my.cnf from production for the test server.
Anybody has another idea?? i'm totally lost :(
