MySQL Forums :: Performance :: MySQL 5.5.31 cripplingly slow


Advanced Search

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 1620 Mohammad Chang 01/15/2014 07:16AM
Re: MySQL 5.5.31 cripplingly slow 737 Peter Brawley 01/15/2014 11:44AM
Re: MySQL 5.5.31 cripplingly slow 722 Rick James 01/15/2014 02:12PM
Re: MySQL 5.5.31 cripplingly slow 2492 Mohammad Chang 01/17/2014 05:52AM
Re: MySQL 5.5.31 cripplingly slow 1007 Mohammad Chang 01/17/2014 07:56AM
Re: MySQL 5.5.31 cripplingly slow 826 Peter Brawley 01/17/2014 11:23AM
Re: MySQL 5.5.31 cripplingly slow 820 Mohammad Chang 01/17/2014 11:29AM
Re: MySQL 5.5.31 cripplingly slow 841 Rick James 01/17/2014 02:03PM
Re: MySQL 5.5.31 cripplingly slow 698 Mohammad Chang 01/17/2014 02:43PM
Re: MySQL 5.5.31 cripplingly slow 819 Rick James 01/17/2014 05:13PM
Re: MySQL 5.5.31 cripplingly slow 995 Mohammad Chang 01/18/2014 09:53AM
Re: MySQL 5.5.31 cripplingly slow 705 Rick James 01/18/2014 04:00PM
Re: MySQL 5.5.31 cripplingly slow 729 Mohammad Chang 01/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.