1

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 :(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.