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
1671
January 15, 2014 07:16AM
752
January 15, 2014 11:44AM
740
January 15, 2014 02:12PM
2543
January 17, 2014 05:52AM
1032
January 17, 2014 07:56AM
851
January 17, 2014 11:23AM
845
January 17, 2014 11:29AM
864
January 17, 2014 02:03PM
718
January 17, 2014 02:43PM
842
January 17, 2014 05:13PM
1021
January 18, 2014 09:53AM
721
January 18, 2014 04:00PM
750
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.