MySQL Forums
Forum List  »  Performance

MySQL 5.5.31 cripplingly slow
Posted by: Mohammad Chang
Date: January 15, 2014 07:16AM

I'd like to thank everyone in advance for helping me out and being understanding. This is my first post in the forum so please correct me if I do something wrong or don't give enough information.

A little while ago I upgraded MySQL on my server to version 5.5.31 and since then MySQL has been running so slow that's it's become unusable. A script which used to take around 3 minutes on average, now takes almost an hour.

I run a very basic VPS with 256MB of RAM. Everything was working fine until the upgrade, so I'm guessing that's where the problem is--but it could be something else as other software was upgraded at the same time. The server runs Debian 7 (Wheezy) and I've been using PHP 5.4.22 with PDO to access MyISAM tables, though like I said the same exact code started running *much* slower after the upgrade.

Here's some other info that may help. Let me know if there's absolutely anything else I can do to help you help me.


mysql> SHOW VARIABLES LIKE 'sync%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| sync_binlog | 0 |
| sync_frm | ON |
| sync_master_info | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+---------------------+-------+
5 rows in set (0.00 sec)


mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 8388608 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+------------------------------+---------+
14 rows in set (0.00 sec)


mysql> SHOW VARIABLES LIKE '%log%';
+-----------------------------------------+------------------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/craigwatch.log |
| innodb_flush_log_at_trx_commit | 2 |
| 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_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | /var/lib/mysql/craigwatch.com.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/craigwatch-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+------------------------------------+
41 rows in set (0.01 sec)


mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)


mysql> SHOW GLOBAL STATUS LIKE 'Qc%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.08 sec)


Once again, I really appreciate your patience and assistance!

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL 5.5.31 cripplingly slow
2674
January 15, 2014 07:16AM
1123
January 15, 2014 11:44AM
1184
January 15, 2014 02:12PM
3438
January 17, 2014 05:52AM
1450
January 17, 2014 07:56AM
1214
January 17, 2014 11:23AM
1246
January 17, 2014 11:29AM
1315
January 17, 2014 02:03PM
1092
January 17, 2014 02:43PM
1241
January 17, 2014 05:13PM
1484
January 18, 2014 09:53AM
1163
January 18, 2014 04:00PM
1141
January 18, 2014 08:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.