Re: Table Design
Hi wultsch, Thanks a lot. Every thing is going fine. I am only concerned with duplicate index, as it is using disk space. Is there any solution so that i can ignore duplicate index by altering the table design. OR i have to end up with duplicate index. Thanks, Krishna Chandra Prajapati On Thu, Apr 24, 2008 at 9:27 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati > <[EMAIL PROTECTED]> wrote: > > Hi All, > > > > Below is the table design on mysql server. > > > > CREATE TABLE `coupon_per_course` ( > > `coupon_id` int(10) unsigned NOT NULL default '0', > > `course_id` int(10) unsigned NOT NULL default '0', > > PRIMARY KEY (`coupon_id`,`course_id`), > > KEY `idx_coupon_per_course` (`coupon_id`), > > KEY `idx_coupon_per_course_1` (`course_id`) > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > > > In my view index idx_coupon_per_course should not be there. Since > coupon_id > > is a primary key. so it will be utilized for searching. > > > > Before removing index idx_coupon_per_course > > mysql> do benchmark(100,(select sql_no_cache ac.plan from > > affiliate_coupon ac, coupon_per_course cpc where > ac.coupon_code='TST0G0' > > and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); > > Query OK, 0 rows affected (0.06 sec) > > > > > > After removing index idx_coupon_per_course > > mysql> do benchmark(100,(select sql_no_cache ac.plan from > > affiliate_coupon ac, coupon_per_course cpc where > ac.coupon_code='TST0G0' > > and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); > > Query OK, 0 rows affected (0.07 sec) > > > > I am not able to understand why after removing the index > > idx_coupon_per_course, it is taking more time. As it must take less > time. > > > > Some other statistics are > > mysql> select count(*) from coupon_per_course; > > +--+ > > | count(*) | > > +--+ > > | 296218 | > > +--+ > > mysql> select count(distinct coupon_id) from coupon_per_course; > > +---+ > > | count(distinct coupon_id) | > > +---+ > > |211519 | > > +---+ > > > > Please suggest me the correct table design. > > Thanks in advance. > > > > Thanks, > > -- > > Krishna Chandra Prajapati > > > Hi Krishna, > I have run into similar issues in the past and have ended up having > duplicative indexes. The multi column indexes have higher cardinality > and although it should not be an issue, lookup on the first portion of > the index alone is not as efficient. I would love to know why this > is/what I am dong wrong. > > Are you having issues with INSERT speed, or the size of the your indexes? > > Posting your explain (extended) and show index may be helpful. > > For whatever it is worth, I always suggest explicit joins and using AS: > SELECT sql_no_cache ac.plan > FROMcoupon_per_course AS cpc >INNER JOIN affiliate_coupon AS ac USING(coupon_id) > WHERE cpc.course_id = 213336 >AND ac.coupon_code='TST0G0' > > I think it makes queries much easier to read and understand. > > -- > Rob Wultsch > [EMAIL PROTECTED] > wultsch (aim) > -- Krishna Chandra Prajapati
Re: TO_DAYS Date Range Question
David Perron schrieb: Hi Sebastian- Wanted to follow up on this. I figured out the problem. You actually have to use the LEAST & GREATEST operators when comparing multiple values, this statement works perfectly. LEAST(EndDays,Q2EndDays) - GREATEST(Q2StartDays,StartDays) as DaysInQ2, Thanks again for the tip! Have a great day. oh, yes, for sure, sorry, my mistake! :-) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
D Hill schrieb:
On Thu, 24 Apr 2008 at 08:58 +0200, [EMAIL PROTECTED] confabulated:
D Hill schrieb:
I have something I am trying to resolve with an over abundant number
of slow queries. Perhaps it is because of some additional indexes
needed. As soon as I enabled the option
'log_queries_not_using_indexes = 1' in the configuration file, I
started getting messages relating to the select query:
SELECT wite_what, wite_desc FROM witelist
WHERE
wite_what = '$oct1' OR
wite_what = '$oct1.$oct2' OR
wite_what = '$oct1.$oct2.$oct3' OR
wite_what = '$oct1.$oct2.$oct3.$oct4' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '$from_dom' OR
wite_what = '$rcpt_dom';
did you tried (result depending on your MySQL version):
WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
'$oct1.$oct2.$oct3.$oct4', '[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', '$from_dom', '$rcpt_dom');
MySQL version is 5.0.51. Sorry I forgot to mention that. I did change
the query to what you have shown. I'll have to wait till the server
comes under a load to tell. I have noted when the last slow query was
logged for this and will see.
you could also vary with thee index length if wite_what.
Right now the index is for the full length of the field (128). I just
ran a query for the length of wite_what and the maximum length so far is
34. So, I will cut the index length down to 64.
and what indexes do you have currently exactly?
id - is the primary and has an index type btree
wite_what - is a unique and has an index type of btree
so this looks all ok, i am not sure if the query time includes the time if
the query needs to wait for locked tables ...
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query for details
Hi all, I have one table as following AccessDetails DateTimeUserName SiteName StatusVirus_Category |2008-04-23 | 14:27:38 | 172.16.1.40| en.wikipedia.org | A | unclassified | | 2008-04-23 | 14:27:39 | 172.16.1.40| en.wikipedia.org | A | unclassified | | 2008-04-23 | 14:27:40 | 172.16.1.92 | www.google.com | D | Internet | | 2008-04-23 | 14:27:40 | 172.16.1.40 | en.wikipedia.org | A | unclassified | | 2008-04-23 | 14:27:41 | 172.16.1.40 | en.wikipedia.org | O | Internet | | 2008-04-23 | 14:27:42 | 172.16.1.40 | en.wikipedia.org | A | unclassified | | 2008-04-23 | 14:27:42 | 172.16.1.40 | en.wikipedia.org | O | unclassified | | 2008-04-23 | 14:27:43 | 172.16.1.40| en.wikipedia.org | A| unclassified | | 2008-04-23 | 14:27:43 | 172.16.1.40| meta.wikimedia.org | A| unclassified | | 2008-04-23 | 14:27:47 | 172.16.1.34 | googletb.skype.com | D | unclassified | from the above table , i need the details of , how many total users, how total accessed sited,how many total denied sites ,how many total overridden sites and how many total sites(accessed+denied+overridden) for particular Virus_Category with Date and Time. In above table the Status shows A for accessed sites, D for Denied sites,O for Overridden sites.. how can i form the query?? Thanks,
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
JW wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT This machine is not what is seems to be unfortunately. 1. Dell until their latest series with the Woodcrest based CPUs was a notoriously bad performer period. 2. Raid 5 + Database == run faster over serial cable. Use RAID 1+0. 3. Dell hasn't made a re-branded a decent RAID controller that is anything more than 18 months old. 4. 16GB doesn't help you because you are limited by your 32bitness. Get a real machine with real processors. 5. The CPU is hyper threaded, not dual core. I doubt you would ever get decent performance out of this for any database let alone MySQL. Tell your boss to suck it up, spend 5k. You can get a new, decent Dell for that. Or better yet, a HP. Sincerely, Joshua D. Drake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display more than 2500 rows
Velen wrote: Hi, May be it's not the right forum i'm posting to. I have a Mysql Query : Select a.code,b.description, b.other_details,a.qty,a.price from xyz a, bcd b where a.code=b.code and a.id='5' order by a.id This is running fine but when using VB6 to display it in a Msflexgrid, it's a nightmare! It will take about 3-5 mins to display around 2500 rows. I'm not sure what you mean when you say it is "running fine". If that means that you are getting the right result but it's taking too long, that's one thing. If you mean that the speed is fine until you get VB6 involved, that's another. Try the query in MySQL itself and see how long it takes to display the data. If the speed is perfectly acceptable in raw MySQL but dreadful when you use VB6, you've pretty much proven that VB6 is the problem, not MySQL. In that case, you may want to ask on a VB6 newsgroup for hints in making it perform better. Can anyone suggest a better alternative to Msflexgrid or how to improve the speed on msflexgrid? Thanks. Regards, Velen -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spatial data and mysql
I have been storing points in mysql without use of the spatial extension. I do not forsee the need to ever store more than points, and am wondering if the spatial extensions would offer any significant advantages. I have looked a bit for tutorials, etc... and have not found much. One feature that I would like is to be able to find all points withen X distance from of point Y, without doing a table scan. Would the spatial index (Rtree) be able to achieve this? Are there any good tutorials (or heaven forbid, books) that anyone can suggest? Should I go hang out with the cool kids that are using postGIS ;) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: % wildcard host permission not working
Did you "FLUSH PRIVILEGES"? I'd also check that the username and passwords are the same for each host entry, I've had problems if passwords were different for a shared username from different hosts. On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote: I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
I think what we need to know is more stuff about the database itself. How big is it? I assume if you were able to serve it from a Mac Mini it can't be all that big. 16GB should be big enough to contain all the data and serve it up quickly. And while 4-disk RAID 5 isn't all that great, it's certainly better than a single ATA disk in Mac Mini. If your database is huge, then 16GB is barely enough to hold innodb buffer pool (if innodb) and maybe the indexes. Then your RAID config will come into play (and maybe filesystem type, tuning). Then whether it's a Dell PE1750 or a 6650 would not make a huge difference since it's I/O bound. Were you testing with something like production data or just some test data? Have you modified my.cnf to reflect the new hardware config? Like Baron Schwartz asked, does your test reflect real-life workload? And yes, the 3.0Ghz Xeon processors you mentioned are 32-bit. You get that error message if your processor doesn't have EM64T capability. -Paul JW wrote: Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little "laptop" hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the "-bigmem" kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about "this CPU does not support long (something) please use a 32-bit OS" - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Display more than 2500 rows
Hi, May be it's not the right forum i'm posting to. I have a Mysql Query : Select a.code,b.description, b.other_details,a.qty,a.price from xyz a, bcd b where a.code=b.code and a.id='5' order by a.id This is running fine but when using VB6 to display it in a Msflexgrid, it's a nightmare! It will take about 3-5 mins to display around 2500 rows. Can anyone suggest a better alternative to Msflexgrid or how to improve the speed on msflexgrid? Thanks. Regards, Velen
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, On Thu, Apr 24, 2008 at 12:20 PM, Jeremy Cole <[EMAIL PROTECTED]> wrote: > Hi, > > > > > > Someone suggested I try the -amd64 kernels which provide 64 bit but > when I try > > > to boot it I get various errors about "this CPU does not support long > > > (something) please use a 32-bit OS" - the 64 bit install CD says the > same > > > message. So I assume these are not 64 bit CPUs. > > > > > > > They almost certainly are. Look at the contents of /proc/cpuinfo. > > > > You are probably using a 32-bit OS. You can't use a lot of memory > > efficiently unless you install a 64-bit OS, regardless of whether it > > has "big memory support". But that's an x86_64 OS, not an AMD64 OS. > > These are not the same architecture. > > > > Er, since he's talking about a 6650, a 6th generation Dell machine, it very > likely *does* have 32-bit CPUs. And he's said it came with the PERC 3/DC > card, which is a very old RAID card. I would hope this machine didn't cost > much, as it's quite old. When you get old like me you won't remember every machine model :-) I didn't think they'd made 32-bit Xeons for a long time. (But I guess that's your point). > Besides that, though, x86_64 is exactly the amd64 architecture. AMD came > up with it, Linux called it amd64, and then when Intel copied it and called > it EM64T, it was renamed in Linux to x86_64 to be more generic. And this one gets me every time. OK, sorry for the wrong advice JW! You can probably sort all this out on your own now. Like I said, /proc/cpuinfo. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about "this CPU does not support long (something) please use a 32-bit OS" - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. They almost certainly are. Look at the contents of /proc/cpuinfo. You are probably using a 32-bit OS. You can't use a lot of memory efficiently unless you install a 64-bit OS, regardless of whether it has "big memory support". But that's an x86_64 OS, not an AMD64 OS. These are not the same architecture. Er, since he's talking about a 6650, a 6th generation Dell machine, it very likely *does* have 32-bit CPUs. And he's said it came with the PERC 3/DC card, which is a very old RAID card. I would hope this machine didn't cost much, as it's quite old. Besides that, though, x86_64 is exactly the amd64 architecture. AMD came up with it, Linux called it amd64, and then when Intel copied it and called it EM64T, it was renamed in Linux to x86_64 to be more generic. The above message is the exact one you get when you try to boot an x86_64 kernel on a 32-bit CPU. Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hi, On Wed, Apr 23, 2008 at 11:07 PM, JW <[EMAIL PROTECTED]> wrote: > Hello, > > We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast > server. > > Specs are: > OS: Linux Debian 4.0/Etch > RAID 5 on 4x U320 15k rpm drives > (uses a perc-raid 3/DC hardware raid controller) > 16GB of RAM > 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it > shows up as 8 processors - maybe it's only HT > > I first made the mistake of using the default kernel, which provides SMP > support but not large memory support. > > I have the output of a mysql sql-bench run from mysql on a Mac Mini to > compare > performance with. > > The server was only 0.35 (relative) the speed of the Mac mini - that means an > 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as > a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those > little "laptop" hard drives, too). > > Needless to say my employer was shocked at the terrible performance and > decided to sell the 6650 right away. > > But I can't help but wonder if there's not something terribly wrong with the > settings - either the OS or mysql settings. > > I changed the kernel to the "-bigmem" kernel. It now sees all the RAM, but > the > sql-bench output on this try was _exactly_ the same: 0.35 > > I copied the my-huge.cnf from the examples directory and changed the > thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). > > I also set the tmpdir, basedir, datadir and language, which were set in the > original my.cnf > > I ran sql-bench again and the performance was even worse this time: 0.36 > > Someone suggested I try the -amd64 kernels which provide 64 bit but when I > try > to boot it I get various errors about "this CPU does not support long > (something) please use a 32-bit OS" - the 64 bit install CD says the same > message. So I assume these are not 64 bit CPUs. They almost certainly are. Look at the contents of /proc/cpuinfo. You are probably using a 32-bit OS. You can't use a lot of memory efficiently unless you install a 64-bit OS, regardless of whether it has "big memory support". But that's an x86_64 OS, not an AMD64 OS. These are not the same architecture. > Any idea how I can configure this server to maximize performace? > > I think the multiple CPUs are a waste: I'm not looking for lots of > concurrency, I want 1 query done really fast. You will be bound by CPU performance on any given single query, yes. But properly tuned, you may get a lot more performance out of this machine. Have you tuned MySQL (key_buffer_size and/or innodb_buffer_pool_size) to use the added memory, for starters? How much data do you even have? If your data all fits in the mac mini's memory and it has a comparable CPU and bus, I wouldn't be surprised to see it keeping up with the Dell fairly well on this benchmark. More to the point: does the benchmark reflect your real-life workload? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Design
On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > Hi All, > > Below is the table design on mysql server. > > CREATE TABLE `coupon_per_course` ( > `coupon_id` int(10) unsigned NOT NULL default '0', > `course_id` int(10) unsigned NOT NULL default '0', > PRIMARY KEY (`coupon_id`,`course_id`), > KEY `idx_coupon_per_course` (`coupon_id`), > KEY `idx_coupon_per_course_1` (`course_id`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > In my view index idx_coupon_per_course should not be there. Since coupon_id > is a primary key. so it will be utilized for searching. > > Before removing index idx_coupon_per_course > mysql> do benchmark(100,(select sql_no_cache ac.plan from > affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' > and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); > Query OK, 0 rows affected (0.06 sec) > > > After removing index idx_coupon_per_course > mysql> do benchmark(100,(select sql_no_cache ac.plan from > affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' > and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); > Query OK, 0 rows affected (0.07 sec) > > I am not able to understand why after removing the index > idx_coupon_per_course, it is taking more time. As it must take less time. > > Some other statistics are > mysql> select count(*) from coupon_per_course; > +--+ > | count(*) | > +--+ > | 296218 | > +--+ > mysql> select count(distinct coupon_id) from coupon_per_course; > +---+ > | count(distinct coupon_id) | > +---+ > |211519 | > +---+ > > Please suggest me the correct table design. > Thanks in advance. > > Thanks, > -- > Krishna Chandra Prajapati > Hi Krishna, I have run into similar issues in the past and have ended up having duplicative indexes. The multi column indexes have higher cardinality and although it should not be an issue, lookup on the first portion of the index alone is not as efficient. I would love to know why this is/what I am dong wrong. Are you having issues with INSERT speed, or the size of the your indexes? Posting your explain (extended) and show index may be helpful. For whatever it is worth, I always suggest explicit joins and using AS: SELECT sql_no_cache ac.plan FROMcoupon_per_course AS cpc INNER JOIN affiliate_coupon AS ac USING(coupon_id) WHERE cpc.course_id = 213336 AND ac.coupon_code='TST0G0' I think it makes queries much easier to read and understand. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
On Thu, 24 Apr 2008 at 10:16 -0400, [EMAIL PROTECTED] confabulated: On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote: IMHO not in this case, cause it is just a simple "WHERE field IN ()" I'm pretty sure that just looks like a bunch of ORs to MySQL. If it didn't use the index with OR, it won't use it with IN. What usually works is to change it to UNION: SELECT wite_what, wite_desc FROM witelist WHERE wite_what = '$oct1' UNION SELECT wite_what, wite_desc FROM witelist WHERE wite_what = '$oct1.$oct2' UNION ...etc. I'm still new to MySQL. The input is greatly appreciated. It took some minor thought and the documentation, but I understand what is going on with the UNION. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little "laptop" hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the "-bigmem" kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about "this CPU does not support long (something) please use a 32-bit OS" - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Php-mssql connection problems on Windows XP
>-Original Message-
>From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
>Sent: Thursday, April 24, 2008 10:01 AM
>To: 'Padiyath Sreekumaran'; mysql@lists.mysql.com
>Subject: RE: Php-mssql connection problems on Windows XP
>
>When using odbc_connect, you don't use the DSN you've previously
>created.
[JS] I should have said you don't NEED to use the DSN. You can, although I
haven't myself done so. Doing in your program insulates you from changes
made to the name of the DSN names, which happens when people get sloppy.
Since you've gotten yours working, I'm not about to argue with success. The
manual notes show a couple of different way of specifying a DSN.
>You use the complete definition of the connection. Here's an example of
>connecting to an MS Access database:
>
> define('ODBC_CONNECT',
> 'DRIVER={Microsoft Access Driver (*.mdb)};
>DBQ=server\\access\\subtable_usa.mdb');
> ...
> $db_access = odbc_connect(ODBC_CONNECT, "", "")
> or exit;
>
>The part between the braces is the same as what you would select when
>defining a DSN.
>
>Regards,
>
>Jerry Schwartz
>The Infoshop by Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>
>www.the-infoshop.com
>www.giiexpress.com
>www.etudes-marche.com
>
>>-Original Message-
>>From: Padiyath Sreekumaran [mailto:[EMAIL PROTECTED]
>>Sent: Thursday, April 24, 2008 5:56 AM
>>To: mysql@lists.mysql.com
>>Subject: Php-mssql connection problems on Windows XP
>>
>>
>>Hello,
>>I can connect to a SQL server in the following way from my windows OS
>>machine:
>>Start --> control Panel --> Administrative tools ---> Data
>>Sources(ODBC)
>>and provide the necessary input. This works for me without problems.
>>
>>I have installed xamp sw on this window machine. The SW version is
>>xampfile-win32.1.6.6a.This includes Apache, MySql and PHP SW.
>>How can I connect to the SQL server with a php script?I want to use
>>the same machine + database as I did in the above case.I have started
>>Apache + Mysql from xamp.The in Internet explorere I gave the following
>>Command: http://localhost/scrpt.php
>>
>>I tried the follwong script from(script.php) :
>>
>>>$dsn="asi_qms";
>>$username="asi_qms_2006";
>>$password="something";
>>$server="xxx";
>>if(!$handle = odbc_connect($dsn, '$username', '$password')) die('Keine
>>Verbindung möglich!');
>>?>
>>
>>I got the following error when I execute the previous script:
>>
>>Warning: odbc_connect() [function.odbc-connect]: SQL error:
>>[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
>>'$username'., SQL state 28000 in SQLConnect in
>>C:\xampplite\htdocs\script.php on line 6
>>Keine Verbindung möglich!
>>
>>What is Iam missing? Any help is appreciated.
>>Please send a copy of the answer in my personal E-mail address also.
>>
>>Thanks in advance.
>>
>>Regards,
>>Kumar
>>
>> --
>>Padiyath Sreekumar | Tel: +41.56.310.3643
>>Paul Scherrer Institut | email: [EMAIL PROTECTED]
>>AIT | Office: WHGA/U132
>>WHGA/U132| Fax: +41.56.310.3649
>>CH-5232 Villigen PSI |
>>Switzerland |
>>-
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>infoshop.com
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote: > IMHO not in this case, cause it is just a simple "WHERE field IN ()" I'm pretty sure that just looks like a bunch of ORs to MySQL. If it didn't use the index with OR, it won't use it with IN. What usually works is to change it to UNION: SELECT wite_what, wite_desc FROM witelist WHERE wite_what = '$oct1' UNION SELECT wite_what, wite_desc FROM witelist WHERE wite_what = '$oct1.$oct2' UNION ...etc. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
On Thu, 24 Apr 2008 at 08:58 +0200, [EMAIL PROTECTED] confabulated:
D Hill schrieb:
I have something I am trying to resolve with an over abundant number of
slow queries. Perhaps it is because of some additional indexes needed. As
soon as I enabled the option 'log_queries_not_using_indexes = 1' in the
configuration file, I started getting messages relating to the select
query:
SELECT wite_what, wite_desc FROM witelist
WHERE
wite_what = '$oct1' OR
wite_what = '$oct1.$oct2' OR
wite_what = '$oct1.$oct2.$oct3' OR
wite_what = '$oct1.$oct2.$oct3.$oct4' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '[EMAIL PROTECTED]' OR
wite_what = '$from_dom' OR
wite_what = '$rcpt_dom';
did you tried (result depending on your MySQL version):
WHERE wite_what IN ('$oct1', '$oct1.$oct2', '$oct1.$oct2.$oct3',
'$oct1.$oct2.$oct3.$oct4', '[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', '$from_dom', '$rcpt_dom');
MySQL version is 5.0.51. Sorry I forgot to mention that. I did change the
query to what you have shown. I'll have to wait till the server comes
under a load to tell. I have noted when the last slow query was logged for
this and will see.
you could also vary with thee index length if wite_what.
Right now the index is for the full length of the field (128). I just ran
a query for the length of wite_what and the maximum length so far is 34.
So, I will cut the index length down to 64.
and what indexes do you have currently exactly?
id - is the primary and has an index type btree
wite_what - is a unique and has an index type of btree
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Php-mssql connection problems on Windows XP
When using odbc_connect, you don't use the DSN you've previously created.
You use the complete definition of the connection. Here's an example of
connecting to an MS Access database:
define('ODBC_CONNECT',
'DRIVER={Microsoft Access Driver (*.mdb)};
DBQ=server\\access\\subtable_usa.mdb');
...
$db_access = odbc_connect(ODBC_CONNECT, "", "")
or exit;
The part between the braces is the same as what you would select when
defining a DSN.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
>-Original Message-
>From: Padiyath Sreekumaran [mailto:[EMAIL PROTECTED]
>Sent: Thursday, April 24, 2008 5:56 AM
>To: mysql@lists.mysql.com
>Subject: Php-mssql connection problems on Windows XP
>
>
>Hello,
>I can connect to a SQL server in the following way from my windows OS
>machine:
>Start --> control Panel --> Administrative tools ---> Data
>Sources(ODBC)
>and provide the necessary input. This works for me without problems.
>
>I have installed xamp sw on this window machine. The SW version is
>xampfile-win32.1.6.6a.This includes Apache, MySql and PHP SW.
>How can I connect to the SQL server with a php script?I want to use
>the same machine + database as I did in the above case.I have started
>Apache + Mysql from xamp.The in Internet explorere I gave the following
>Command: http://localhost/scrpt.php
>
>I tried the follwong script from(script.php) :
>
>$dsn="asi_qms";
>$username="asi_qms_2006";
>$password="something";
>$server="xxx";
>if(!$handle = odbc_connect($dsn, '$username', '$password')) die('Keine
>Verbindung möglich!');
>?>
>
>I got the following error when I execute the previous script:
>
>Warning: odbc_connect() [function.odbc-connect]: SQL error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
>'$username'., SQL state 28000 in SQLConnect in
>C:\xampplite\htdocs\script.php on line 6
>Keine Verbindung möglich!
>
>What is Iam missing? Any help is appreciated.
>Please send a copy of the answer in my personal E-mail address also.
>
>Thanks in advance.
>
>Regards,
>Kumar
>
> --
>Padiyath Sreekumar | Tel: +41.56.310.3643
>Paul Scherrer Institut | email: [EMAIL PROTECTED]
>AIT | Office: WHGA/U132
>WHGA/U132| Fax: +41.56.310.3649
>CH-5232 Villigen PSI |
>Switzerland |
>-
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
% wildcard host permission not working
I set up a user and entered % for the host. I am not able to connect. However, if I change the host value to my FQDN it works fine. Shouldn't the wildcard allow me to connect from any host? Thanks, Adam -- Adam Gerson Assistant Director of Technology Apple Certified System Administrator (ACSA) Columbia Grammar and Prep School phone. 212-749-6200 ex. 321 fax. 212-428-6806 [EMAIL PROTECTED] http://www.cgps.org Public key - subkeys.pgp.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Design
Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql> do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql> do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql> select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql> select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ Please suggest me the correct table design. Thanks in advance. Thanks, -- Krishna Chandra Prajapati
Re: Php-mssql connection problems on Windows XP
Padiyath Sreekumaran schrieb:
Hello Sebastian,
Thanks for your mail. But I donot see any difference in my $username and yours
except
'(").
what surprise, yes, thats it!
you have to use no quotes at all (or doublequotes) around variables, RTMF is
this case the one from PHP
but what has this to do with MySQL???
If the above works I want to use mssql_connect command.
mssql extension for PHP has absolutely nothing to do with MySQL, or?
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Php-mssql connection problems on Windows XP
Padiyath Sreekumaran schrieb: I got the following error when I execute the previous script: Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '$username'., SQL state 28000 in SQLConnect in C:\xampplite\htdocs\script.php on line 6 Keine Verbindung möglich! What is Iam missing? Any help is appreciated. Please send a copy of the answer in my personal E-mail address also. $username instead of '$username' same for '$password' but what has this to do with MySQL??? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Php-mssql connection problems on Windows XP
Hello, I can connect to a SQL server in the following way from my windows OS machine: Start --> control Panel --> Administrative tools ---> Data Sources(ODBC) and provide the necessary input. This works for me without problems. I have installed xamp sw on this window machine. The SW version is xampfile-win32.1.6.6a.This includes Apache, MySql and PHP SW. How can I connect to the SQL server with a php script?I want to use the same machine + database as I did in the above case.I have started Apache + Mysql from xamp.The in Internet explorere I gave the following Command: http://localhost/scrpt.php I tried the follwong script from(script.php) : I got the following error when I execute the previous script: Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '$username'., SQL state 28000 in SQLConnect in C:\xampplite\htdocs\script.php on line 6 Keine Verbindung möglich! What is Iam missing? Any help is appreciated. Please send a copy of the answer in my personal E-mail address also. Thanks in advance. Regards, Kumar -- Padiyath Sreekumar | Tel: +41.56.310.3643 Paul Scherrer Institut | email: [EMAIL PROTECTED] AIT | Office: WHGA/U132 WHGA/U132| Fax: +41.56.310.3649 CH-5232 Villigen PSI | Switzerland | - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Symlink InnoDB tables without stopping MySQL
Hi, I guessed it was something like it and that is why I wanted to make sure how it should be done. Using the ALTER TABLE table DISCARD TABLESPACE doesn't seem to work as expected - I succeeded to crash the test server twice. See the mysql log details below. What I did was the following: - create table - check INNODDB status and copy the table.ibd to a new location - run ALTER TABLE table DISCARD TABLESPACE - symlink the table.ibd copy within the database folder - run ALTER TABLE table IMPORT TABLESPACE - run show table status like 'table'; And here are the crash details from the log. InnoDB: buf pool start is at 0x3666c000, end at 0xb366c000 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, InnoDB: you can look from section 6.1 at http://www.innodb.com/ibman.html InnoDB: how to force recovery. 080424 4:31:55InnoDB: Assertion failure in thread 68795312 in file ./../include/buf0buf.ic line 262 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. InnoDB: Thread 73976752 stopped in file ./../include/sync0sync.ic line 111 InnoDB: Thread 729131952 stopped in file sync0arr.c line 336 InnoDB: Thread 150207408 stopped in file sync0arr.c line 336 InnoDB: Thread 747498416 stopped in file sync0arr.c line 336 InnoDB: Thread 63421360 stopped in file ./../include/sync0sync.ic line 111 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576000 read_buffer_size=507904 max_used_connections=601 max_connections=600 threads_connected=394 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x2c24e950 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x4197e0c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8136da4 0x438898 (nil) 0x8299f88 0x829a024 0x81c2f5b 0x81d6f60 0x814a563 0x814e66c 0x814f08a 0x814f8e5 0x8150330 0x432371 0x38cffe New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0xbb66460 = show table status like 'temp%' thd->thread_id=2545123 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 080424 04:31:56 mysqld restarted 080424 4:31:57 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. Thanks for your help Dobromir Velev On Wednesday 23 April 2008 22:05, Jerry Schwartz wrote: > If Linux works the same way as HP-UX (and it should), anything you do to an > open file (including deleting it) has no effect until the file is closed. > The MySQL server is still using the "old" file. The next time it stops and > restarts, it will follow the symlink. I don't know what the effect of > accessing a "stale" copy of the file will do. > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > www.giiexpress.com > www.etudes-marche.com > > >-Original Message- > >From: Sebastian Mendel [mailto:[EMAIL PROTECTED] > >Sent: Wednesday, April 23, 2008 9:27 AM > >To: Dobromir Velev > >Cc: mysql@lists.mysql.com > >Subject: Re: Symlink InnoDB tables without stoping MySQL > > > >Dobromir Velev schrieb: > >> Hi, > >> What I'm trying to do is to create a new InnoDB table on a different > > > >disk and > > > >> symlink it to an existing database. > >> I have innodb_file_per_table turned on and here is how I tried to do > > > >it > > > >> mysql> \u test > >> mysql> create table test (...) ENGINE = 'InnoDB'; > >> mysql>\q > >> > >> move the test.ibd file to the other disk > >> create a sim
Re: Starting a 2nd MySQL instance on UNIX
Mark, When you try to log-in to the new instance, are you specifying the new port number to the client? If you don't give it the new port number, then it will connect to the default port, which is presumably your 4.0.20 instance. Mark-E wrote: I have a Solaris box where MySQL 4.0.20 instance is running (to support Bugzilla 2.22). I have loaded mysql5.0 on the same box (for Bugzilla 3.0.3) and created a new mysql50 user that I want to use to run this instance with. I tried to start the instance on another port by running the following command... ./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.0/my.cnf --socket=/tmp/mysql50/mysql.sock --port=3307 --basedir=/usr/local/mysql-5.0 --datadir=/usr/local/mysql-5.0/data --pid-file=/usr/local/mysql-5.0/mysql50.pid --user=mysql50 The instance appears to start but the message "Starting the instance" comes up and I never get back to the system prompt. it just sits there. If I open another terminal window and do a ps -ef | grep mysql, I can see the new processes running. There is nothing in the error log. I ran the mysql_install_db.sh script to create the mysql database however, I cannot log in. I thought that it creates a root user with no password. I tired logging in as root with no password bu no luck. If i use the mysql 4.0.20 root user password, I get into the 4.0.20 instance even though the mysql50 user does not have mysql 4.0.20 in it's path. when I run mysql at the prompt, how would I differentiate between the 2 instances? So at this point I am stuck. If anyone out can help guide me on what I need to do to ge tthe instance up and running properly, I would appreciate it. I am rather new to MySQL and I have read through the docs but things are still not very clear. Thanks! Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cluster/Cluster Carrier Grade Changelogs Have Moved
Hi, The changelogs for MySQL Cluster have been consolidated and can now be found here: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news.html They're now arranged by NDB version number: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news-6-3.html http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news-6-2.html http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news-6-1.html Changelogs for individual releases can be found by converting all the "." characters in the version string to "-" characters, appending this to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news- and tacking on ".html" to the end. This sounds complicated, so here's an example: The changelog for MySQL 5.1.23-ndb-6.2.14 can be found at http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-news-5-1-23-ndb-6-2-14.html cheers jon. -- Jon Stephens - [EMAIL PROTECTED] Technical Writer - MySQL Documentation Team Sun Microsystems AB - Database Technology Group Liljeholmen, Stockholm, Sweden (GMT +01.00) Mobile: +46 (0) 736 773 993 Skype: plastic-fish MySQL: www.mysql.com Sun: www.sun.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
