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 col
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
RGE tables if they
are applicable.
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 i
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 immedi
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 it
and good luck,
-Aaron
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 fro
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 ha
AM
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.
>
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 re-des
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 innodb_log_buff
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 hardwa
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
> 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 I
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
structur
more 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 M
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 with
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.
Af
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:
http://dev.mys
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 consid
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
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 versio
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 h
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
p
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 se
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
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 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:
If the value of this variable is positive, the MySQL server synchronizes its
binary log to disk (fdatas
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 proces
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 hap
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:
>
> http://dev
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 meg
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 p
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 alloc
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 i
ces/articles/mysql-set-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 faten
> > 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 incorre
sql@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 c
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 n
k using 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: &quo
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
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.
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 bottlenec
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 chan
asis but I 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: Perf
;
om> cc: [EMAIL PROTECTED]
Fax to:
06/28/2004
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 fil
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 up
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 amount
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 My
tly, the complaints are coming 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
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: t
ble.
>
>
> c) Seems unlikely - if the actual developers can't do it, I probably can't
> b) Seems possible, I assume no one saw a need for a constant width column
> of 4MB, so hopefully its not too difficult a) Was my first try. Inserting
> takes about twice as long as
ED]>
To: "Nicholas Elliott" <[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 l
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
y its not too difficult
a) Was my first try. Inserting takes 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,
[EMAIL 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 ap
[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
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 s
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 single
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 processors
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 rela
Benjamin Pflugmann wrote:
>
> Hi.
>
> On Thu, Feb 08, 2001 at 02:48:15PM -0700, [EMAIL PROTECTED] wrote:
> [...]
> > While we are (were) on the subject.. Any thoughts why like would not be
> > using an index in this case?
> >
> > mysql> explain select * from _data where datatime like '12:00:%';
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 m
MYISAM
improved performance. As well as doing maintenance on each table with
myisamchk.
Thanks again,
Ryan
-Original Message-
From: Ryan Hadley [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 3:01 PM
To: [EMAIL PROTECTED]
Subject: RE: Performance issues.
Maybe we're
Hi.
On Thu, Feb 08, 2001 at 02:48:15PM -0700, [EMAIL PROTECTED] wrote:
[...]
> While we are (were) on the subject.. Any thoughts why like would not be
> using an index in this case?
>
> mysql> explain select * from _data where datatime like '12:00:%';
> +---+--+---+--+---
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 th
rom: Jason Terry [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 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
gt;
Sent: Wednesday, February 07, 2001 6:32 AM
Subject: RE: Performance issues.
> Ryan:
>
> 1.) ...WHERE word LIKE '%word%' will never be fast.
>
> 2.) Since you said you have indexed the field, if you can limit your
> searches to ...WHERE word LIKE 'word%'
Tuesday, February 06, 2001 6:09 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Performance issues.
>
>
> Thanks for the quick response.
>
> The response time is slow... and the mysqld processes are what is
> hogging up
> the system.
>
> We do have indexes on the fields, but from
"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 impor
uesday, February 06, 2001 7:26 PM
To: 'Ryan Hadley'; [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 st
er
than a full table 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: Perfor
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 h
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
p
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 have an INDEX on the keyWord column, create one.
If you can induce your customer/users to type more characters, that
would help.
WHERE
I'm not much of a db admin so I really don't know how to get better
performance out of our database... but it seriously needs some speeding up.
We have this huge dictionary... It's 272235 rows. We have to be able to
search through it.
We run about 800,000 visits a day.
Right now we're doing a
77 matches
Mail list logo
