Suggested tools to benchmark configuration changes

2007-09-11 Thread Rob Wultsch
I have a server that has several hundred table in a few different databases comprising almost a gig of data, all running on a rather old (3.23) version of mysql. I have used the slow query log to identify queries and have optimized the queries significantly. At this point the entries in the slow-q

Re: Question: table schema optimization

2007-09-11 Thread Chris
Hailiang Ji wrote: Folks, A help needed. My manager's pushed me to optimize the tables that I created in distributed in several DBs. I have tried best to explain to him that I have followed the strict formula design to do the Join, Search and so on. However, the system performance is not good en

Re: Question: table schema optimization

2007-09-11 Thread Brent Baisley
You should read up on the "show status" and "show variables" output. That will give you a start as to perhaps some obvious issues. For instances, your opened_tables and threads_created should not be large. Using the right table types is also a very big issue, although you may have gone down

Mail not being accepted

2007-09-11 Thread Sam Klin
When I try to email to this list my email is reject with the following message? Error: 552 Mail with no Date header not accepted here My Mail has a date header in it...?

Re: Copying InnoDB files to remote server -> remote server won't start

2007-09-11 Thread Whil Hentzen
Baron Schwartz wrote: Whil Hentzen wrote: Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /

Re: Copying InnoDB files to remote server -> remote server won't start

2007-09-11 Thread Baron Schwartz
Whil Hentzen wrote: Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /var/logs, and it contai

Re: Copying InnoDB files to remote server -> remote server won't start

2007-09-11 Thread Whil Hentzen
Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /var/logs, and it contains: "./ibdata1: err

Copying InnoDB files to remote server -> remote server won't start

2007-09-11 Thread Whil Hentzen
Hi gang, After reading through two years of 'how do I back up my database' threads, I'm trying out the various backup mechanisms offered up. I've read through these steps: http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html Running a local box and a remote box, both with Fedora 6, MyS

Re: archive engine potential bug

2007-09-11 Thread Rob
also when it hits a dupl. and i skip the record, and continue slave it hits another duplicate entry almost instantly, when i then drop the table on the slave, and recreate it manually and set it to myisam at that time, the slave continues without a problem. On Tue, 2007-09-11 at 16:42 +0200, WiNK

Re: Really strange index/speed issues

2007-09-11 Thread Chris Hemmings
Baron Schwartz wrote: Chris Hemmings wrote: Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may alread

Re: Really strange index/speed issues

2007-09-11 Thread Michael Dykman
The results of an EXPLAIN have a lot to do with the data which is actually on the system. In this case, it seems to hinge on the distribution of your 'price' attribute.. how many records on your system? and what is the general distribution of the price attribute? (how many distinct values) On

Re: Really strange index/speed issues

2007-09-11 Thread Baron Schwartz
Chris Hemmings wrote: Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thoug

Re: Really strange index/speed issues

2007-09-11 Thread Chris Hemmings
Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Se

Re: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread Baron Schwartz
Fan, Wellington wrote: > > Given: MySQL 4.0.12, I need to implement a pageview log with a resolution of 1 day. If you want to brute-force it, I think I would go this route: create table hits ( day date not null primary key, hitcount int unsigned not null, ); insert ignore into hits(day, hitc

RE: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread Fan, Wellington
> > Given: MySQL 4.0.12, I need to implement a pageview log with a > > resolution of 1 day. .. > > Would the "REPLACE" method work? > > David Hmmm...as I read the docs, the "LOCK IN SHARED MODE" seemed to be the real key to this. I created a test script and ran: $ ab -n100 -c100 local

Re: help with ORDER BY

2007-09-11 Thread Michael Dykman
Thing 1: your auto_increment key MUST be your primary key. Thing 2: the timestamp field will be updated with the current epochal timestamp which only increments every second.. as you have a timestamp field as you primary (and therefore unique) key, you will never be able to perform more than on

archive engine potential bug

2007-09-11 Thread WiNK / Rob
W00ps forgot to update subject of my email, WiNK / Rob wrote: Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) def

Re: help with ORDER BY

2007-09-11 Thread WiNK / Rob
Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`clickID`

Re: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread dpgirago
> Given: MySQL 4.0.12, I need to implement a pageview log with a > resolution of 1 day. > > I propose this table: > > CREATE TABLE `pageviews` ( > `id` int(11) NOT NULL auto_increment, > `date` date NOT NULL default '-00-00', > `url` char(120) NOT NULL default '', > `views` mediumint(9) NO

help with ORDER BY

2007-09-11 Thread Pedro Mpa
Hi all! I need some help with ORDER BY in the following example. I want to order by selected category, then by subcategories of the selected category, then by categories with the same parent_id of the selected category, then by random if possible, or random within the categories if possible, but t

Re: Big SELECT: ordering results by where matches are found

2007-09-11 Thread Chris Sansom
At 13:34 -0400 10/9/07, Baron Schwartz wrote: Looks like you've found the solution you need. The only other suggestion I have is to use UNION ALL if you don't need to eliminate duplicate rows in the UNION, because there's some overhead for checking for them. Hi Baron Thanks for this, and I

Re: What should be a simple query...

2007-09-11 Thread David Schneider-Joseph
Try this: SELECT RMAs.rma_id FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id = rma_line_items.rma_id GROUP BY RMAs.rma_id HAVING COUNT(*) > 1 On Sep 10, 2007, at 11:36 PM, Mike Mannakee wrote: I have two tables, one called RMAs and the other called rma_line_item