Hello everyone, Basically, I have some performance issues. Here's a somewhat complicated story:
I have a Host class with several relations to OS_family, OS_version, OS_release, Project, Reservation, etc. The entire (sanitized) SQL log is too big to be posted here, so I copied it into pastebin: http://pastebin.com/f45ea6b6c The search process is as follows: 1. A complicated query with multiple joins on those relations is ran at the beginning of the search (query below). Now, that query and SQLA processing takes under a second on 400 rows of Hosts. So it's not bad overall. Lines in pastebin: 1-466 Hosts are collected into a list and passed on to Mako for rendering HTML. Mako template accesses fields and relations like OS_version in Host objects. While rendering HTML remaining steps happen in SQL log: 2. Several tables are read in for drop-down lists. Lines in pastebin: 467-608 3. Lots of small queries happen filling in details of some objects. Those small queries take at least several seconds! Lines in pastebin: 609-1087 Those queries look like: INFO:sqlalchemy.engine.base.Engine.0x...e66c:SELECT os_version.id AS os_version_id, os_version.os_version AS os_version_os_version FROM os_version WHERE os_version.id = %(param_1)s INFO:sqlalchemy.engine.base.Engine.0x...e66c:{'param_1': 15} DEBUG:sqlalchemy.engine.base.Engine.0x...e66c:Col ('os_version_id', 'os_version_os_version') DEBUG:sqlalchemy.engine.base.Engine.0x...e66c:Row (15, 'SLED') INFO:sqlalchemy.engine.base.Engine.0x...e66c:SELECT os_version.id AS os_version_id, os_version.os_version AS os_version_os_version FROM os_version WHERE os_version.id = %(param_1)s INFO:sqlalchemy.engine.base.Engine.0x...e66c:{'param_1': 30} I don't get it - all the os_version objects should have been read in before? What I do not get is why after this query takes place, SQLA runs a lot those small queries - I included all (most?) the necessary columns in the big initial query, so Host data should be filled in by SQLA eager loading (which is default except for self-referential objects according to docs, and I have no self-referential objects here)? Is this result of subqueries in the big query? Smth else? Here's code just in case someone is patient enough to skim it: The big query function: ######################################################## def retval(req, session, columnlist, field, ip, hostname, location, architecture, os_kind, os_version, os_rel, os_update, kernel_bits, physical_box, available, avtime, additional_info, email, virt, cpu, ram, project, upnrunning, direction, hostsel=None, testfordates=True): if debug: logger.debug('start_retval') qtmpl = """ SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.ip2 AS hosts_ip2, h.hostname AS hosts_hostname, h.location AS hosts_location, h.architecture_id AS hosts_architecture_id, h.os_kind_id AS hosts_os_kind_id, h.os_version_id AS hosts_os_version_id, h.os_update AS hosts_os_update, h.kernel_bits AS hosts_kernel_bits, h.additional_info AS hosts_additional_info, h.column_12 AS hosts_column_12, h.column_13 AS hosts_column_13, h.username AS hosts_username, h.password AS hosts_password, h.alias AS hosts_alias, h.virtualization_id AS hosts_virtualization_id, h.shareable AS hosts_shareable, h.shareable_between_projects AS hosts_shareable_between_projects, h.notes AS hosts_notes, h.cpu AS hosts_cpu, h.ram AS hosts_ram, h.column_24 AS hosts_column_24, h.batch AS hosts_batch, h.asset AS hosts_asset, h.owner AS hosts_owner, h.ssh_key_present AS hosts_ssh_key_present, h.machine_type_model AS hosts_machine_type_model, h.mac_address_eth_0 AS hosts_mac_address_eth_0, h.physical_box AS hosts_physical_box, h.up_n_running AS hosts_up_n_running, h.available AS hosts_available, h.project_id AS hosts_project_id, architecture.id AS architecture_id, architecture.architecture AS architecture_architecture, os_kind.id AS os_kind_id, os_kind.os_kind AS os_kind_os_kind, os_version.id AS os_version_id, os_version.os_version AS os_version_os_version, os_rel.id AS os_rel_id, os_rel.os_rel AS os_rel_os_rel, virtualization.id AS virtualization_id, virtualization.virtualization AS virtualization_virtualization, virtualization.color AS virtualization_color, project.id AS project_id, project.project AS project_project, email.id AS email_id, email.email AS email_email, cr.reservation_id AS reservation_id, cr.reservation_start_date AS reservation_start_date, cr.reservation_end_date AS reservation_end_date, cr.reservation_status AS reservation_status, cr.reservation_businessneed AS reservation_businessneed, cr.reservation_notetohwrep AS reservation_notetohwrep, cr.reservation_email_id AS reservation_email_id, cr.reservation_project_id AS reservation_project_id FROM hosts h LEFT OUTER JOIN ( SELECT h.id AS host_id, h.ip AS host_ip, r.id AS reservation_id, r.start_date AS reservation_start_date, r.end_date AS reservation_end_date, r.status AS reservation_status, r.businessneed AS reservation_businessneed, r.notetohwrep AS reservation_notetohwrep, r.email_id AS reservation_email_id, r.project_id AS reservation_project_id FROM hosts h LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id INNER JOIN ( SELECT h.id as host_id, MIN(r.start_date) AS min_start_date FROM hosts h LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND r.start_date >= '%(today)s'::date GROUP BY h.id ) AS mindate(host_id, min_start_date) ON mindate.host_id = h.id AND (mindate.min_start_date = r.start_date OR mindate IS NULL) ) AS cr(host_id, host_ip, reservation_id, reservation_start_date, reservation_end_date, reservation_status, reservation_businessneed, reservation_notetohwrep, reservation_email_id, reservation_project_id) ON h.id = cr.host_id INNER JOIN architecture ON architecture.id = h.architecture_id %(architecture)s INNER JOIN os_kind ON h.os_kind_id = os_kind.id %(os_kind)s INNER JOIN os_version ON h.os_version_id = os_version.id %(os_version)s INNER JOIN os_rel ON h.os_rel_id = os_rel.id %(os_rel)s INNER JOIN virtualization ON h.virtualization_id = virtualization.id %(virt)s INNER JOIN project ON h.project_id = project.id %(project)s %(emailjoin)s WHERE %(upnrunning)s h.id NOT IN ( SELECT m.host_id FROM reservation r, reservation_hosts m WHERE r.id = m.reservation_id AND (r.start_date <= '%(today)s'::date AND r.end_date > '%(avtime)s'::date) ) %(hostsel)s %(ip)s %(hostname)s %(location)s %(os_update)s %(physical_box)s %(additional_info)s %(cpu)s %(ram)s ORDER BY %(field)s ip ASC""" if ip != '': ip = """ AND h.ip LIKE '%%%s%%' """ % ip if hostname !='': hostname = """ AND lower(h.hostname) LIKE '%%%s%%' """ % hostname.lower() if location != '': location = """ AND lower(h.location) LIKE '%%%s%%' """ % location.lower() if architecture != '': architecture = """ AND architecture.architecture = '%s' """ % architecture if os_kind != '': os_kind = """ AND os_kind.os_kind = '%s' """ % os_kind if os_version != '': os_version = """ AND os_version.os_version = '%s' """ % os_version if os_rel != '': os_rel = """ AND os_rel.os_rel = '%s' """ % os_rel if os_update != '': os_update = """ AND h.os_update = '%s' """ % os_update if physical_box != '': physical_box = """ AND h.physical_box = %s """ % str(physical_box) if additional_info != '': additional_info = """ AND lower(h.additional_info) LIKE '%%%s%%' """ % additional_info.lower() if project != '': project = """ AND project.project = '%s' """ % project if email != '': emailjoin = """ INNER JOIN email ON cr.reservation_email_id = email.id AND lower(email.email) LIKE '%%%s%%' """ % email.lower() else: emailjoin = """ LEFT OUTER JOIN email ON cr.reservation_email_id = email.id """ if virt != '': virt = """ AND virtualization.virtualization = '%s' """ % virt if cpu != '': cpu = """ AND lower(h.cpu) LIKE '%%%s%%' """ % cpu.lower() if ram != '': ram = """ AND h.ram LIKE '%%%s%%' """ % ram if field != '': if direction == 'ascending': field = " %s ASC, " % field elif direction == 'descending': field = " %s DESC, " % field else: field = '' if hostsel is not None: hs = " AND h.id IN (" for h in hostsel: hs += "%d, " % h hs = hs.rstrip(", ") hs += ") " hostsel = hs else: hostsel = '' today = datetime.date.today().strftime("%Y-%m-%d") nextyear = plusdays(datetime.date.today(), 365).strftime("%Y-%m-%d") if available or avtime == 0: avtime = plusdays(datetime.date.today(), avtime).strftime("%Y-%m-%d") else: avtime = plusdays(datetime.date.today(), 3650).strftime("%Y-%m-%d") if httpses.get('hwrep',False) == False: upnrunning = 'h.up_n_running = True AND h.shareable = True AND' elif upnrunning == True: upnrunning = 'h.up_n_running = True AND h.shareable = True AND' elif upnrunning == False: upnrunning = 'h.up_n_running = False AND' else: upnrunning = 'h.shareable = True AND' qdict = {'ip': ip, 'hostname': hostname, 'location': location, 'architecture': architecture, 'os_kind': os_kind, 'os_version': os_version, 'os_rel':os_rel, 'os_update': os_update, 'physical_box': physical_box, 'available': available, 'avtime': avtime, 'additional_info': additional_info, 'emailjoin': emailjoin, 'virt': virt, 'cpu': cpu, 'ram': ram, 'project': project, 'direction': direction, 'field': field, 'hostsel': hostsel, 'today': today, 'nextyear':nextyear, 'upnrunning':upnrunning} q = qtmpl % qdict #qhtml = q.replace('\n', '<br>') #req.write(qhtml) if debug: logger.debug('start_sqla_query') selectexpr = session.query(Host, Architecture, OS_Kind, OS_version, OS_rel, Virtualization, Project, Email, Reservation).from_statement(q).all() if debug: logger.debug('end_sqla_query') if debug: logger.debug('start_formatting_outlist') outlist = [] #req.write(str(columnlist)) # ['ip', 'hostname', 'location', 'architecture', 'os_kind', 'os_version', 'os_rel', 'os_update', 'physical_box', 'up_n_running', 'available', 'additional_info', 'email', 'column_12', 'column_13', 'username', 'password', 'alias', 'virtualization', 'shareable', 'shareable_between_projects', 'notes', 'cpu', 'ram', 'column_24', 'batch', 'asset', 'owner', 'ssh_key_present', 'machine_type_model', 'mac_address_eth_0', 'id', 'project'] for host, arch, osk, osv, osr, virt, project, em, rsv in selectexpr: outlist.append((host, em, rsv)) if debug: logger.debug('end_formatting_outlist') logger.debug('end_retval') return outlist ######################################################## The code utilizing big query function: ######################################################## def viewstatus(req, columnlist, page, ip, hostname, location, architecture, os_kind, os_version, os_rel, os_update, kernel_bits, physical_box, available, avtime, additional_info, email, virt, cpu, ram, project, field, upnrunning, direction): global httpses, adminmail if debug: logger.debug('start_viewstatus') # list columns # cl = [] # for (idx, x) in enumerate(columnlist): # cl.append(x + ' %d' % idx) # req.write('<br>'.join(cl)) #req.write("<br>".join(dir(req))) durl = createurl(req, page, ip, hostname, location, architecture, os_kind, os_version, os_rel, os_update, kernel_bits, physical_box, available, avtime, additional_info, email, virt, cpu, ram, project) content = retval(req, session, columnlist, field, ip, hostname, location, architecture, os_kind, os_version, os_rel, os_update, kernel_bits, physical_box, available, avtime, additional_info, email, virt, cpu, ram, project, upnrunning, direction) architectures = [ x.architecture for x in session.query(Architecture).order_by(Architecture.architecture).all() ] if u'' not in architectures: architectures.insert(0, u'') os_kinds = [ x.os_kind for x in session.query(OS_Kind).order_by(OS_Kind.os_kind).all() ] if u'' not in os_kinds: os_kinds.insert(0, u'') os_versions = [ x.os_version for x in session.query(OS_version).order_by(OS_version.os_version).all() ] if u'' not in os_versions: os_versions.insert(0, u'') physical_boxes = [ u'', True, False ] avtimes = [ 1, 7, 30, 3650 ] dateselectors = [ u'', 'eq', 'lt', 'gt', 'dif' ] dateselectorsvalues = [ u'', '=', '<', '>', '!=' ] virtualizationcolors=dict() for x in session.query(Virtualization).order_by(Virtualization.virtualization).all(): virtualizationcolors[x.virtualization] = x.color virtualizations = [ x.virtualization for x in session.query(Virtualization).order_by(Virtualization.virtualization).all() ] if u'' not in virtualizations: virtualizations.insert(0, u'') os_rels = [ x.os_rel for x in session.query(OS_rel).order_by(OS_rel.os_rel).all() ] if u'' not in os_rels: os_rels.insert(0, u'') projects = [ x.project for x in session.query(Project).order_by(Project.project).all() ] upnrunnings = [ u'', True, False ] # if u'' not in projects: # projects.insert(0, u'') hostsel = [] # try: # hostsel=httpses['ids'] # except KeyError: # pass if req.form.has_key('submit_search') or req.form.has_key('submit_addtocart'): for f in req.form: if f.startswith('host_'): hostsel.append(int(f.split('_')[1])) httpses['backtostatusurl']=durl httpses.save() kwargs={ 'selectedpage':page, 'hosts':content, 'cl':columnlist, 'archs':architectures, 'os_kind':os_kinds, 'os_version':os_versions, 'os_rel':os_rels, 'physical_box':physical_boxes, 'avtime':avtimes, 'virtualization':virtualizations, 'virtualizationcolor':virtualizationcolors, 'locolor':'', 'recno':len(content), 'dirurl':durl, 'selectedip':ip, 'selectedhostname':hostname, 'selectedlocation':location, 'selectedarch':architecture, 'selectedosk':os_kind, 'selectedosv':os_version, 'selectedosr': os_rel, 'selectedosupd':os_update, 'selectedpb':physical_box, 'selectedav':available, 'selectedavtime': avtime, 'selectedadditionalinfo':additional_info, 'selectedemail':email, 'dateselectors':dateselectors, 'dateselectorsvalues':dateselectorsvalues, 'selectedvirtualization':virt, 'selectedcpu':cpu, 'selectedram':ram, 'project':projects, 'selectedproject':project, 'datecolor':'', 'hostsel':hostsel, 'adminmail':adminmail, 'selectedupnrunning':upnrunning, 'upnrunning':upnrunnings } if httpses.has_key('cn'): kwargs['cn'] = httpses['cn'] else: kwargs['cn'] = '' if httpses.has_key('hwrep'): kwargs['hwrep']=httpses['hwrep'] else: kwargs['hwrep']=False if kwargs['hwrep']: projids = httpses['hwrepprojectids'] todaydt = datetime.date.today() rsvs = session.query(Reservation).filter(Reservation.project_id.in_(projids)).filter(Reservation.status == 'pending').filter(Reservation.end_date > todaydt).all() for r in rsvs: if len(r.hosts) == 0 and len(r.newhosts) == 0: rsvs.remove(r) kwargs['pendingrsvno'] = len(rsvs) useredits = session.query(DelayedOp).filter(DelayedOp.project_id.in_(projids)).filter(DelayedOp.opid == 1).all() kwargs['usereditno'] = len(useredits) if httpses.has_key('hwrepspecial') and (httpses['hwrepspecial'] is True): kwargs['hwrepspecial'] = True else: kwargs['hwrepspecial'] = False if debug: logger.debug('start_render_html_viewstatus') renderhtml(req,'viewstatus.mako',**kwargs) if debug: logger.debug('end_viewstatus') return apache.OK ######################################################## ############### Big query ################### SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.ip2 AS hosts_ip2, h.hostname AS hosts_hostname, h.location AS hosts_location, h.architecture_id AS hosts_architecture_id, h.os_kind_id AS hosts_os_kind_id, h.os_version_id AS hosts_os_version_id, h.os_update AS hosts_os_update, h.kernel_bits AS hosts_kernel_bits, h.additional_info AS hosts_additional_info, h.column_12 AS hosts_column_12, h.column_13 AS hosts_column_13, h.username AS hosts_username, h.password AS hosts_password, h.alias AS hosts_alias, h.virtualization_id AS hosts_virtualization_id, h.shareable AS hosts_shareable, h.shareable_between_projects AS hosts_shareable_between_projects, h.notes AS hosts_notes, h.cpu AS hosts_cpu, h.ram AS hosts_ram, h.column_24 AS hosts_column_24, h.batch AS hosts_batch, h.asset AS hosts_asset, h.owner AS hosts_owner, h.ssh_key_present AS hosts_ssh_key_present, h.machine_type_model AS hosts_machine_type_model, h.mac_address_eth_0 AS hosts_mac_address_eth_0, h.physical_box AS hosts_physical_box, h.up_n_running AS hosts_up_n_running, h.available AS hosts_available, h.project_id AS hosts_project_id, architecture.id AS architecture_id, architecture.architecture AS architecture_architecture, os_kind.id AS os_kind_id, os_kind.os_kind AS os_kind_os_kind, os_version.id AS os_version_id, os_version.os_version AS os_version_os_version, os_rel.id AS os_rel_id, os_rel.os_rel AS os_rel_os_rel, virtualization.id AS virtualization_id, virtualization.virtualization AS virtualization_virtualization, virtualization.color AS virtualization_color, project.id AS project_id, project.project AS project_project, email.id AS email_id, email.email AS email_email, cr.reservation_id AS reservation_id, cr.reservation_start_date AS reservation_start_date, cr.reservation_end_date AS reservation_end_date, cr.reservation_status AS reservation_status, cr.reservation_businessneed AS reservation_businessneed, cr.reservation_notetohwrep AS reservation_notetohwrep, cr.reservation_email_id AS reservation_email_id, cr.reservation_project_id AS reservation_project_id FROM hosts h LEFT OUTER JOIN ( SELECT h.id AS host_id, h.ip AS host_ip, r.id AS reservation_id, r.start_date AS reservation_start_date, r.end_date AS reservation_end_date, r.status AS reservation_status, r.businessneed AS reservation_businessneed, r.notetohwrep AS reservation_notetohwrep, r.email_id AS reservation_email_id, r.project_id AS reservation_project_id FROM hosts h LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id INNER JOIN ( SELECT h.id as host_id, MIN(r.start_date) AS min_start_date FROM hosts h LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND r.start_date >= '2009-05-25'::date GROUP BY h.id ) AS mindate(host_id, min_start_date) ON mindate.host_id = h.id AND (mindate.min_start_date = r.start_date OR mindate IS NULL) ) AS cr(host_id, host_ip, reservation_id, reservation_start_date, reservation_end_date, reservation_status, reservation_businessneed, reservation_notetohwrep, reservation_email_id, reservation_project_id) ON h.id = cr.host_id INNER JOIN architecture ON architecture.id = h.architecture_id INNER JOIN os_kind ON h.os_kind_id = os_kind.id INNER JOIN os_version ON h.os_version_id = os_version.id INNER JOIN os_rel ON h.os_rel_id = os_rel.id INNER JOIN virtualization ON h.virtualization_id = virtualization.id INNER JOIN project ON h.project_id = project.id AND project.project = 'LMT' LEFT OUTER JOIN email ON cr.reservation_email_id = email.id WHERE h.shareable = True AND h.id NOT IN ( SELECT m.host_id FROM reservation r, reservation_hosts m WHERE r.id = m.reservation_id AND (r.start_date <= '2009-05-25'::date AND r.end_date > '2009-05-26'::date) ) ORDER BY virtualization DESC, ip ASC ############### Big query ################### --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
