We help IT Professionals succeed at work.

Issues with SQL Deadlocks and sluggish system

320 Views
Last Modified: 2016-03-02
We've been having a whale of a time determining why, all of a sudden, we're getting SQL Deadlocks and extreme slowness in our website. I upped the max_connections to alleviate some "Out of Connections" error we had as well.

I have the following in my.cnf
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 150

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Open in new window

We are using ExpressionEngine and Magento, and I know Magento is a database hog and is the ONLY source of the SQL Deadlocks as it is occuring in Magento's PDO.php file when a customer update is being performed.
#0 /store/lib/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /store/lib/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /store/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `magcust...', Array)
#3 /store/lib/Varien/Db/Adapter/Pdo/Mysql.php(333): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `magcust...', Array)
#4 /store/lib/Zend/Db/Adapter/Abstract.php(604): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `magcust...', Array)
#5 /store/app/code/core/Mage/Eav/Model/Entity/Abstract.php(1133): Zend_Db_Adapter_Abstract->update('magcustomer_ent...', Array, 'entity_id='9170...')
#6 /store/app in /srv/www/htdocs/store/lib/Zend/Db/Statement/Pdo.php on line 235

Open in new window

We authenticate our users through our ERP using a web service and have everyone's Magento account have the same password so that we can log them in automatically. We update their Magento record when we authenticate.

I added code to retry when a deadlock was found but that only caused the system to slow down even more. So, what do you think may be causing our issues?  

Here is also the uncommented variables in my PHP .ini file:
zend.ze1_compatibility_mode = Off
short_open_tag = On
asp_tags = Off
precision    =  14
y2k_compliance = On
output_buffering = 4096
zlib.output_compression = Off
implicit_flush = Off
unserialize_callback_func=
serialize_precision = 100
allow_call_time_pass_reference = Off
safe_mode = Off
safe_mode_gid = Off
safe_mode_include_dir =
safe_mode_exec_dir =
safe_mode_allowed_env_vars = PHP_
safe_mode_protected_env_vars = GCONV_PATH,GETCONF_DIR,HOSTALIASES,LD_AUDIT,LD_DEBUG,LD_DEBUG_OUTPUT,LD_DYNAMIC_WEAK,LD_LIBRARY_PATH,LD_ORIGIN_PATH,
         LD_PRELOAD,LD_PROFILE,LD_SHOW_AUXV,LD_USE_LOAD_BIAS,LOCALDOMAIN,LOCPATH,MALLOC_TRACE,NLSPATH,RESOLV_HOST_CONF,RES_OPTIONS,TMPDIR,TZDIR
disable_functions =
disable_classes =
expose_php = On

max_execution_time = 60     ; Maximum execution time of each script, in seconds
max_input_time = 60	        ; Maximum amount of time each script may spend parsing request data
memory_limit = 1024M        ; Maximum amount of memory a script may consume (128MB)
error_reporting  =  E_ALL

display_errors = Off
display_startup_errors = Off
log_errors = On
log_errors_max_len = 1024
ignore_repeated_errors = Off
ignore_repeated_source = Off
report_memleaks = On
track_errors = Off
html_errors = Off
error_log = /var/tmp/php_error.log
variables_order = "GPCS"
register_globals = Off
register_long_arrays = Off
register_argc_argv = Off
auto_globals_jit = On
post_max_size = 20M

magic_quotes_gpc = Off
magic_quotes_runtime = Off
magic_quotes_sybase = Off
auto_append_file =

default_mimetype = "text/html"
default_charset = "UTF-8"

include_path = ".:/usr/share/php5:/usr/share/php5/PEAR"
doc_root =
user_dir =
extension_dir = /usr/lib64/php5/extensions
enable_dl = Off

file_uploads = On
upload_tmp_dir = "/tmp"
upload_max_filesize = 20M

allow_url_fopen = On
allow_url_include = Off
default_socket_timeout = 60

define_syslog_variables  = Off

sql.safe_mode = Off

odbc.allow_persistent = Off
odbc.check_persistent = On
odbc.max_persistent = -1
odbc.max_links = -1
odbc.defaultlrl = 4096
odbc.defaultbinmode = 1

mysql.allow_persistent = Off
mysql.max_persistent = -1
mysql.max_links = -1
mysql.default_port =

mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =

mysql.connect_timeout = 60
mysql.trace_mode = Off

mysqli.max_links = -1
mysqli.default_port = 3306
mysqli.default_socket =

mysqli.default_host =
mysqli.default_user =
mysqli.default_pw =

mysqli.reconnect = Off

session.save_handler = files

session.save_path = "/var/lib/php5"
session.use_cookies = 1
session.use_only_cookies = 1
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.cookie_httponly = 1
session.serialize_handler = php
session.gc_probability = 1
session.gc_divisor     = 1000
session.gc_maxlifetime = 1440
session.bug_compat_42 = 0
session.bug_compat_warn = 1
session.referer_check =
session.entropy_length = 16
session.entropy_file = /dev/urandom
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 1
session.hash_bits_per_character = 5
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=fakeentry"


soap.wsdl_cache_enabled=1
soap.wsdl_cache_dir="/tmp"
soap.wsdl_cache_ttl=86400

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Eddie ShipmanAll-around developer
CERTIFIED EXPERT

Author

Commented:
That solution will not work for us. The deadlock is coming from a single point, the $customer->save()
in a function called mag_login_or_create() in our ExpressionEngine plugin. I have put in some logging and it seems that the call actually IS being called multiple times at once from the same "user". Not exactly sure why, though.
CERTIFIED EXPERT
Top Expert 2004

Commented:
Unfortunately, deadlocks are usually a design problem, not a configuration or implementation problem.  Resolving these deadlocks will almost certainly involve debugging and modifying the code - something that would best be done by the authors.  Have you tried contacting Magento for any assistance?

I'm not familiar enough with Magento to be of any real, specific help.  The most I could do is point you in the right direction to isolate the cause of the lock.  It sounds like you've already found it.

Perhaps some other experts with more knowledge will chime in.
Eddie ShipmanAll-around developer
CERTIFIED EXPERT

Author

Commented:
Are you kidding, attempting to get help from them is like pulling teeth from a lion. Their "support" forums are a joke, no one uses them anymore and the Stack Overflow Magento area is not very highly trafficked.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.