Hi all,
I've got some semi-general questions on the topics in the title. What I'm
looking for is more in the line of theory than query specifics. I am but a
poor peasant boy.
What I have is an application that makes heavy use of views. If I understand
views correctly (and I may not), views
At first blush, your problem would appear to concern the lack of index-use.
That's where I would begin my investigation. It might be painstaking, but
I would do something like this:
For each view
Look at the Join(s) and see what columns are being joined
Look at the tables and see what
.
Hope that helps or points you in the right direction.
Brent Baisley
On Sep 4, 2008, at 4:26 PM, Josh Miller wrote:
Good afternoon,
I have recently converted a large table from MyISAM to InnoDB and am
experiencing severe performance issues because of it. HTTP response
times have gone
Aaron Blew wrote:
Here are a couple ideas:
* Decrease innodb_autoextend_increment to 8 or even 4. You may see
additional IO wait because you're pre-allocating space in chunks
disproportinate to what you immediately need, causing bursty performance.
* If your remaining MyISAM tables don't need
On Fri, Sep 5, 2008 at 12:55 PM, Josh Miller [EMAIL PROTECTED] wrote:
Aaron Blew wrote:
Here are a couple ideas:
* Decrease innodb_autoextend_increment to 8 or even 4. You may see
additional IO wait because you're pre-allocating space in chunks
disproportinate to what you immediately need,
Good afternoon,
I have recently converted a large table from MyISAM to InnoDB and am
experiencing severe performance issues because of it. HTTP response
times have gone from avg .25 seconds to avg 2-3 seconds. Details follow:
PHP/MySQL website, no memcached, 3 web nodes that interact
convenient for InnoDB.
With kind regards,
TomH
-Original Message-
From: Josh Miller [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 04, 2008 10:27 PM
To: mysql@lists.mysql.com
Subject: innodb/myisam performance issues
Good afternoon,
I have recently converted a large table from MyISAM
Tom Horstmann wrote:
Hello Josh,
why you moved your table to InnoDB? Your description doesn't sound like the
tables rows
are accessed concurrently and need to be locked? Are you sure you need
InnoDB for this table?
If you need InnoDB you probably need to redesign your queries and table
The rows in this table are accessed concurrently as any activity on the
site is recorded/added/updated to this table. We have several others
which serve similar purposes, (sessions, totaltraffic, etc...).
Is the performance lag occurring with read-only queries and updates/inserts
to the
between 16-32MB if you have many transactions.
TomH
-Original Message-
From: Tom Horstmann [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 04, 2008 11:15 PM
To: 'Josh Miller'
Cc: mysql@lists.mysql.com
Subject: RE: innodb/myisam performance issues
The rows in this table are accessed
On Thu, Sep 4, 2008 at 4:26 PM, Josh Miller [EMAIL PROTECTED] wrote:
We're seeing a significantly higher percentage of IO wait on the system,
averaging 20% now with the majority of that being user IO. The system is
not swapping at all.
O_DIRECT may not be the best setting for your hardware.
Tom Horstmann wrote:
Addendum..
Please also try increasing your innodb_log_file_size to a much higher value
if you
have lots of writes/transactions. Maybe 250MB is a good first try.
You need to delete/move the InnoDB logs before restart.
Not sure about this, but please also set
Perrin Harkins wrote:
What you really need to do is look at which queries are slow and run
EXPLAIN plans for them. Most big performance problems like you're
describing are due to index issues, so that's where you should be
looking. Server tuning comes lat
We definitely need to work on
To: Tom Horstmann
Cc: mysql@lists.mysql.com
Subject: Re: innodb/myisam performance issues
Tom Horstmann wrote:
Addendum..
Please also try increasing your innodb_log_file_size to a much higher
value
if you
have lots of writes/transactions. Maybe 250MB is a good first try.
You need to delete/move
On Thu, Sep 4, 2008 at 6:43 PM, Josh Miller [EMAIL PROTECTED] wrote:
We'd like to prove InnoDB and move onto that storage engine for the
transaction support, MVCC, etc.. but we're finding that performance is poor.
Well, thousands of large InnoDB database users prove that the engine
itself has
On Thu, Sep 4, 2008 at 1:26 PM, Josh Miller [EMAIL PROTECTED]wrote:
Good afternoon,
I have recently converted a large table from MyISAM to InnoDB and am
experiencing severe performance issues because of it. HTTP response times
have gone from avg .25 seconds to avg 2-3 seconds. Details follow
RV Tec wrote:
Folks,
A few months ago, I came here asking for directions on how I could
improve performance of MySQL. Back then, I was using OpenBSD on a dual
Opteron 248 with 2GB, LSI MegaRAID 320-1, 15k RPM SCSI discs, MySQL was
(still is) 4.0.27, the database is MyISAM, reaching 50GB.
Folks,
A few months ago, I came here asking for directions on how I could improve
performance of MySQL. Back then, I was using OpenBSD on a dual Opteron 248
with 2GB, LSI MegaRAID 320-1, 15k RPM SCSI discs, MySQL was (still is)
4.0.27, the database is MyISAM, reaching 50GB.
After some
RV, you may find that increasing the size of the key_buffer could have
a negative effect on performance. You want to make sure that there's
enough physical RAM for all the processes on the machine, to avoid
paging/swapping to disk, which is very slow.
Here's a an interesting note:
Hi,
I've got a view of a base table that is 100% identical to that base table
except for one column, which is a projection of the base table after its MD5
hashed. The table is largish (~700,000 rows) and is growing quickly.
Queries on the base table are nice and fast, but on the hashed view are
If I'm understanding right - the view contains an additional column
that is an MD5 hash of some or all of the data in the base table,
right?
Yes, I would expect that to be very very slow. When selecting, your
database engine has tro calculate 700K MD5 hashes. Slow. When
selecting a subset it
Hi, thanks for the comments,
If I'm understanding right - the view contains an additional
column that is an MD5 hash of some or all of the data in the
base table, right?
Close. It's got all of the data in the base table except for the colum
that's being hashed - we show the hashed version
The hit with a join on indexed columns is negligible. Relational
databases live for joins - they eat them for breakfast! Seriously, as
long as it's indexed in both tables, it'll be super-speedy.
Dan
On 9/22/06, Christopher Brooks [EMAIL PROTECTED] wrote:
Hi, thanks for the comments,
If I'm
yes, that is the reason for slowness .
Thanks,
Ravi
On Tuesday 11 April 2006 10:51, Mohammed Abdul Azeem wrote:
Hi Ravi,
Since the sync'ing is done to disk with sync_binlog=1, the update
queries to server are slower compared to the server having sync_binlog=0
rite ?
Thanks,
Abdul.
On
Thanks Ravi
On Tue, 2006-04-11 at 11:41 +0530, Ravi Prasad LR wrote:
yes, that is the reason for slowness .
Thanks,
Ravi
On Tuesday 11 April 2006 10:51, Mohammed Abdul Azeem wrote:
Hi Ravi,
Since the sync'ing is done to disk with sync_binlog=1, the update
queries to server are
Hi,
I have a master/slave setup ( replication enabled ) for mysql in two
different geographic locations ( one master/slave set up in each
location). In one location i have configured the sync_binlog=1 . And the
other location does not have the same.
My problem is, when i run similar update
Hi Abdul,
When sync_binlog is set to 1, innodb fsyncs the binary log to
disk after every single write to binary log, but not in the case of
sync_binlog=0.
From MySQL manual:
snip
If the value of this variable is positive, the MySQL server synchronizes its
binary log to disk
Hi Ravi,
Since the sync'ing is done to disk with sync_binlog=1, the update
queries to server are slower compared to the server having sync_binlog=0
rite ?
Thanks,
Abdul.
On Tue, 2006-04-11 at 10:51 +0530, Ravi Prasad LR wrote:
Hi Abdul,
When sync_binlog is set to 1, innodb
Hi,
My software use to run with mySQL 4.1, but now i have to use some of the new
features of mySQL 5.0 i did dump all databases, uninistalled mysql4.1,
installed the mysql5.0 and start loading the .sql file. This operation use
to take about 4 hours but know it is taking 1day!!! Did anything
On Fri, 2005-07-15 at 13:28 -0700, David Griffiths wrote:
David,
Thanks for your suggestions, i'll give them a try.
There are other tuning choices (including the thread-pool-cache). The
best resource is the page on innodb performance tuning, and it can be
found here:
Hi,
A few days ago i posted a quaestion about performace, I now have a
little more info, hopefully someone can help.
I have a table, tblShoppingCart with 3 fields,
cartUid (int 11 auto increment)
userUid (int 11, indexed)
strCartHash (varchar 32)
The table is innodb
Nomally my server load
Tony,
You said that you copied the my.cnf file from huge.cnf - not sure what
version you are using (I missed your original post), but the my-huge.cnf
in mysql 4.0.24 is for MyISAM. You should have used
my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. The
my-huge.cnf
Hi David,
On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote:
Tony,
- not sure what version you are using
4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and
apache webserver and not much else.
You should have used
my-innodb-heavy-4G.cnf as the starting point
Tony,
Your my.cnf file is set up for MyISAM, not InnoDB. MySQL allows you to
allocate memory and resources to any and all storage engines. Yours is
set up to give lots of resources to MyISAM, and none to InnoDB.
Reducing MyISAM
key_buffer = 384M - this is way too much - I'd set to to 2-16
Hi,
I'm managing a large free website running on Linux, with MySQL 4.0.23 and
has 2Gb memory. (PIV 2,6 Ghz)
MySQL is configured to have 382M key-buffer.
There are two problems I have, wich have to do with a large table. This
table contains 5 million records
and is the core of our application.
From: Almar van Pel
After some time (sometimes a week sometimes a month) it appears that the
index of the table gets stuck.
It tries to read from the table but does not get response. This causes the
connectionqueue to fill up
and the load on the system increases dramatically. In other words,
or
not of internal temporary tables, ...
Mathias
-Original Message-
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 17:05
To: Almar van Pel; mysql@lists.mysql.com
Subject: Re: Performance issues when deleting and reading on large table
From: Almar van Pel
Hi Jigal, Mathias,
Thanks the time you took to reply to my issue's!
I would like to clear out some things.
It's a probably a case of not having the cardinality of indexes right and
thus making wrong decisions for queries.
- Currently there is not a single query in the application that does
@lists.mysql.com
Cc: 'Jigal van Hemert'; 'mathias fatene'
Subject: RE: Performance issues when deleting and reading on large table
Hi Jigal, Mathias,
Thanks the time you took to reply to my issue's!
I would like to clear out some things.
It's a probably a case of not having the cardinality
It's a probably a case of not having the cardinality of indexes right
and
thus making wrong decisions for queries.
- Currently there is not a single query in the application that does not
use
the correct index. We only have key-reads. Wich would mean that MySQL is
creating these incorrect
-datatype.html.
Massive load is better without indexes, which are only good for selects.
Mathias
-Original Message-
From: Jigal van Hemert [mailto:[EMAIL PROTECTED]
Sent: dimanche 24 avril 2005 19:46
To: Almar van Pel; mysql@lists.mysql.com
Cc: 'mathias fatene'
Subject: Re: Performance issues
Since MySQL stores RowIDs with the indexed words
instead of the table's primary key IDs, and since it
uses only one index per table in any query, performing
a full-text search on a large table (several million
rows) and joining it with another large table proves
to be extremely slow!
The
On Tue, Jun 22, 2004 at 01:34:39PM -0400, Aram Mirzadeh wrote:
We have an internal SNMP monitoring system that is monitoring about
10,000 devices. Each device is pinged then pulled for about an
average of 25-30 elements. Each of the ping results and elements
are then stored in text file,
to:
06/28/2004 02:24 Subject: Re: Performance issues
PM
couldn't find a quick reference to that.
Cheers
Andrew.
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Aram Mirzadeh [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, June 28, 2004 7:24 PM
Subject: Re: Performance issues
On Tue, Jun 22, 2004 at 01:34:39PM -0400, Aram
On Mon, Jun 28, 2004 at 09:21:04PM +0100, Andrew Pattison wrote:
By default MySQL flushes keys to disk with every INSERT, hence the
performance degredation with performing several single INSERTs one after the
other. The following extract from the MySQL documentation hints at one way
of
We have an internal SNMP monitoring system that is monitoring about
10,000 devices. Each device is pinged then pulled for about an average
of 25-30 elements. Each of the ping results and elements are then
stored in text file, then another system picks them up (NFS) and inserts
them into a
Hi!
Can you give more details on the problematic inserts you're doing (table
structure, indexes and insert command) ?
Also, do you believe your queries would benefit from MySQL's query cache?
Maybe it's worth upgrading to version 4 and use this feature, even if
you allocate just a small
At 12:34 PM 6/22/2004, you wrote:
We have an internal SNMP monitoring system that is monitoring about 10,000
devices. Each device is pinged then pulled for about an average of 25-30
elements. Each of the ping results and elements are then stored in text
file, then another system picks them
from a php Forum and a php picture
Gallery. From the OS side, I don't see any problems, along with the
Apache side. So, I am leaning towards an improper MySQL configuration.
Where do I go to get more information on where to start looking for
performance issues for the DB?
--
Brent Baisley
don't see any problems, along with the
Apache side. So, I am leaning towards an improper MySQL configuration.
Where do I go to get more information on where to start looking for
performance issues for the DB?
Thanks
Eric
Nicholas,
- Original Message -
From: Nicholas Elliott [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Friday, July 11, 2003 6:04 PM
Subject: InnoDB Performance issues
--=_NextPart_000_003B_01C3479C.77A1AB60
Content-Type: text/plain;
charset=iso-8859-1
Content
about twice as long as myisam... sure, I can deal
with that. Selecting a specific date is in the same ballpark as well, so little
problem there. What I'm having severe performance issues on are querys that group, or
do a count(*). For example:
mysql explain select date from
In the last episode (Jul 11), Nicholas Elliott said:
I've been experimenting with the best way to store a large (~100GB)
of data for retrieval. Essentially, I'm storing 9 variables for
approximately 1,000,000 locations a day for the last ten years. This
can work out at around 4MB a variable a
[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, July 11, 2003 11:29 AM
Subject: Re: InnoDB Performance issues
In the last episode (Jul 11), Nicholas Elliott said:
I've been experimenting with the best way to store a large (~100GB)
of data for retrieval. Essentially, I'm storing 9 variables
ballpark as well, so little
problem there. What I'm having severe performance issues on are querys that
group, or do a count(*). For example:
mysql explain select date from basic_daily_grid_innodb
I am having performance problems with my MySQL installation - what would be an
appropriate channel for requesting help to such a problem?
I have posted to this list twice and another one as well. Volumes are very high on
using MySQL/standard SQL questions, but not an single suggestion has been
[snip]
I am having performance problems with my MySQL installation - what would be
an appropriate channel for requesting help to such a problem?
[/snip]
Repost your original concern and I will see if I can help.
Jay
-
Before
PROTECTED]
- Original Message -
From: webmaster [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 07, 2002 5:13 PM
Subject: MySQL performance issues - PLEASE ADVICE!!
I am having performance problems with my MySQL installation - what would be
an appropriate channel for requesting help
Have you checked the network connection? You should be running at
full-duplex so you don't get collisions. But more importantly, make sure you
aren't getting errors on the line. A poor crimp or pinched wire could really
slow things down, especially if you try running at 100MB.
Try copying a
Backgroud:
We have a process that runs on a server (APPDEV1) that writes records to a
mysql server (SQLDEV0). We attempted to move the databases from SQLDEV0 to
SQLDEV1 and have run into some sort of performance bottleneck. The server
SQLDEV0 is a Compac server with ~2GB of ram and two
hello all,
i am interested in determining whether there are performance issues of which
i should be aware related to the usage of a table which takes up ones or
tens of gigabytes. i am planning to use an index of some kind.
i'd appreciate hearing about any experience which you'd relate, or
any
Hi.
On Wed, Feb 07, 2001 at 03:01:28PM -0500, [EMAIL PROTECTED] wrote:
[...]
We have one table with all the defintions on it's own row.
Then we have built off of that table another table that is only the distinct
words, no definitions.
This is because if a word has 10 definitions, it makes
Quentin Bennett wrote:
Hi,
For an indexed column, the index is used if the start of the string is used:
LIKE 'a string of text%' may use an index
LIKE '%any old string%' will not, since the start of the string is unknown.
The index will only be used if the server decides that it
ay, February 07, 2001 11:01 AM
To: Jim Beigel; [EMAIL PROTECTED]
Subject: Re: Performance issues.
Cool, I like this info. (especially key_buffer_size stuff)
However, I was running my numbers against what you said would be good. And
this is what I came up with...
The Key_reads/Key_read_reque
Woah! 800,000 visits a day!? Wow... try 80,000.
-Original Message-
From: Ryan Hadley [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 06, 2001 5:25 PM
To: [EMAIL PROTECTED]
Subject: Performance issues.
I'm not much of a db admin so I really don't know how to get better
performance
ent Hoover [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 06, 2001 5:42 PM
To: [EMAIL PROTECTED]
Subject: Performance issues.
Ryan:
If your response time for this query is slow, it is likely that an INDEX
will help
you. (Read about CREATE INDEX in the MySQL manual.
If you don't already hav
scan.
Have you got the results of 'explain select ' to see if your index is
actually being used.
Regards
Quentin
-Original Message-
From: Ryan Hadley [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 7 February 2001 12:09
To: [EMAIL PROTECTED]
Subject: RE: Performance issues.
Thanks
'; [EMAIL PROTECTED]
Subject: RE: Performance issues.
Hi,
For an indexed column, the index is used if the start of the string is used:
LIKE 'a string of text%' may use an index
LIKE '%any old string%' will not, since the start of the string is unknown.
The index will only be used if the server decides
"Only" 272235??? I enter on average about 75,000 to 80,000 records a day
(and some times, I break 100,000 records a day. I do monthly rotations so
it's easy to calculate how big my table gets). Granted, I don't know what
your table structure is but mine is very simple. All I do is run
70 matches
Mail list logo
