Glad you got to the bottom of it Joey.
On 7 Oct 2011 01:23, "Joey L" wrote:
> Guys - I wanted to thank you all very much for your help
> I found the offending code on the website !
> thank you very very very much...
> what did it for me was a combination of show processlist and show full
>
Guys - I wanted to thank you all very much for your help
I found the offending code on the website !
thank you very very very much...
what did it for me was a combination of show processlist and show full
processlist.
I saw the full queries and the main thing was that it was doing a
query
> From: Joey L
>
> i did google search - myisam is faster...i am not really doing any
> transaction stuff.
That's true for read-only. But if you have a mix of reads and writes, MYISAM
locks tables during writes, which could be blocking reads.
In a museum in Havana, there are t
Neil, whenever you see multiple fields you'd like to index, you should
consider, at least:
* The frequency of each query;
* The occurrences of the same field in multiple queries;
* The cardinality of each field;
There is a tool "Index Analyzer" that may give you some hints, and I
think it's maatk
Only one index at a time can be used per query, so neither strategy is
optimal. You need at look at the queries you intend to run against the
system and construct indexes which support them.
- md
On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins
wrote:
> Maybe that was a bad example. If the query
I've sent this email a few times now, mysql list kept rejecting it due to
size, sorry for any duplicates
I think you need to examine this query in particular:
| 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending
data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_ur
Maybe that was a bad example. If the query was name = 'Red' what index should
I create ?
Should I create a index of all columns used in each query or have a index on
individual column ?
On 6 Oct 2011, at 17:28, Michael Dykman wrote:
> For the first query, the obvious index on score will give
oky..you guys are much more advanced then me!
I am glad i am asking for your help...here is show processlist of mysql below.
One thing to point out - the locks are happening to the 9gig table
like i thought.
I would like to know what i can do - tuning wise to mysql to help this
locking issue.
It se
Precisely my point Singer. There's a workload here that isn't friendly with
table level locking and I would hazard a guess that there's some fights over
IO due to load vs resources. The count is going to be queued as you
describe.
A
On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang wrote:
> Okay,
I am curious.. Are you the only client on this database or or there other
connections doing work in the background? A busy insert/update heavy
application could cause these effects.
- michael dykman
On Thu, Oct 6, 2011 at 12:35 PM, Joey L wrote:
> i did google search - myisam is faster...i a
Hi all,
After a very long hiatus from maintainership (several years), I have
finally released a new version of MySQL-Diff, the CPAN module suite
which also contains mysqldiff, a CLI-based frontend tool for comparing
the table schema of a pair of MySQL databases. Its output is a
sequence of MySQL
i did google search - myisam is faster...i am not really doing any
transaction stuff.
thanks
On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore wrote:
> Sorry, hit send by accident there! *face palm*
> Just had a quick scan of the report. You've got 2 1GB disks in software raid
> - RAID1 or RAID5? I c
For the first query, the obvious index on score will give you optimal
results.
The second query is founded on this phrase: "Like '%Red%' " and no index
will help you there. This is an anti-pattern, I am afraid. The only way
your database can satisfy that expression is to test each and every reco
Sorry, hit send by accident there! *face palm*
Just had a quick scan of the report. You've got 2 1GB disks in software raid
- RAID1 or RAID5? I can also see you're creating a lot of temporary files on
disk. I think in your previous email that your biggest table's index(s) were
larger then the keyb
Joey, does your 'large' table get
On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
> here is mysqlreport ---
>
> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
> Use of uninitialized value $is in multiplication (*) at ./mysqlreport
Hi,
Can anyone help and offer some advice with regards MySQL indexes. Basically
we have a number of different tables all of which have the obviously primary
keys. We then have some queries using JOIN statements that run slowly than
we wanted. How many indexes are recommended per table ? For ex
here is mysqlreport ---
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL 5.1.49-
> thanks for the response - but do not believe queries are the issue
> because - Like I said - i have other websites doing the same exact
> queries as I am doing on the site with the 9gig table.
Contrary to popular believe, size DOES matter... And having a table large
enough so it doesn't fit in
I keep finding it extremely peculiar that a count(*) on a MyISAM table would
take that long. InnoDB needs to effectively *count* the records, but MyISAM
keeps accurate statistics and can just read it from the metadata.
This suggests to me that not all your metadata (ie., table descriptors et al)
Doing the same query on a table that fits into memory is a completely different
thing than doing the query on a table where half the needed data resides on
disk. Maybe your queries are not using an index? On a table with a few
100-thousand records this is probably a non issue for the server, whe
thanks for the response - but do not believe queries are the issue
because - Like I said - i have other websites doing the same exact
queries as I am doing on the site with the 9gig table.
-- my issue is optimizing mysql to handle lots of queries on a 9gig
db. --- i think that is the focus.
All oth
I think in order to solve your problem you will need to post the queries
running against this table along with the explain output of each problem query.
Optimizing server settings is a good start, however, individual query
performance sounds like your problem now.
Sent from my iPad
On Oct 6,
Just as an fyi - I have other databases and their corresponding apache
websites on the same server - performing okay.
It seems that apache/mysql server is just having a hard time dealing
with the access to those pages that deal with the 9gig table on that
particular site. -- Most of the access is
guys - i am having such a hard time with this..it is killing me!!!
Sorry - had to vent.
my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig
of memory. I have 2 software raided drives 1gig each.
I run a couple of databases --- my largest table is about 9gig in
size. --it is being a
On Wed, 5 Oct 2011, Jerry Schwartz wrote:
Can't you use
CREATE TABLE LIKE
and then reset the auto-increment value?
Thanks. Since when does "create table like" exist? I was unaware of it,
but I see it exists in mysql 5.1. The tricks I described worked since 3.x
or thereabouts.
--
25 matches
Mail list logo
