473,287 Members | 1,533 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Memory Maxed out and Slow Query Performance

We have two main query types running against a table of some 2 million
rows and have gotten query response down to well under a second by
using the right indexes.

Problem is that we are running an advertising campaign that brings a
concentrated block of users to the site. When this happens one of the
queries which relies on a particluar index comes severely of the rails
and can take up to 2 minutes filling the slow query log for 15 to 20
minutes. The other query type never appears in the slow log at these
times.

Can anyone explain why this is happening?

The server has 2Gb of ram and a key_buffer of 1Gb. When the problems
occur mysql uses all of the key_buffer but the total value of the MYI
files is under 250Mb. What is in memory - thread related buffers?

It seems as though the index required for the query is not in memory
and the disks are coming into play. Why is only one index affected?

I would really appreciate some pointers, what can I check to pinpoint
the problem and what settings should I implement to preserve
performance?

Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.

Many thanks,

Gary
Jul 23 '05 #1
1 1651
ga********@hotmail.com (Gary Wales) wrote in
news:d8**************************@posting.google.c om:
Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.


Might this be the problem? Why are you using persistant connections as
opposed to regular, non-persisting ones?
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Ryan | last post by:
Hello, I am having trouble with a production db server that likes to gobble up memory. It seems to be a slow burn (maxing out over about an 18 hour time frame, before pegging both procs on the...
4
by: M Wells | last post by:
Hi All, I have a table that currently contains approx. 8 million records. I'm running a SELECT query against this table that in some circumstances is either very quick (ie results returned in...
3
by: gizmo | last post by:
I have a stored procedure that queries a database using a Select statement with some inner joins and conditions. With over 9 million records it takes 1 min 36 sec to complete. This is too slow...
2
by: Yonatan Goraly | last post by:
I am in the process of adding PostgreSQL support for an application, in addition to Oracle and MS SQL. I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board. I have a...
6
by: hauger | last post by:
(DB2 V.8.1) Hi @ll, when i start my DB2 and my application connects and makes some SQL Tests my system is running out of memory (UNITED LINUX 1.0) goes slow and needs very very long time to...
7
by: Salvador | last post by:
Hi, I am using WMI to gather information about different computers (using win2K and win 2K3), checking common classes and also WMI load balance. My application runs every 1 minute and reports...
5
by: Mike S | last post by:
I'm working on a .NET application that was under development by a previous developer, who is no longer working with the company. The program basically extracts records from a source database and...
0
by: Andy_Khosravi | last post by:
I'm having a problem trying to optimize the performance of one of my A97 databases. I have very slow record navigation after a change I made to the table structure, and I'm not sure how best to...
15
by: CMOS | last post by:
one of the projects im working in currently requires use of ultra large sized maps, lists, vector, etc. (basically stl containers). Sizes might grow up to 1000 Million entries. since it is...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.