RE: Storing huge amount of binary data
Sometimes, the easiest way to do this is to use the file system of the linux machine to store the files, and make reference to them in the DB...storing not data in the DB and getting rid of all your possible problems. Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -Original Message- From: Cabbar Duzayak [mailto:[EMAIL PROTECTED] Sent: Saturday, July 09, 2005 12:01 AM To: mysql@lists.mysql.com Subject: Storing huge amount of binary data Hi Everyone, I will be starting a project for which I will need to store about 1 million image files all of which are about 50-100K in size. I will be using Linux for this project. The database won't be very busy, there will be batch image uploads 1-2 times a day, and around 100-200 concurrent users at most, most of which will be reading from the db and writing only session information type of data, etc... And, I don't really need transaction support (InnoDB)... Adding this up, the image data size will be around 50-100 Gb, and I will need to store a total of 1-2 Gb text information (1K for each image) along with each of these images... First of all, I heard that Mysql does not perform very well when tablesize goes above 1 Gb. Is this a myth? Image table is not a big deal, since I can partition/distribute it to ~ 100-200 tables, i.e. by table_(id % 100). However, text information needs to stay in a single table (since I need to do queries on it for information) and there will be multiple indexes over this information. And, as you can imagine, I am not sure if mysql can handle something like this, and was wondering if you can provide some feedback. So my questions are: 1. The main question is, do you guys have any experience with this much binary and regular data? Do you think Mysql can handle this much data in a reliable manner (without corrupting data and/or degrading/terrible performance) ? 2. Can I implement this using regular SCSI disks with regular mysql? Or do I have need advanced solutions such as clustered, replicated, etc? 3. Again, as you can understand, I want to minimize the cost here. If you don't think I can use mysql, do you think Microsoft SQL server is good enough for this task? Thanks... -- 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]
Some query help
Hi Again - I need some more help with a query. I have a list of numbers (bandwidth required)... 2200, 2200, 2200, 400, 320 My data looks like this: Bandwidth | Distance 2250 | 10km 1125 | 10km 622 | 10km 2250 | 20km 1125 | 20km 622 | 20km 2250 | 40km 1125 | 40km 622 | 40km I need the query to look at the list of numbers, and figure out that a certain distance has Bandwidths that are greater than each of the numbers. SELECT DISTINCT(distance) FROM fiber_config WHERE fiber_type = 2 AND bandwidth > (2200, 2200, 2200, 220) ORDER BY distance ASC So I hope you can see what I am trying to get after. Basically I need to fins a distance that can fit each of the numbers in the list. So if 2200 is in the list, 2250 works. If 400 is in the list, 622 works. Thanks for the help on this! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks for the help
Thanks for the help, I'll give some of these examples a try~!!! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to SELECT something (CONCAT) and search the field
Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Field property question!
Yes I do enjoy every day in which the manual makes my life easier. I guess I
didn't work my question specifically enough however. I was actually looking
for a way to 'ALTER' the table so that my State field data is always upper.
In MSSQL you can apply a function to a field, which will then run that
function over the data each time something new gets added. For instance you
could put in Now() in the default, and the default date would appear if you
added a row That's the idea.
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 10, 2005 10:58 AM
To: Matt Babineau; mysql@lists.mysql.com
Subject: RE: Field property question!
[snip]
Can I setup a table so that no matter how data is entered into it (web
form,
command line) The data in one of the columns ALWAYS gets converted to
uppercase? I remeber MSSQL had this feature of being able to apply a
function to a field in its configuration.
[/snip]
The manual, it is amazing no?
http://dev.mysql.com/doc/mysql/en/string-functions.html
UPPER()
INSERT INTO `table` (`colFoo`)
VALUES (UPPER('myData'));
--
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]
Field property question!
Can I setup a table so that no matter how data is entered into it (web form, command line) The data in one of the columns ALWAYS gets converted to uppercase? I remeber MSSQL had this feature of being able to apply a function to a field in its configuration. Thanks! Matt
RE: using between
Have you considered just doing a parse on the the IP ranges and having 8 columns in your database, then write your query to work inside the 8 columns Cols = from_zone, from_net, from_subnet, from_node, to_zone, to_net, to_subnet, to_node Then just parse the ip you are looking up and write your query that way. MySQL should beable to reduce the amount of rows it needs to look at pretty quickly this way. Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 1:05 PM To: 'mos'; 'MySQL list' Subject: RE: using between Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: >Hello > >Is there a way when searching for a range of values for a particular >field that mysql would not have to look at the entire table ... I'm >guessing with some type of composite key or something? ... > >e.g. > >SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; > >Field1 and field2 are indexed but that doesn't help because you're >looking for a range of values instead of a particular value > Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog. Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use "Explain" and you'll see the index that it's using. Mike -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
I do see that the bug was closed over 2 years ago - I guess then it was a persistent problem, but now it is limited to the configuration of the MySQL Server. As I have found out today, Quadrupling the stock memory limits on the MySQL Server solved the problem...not sure what will happen when the database starts growing but, my query is solid, and I am only returning a few rows at a time, so hopefully over a couple hundred queries wont make this thing die! :) Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 3:55 PM To: Michael Dykman Cc: Matt Babineau; 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS I'm confused. That bug was closed over 2 years ago. What makes you think it applies here, and why do you say it is still "unresolved"? Michael Michael Dykman wrote: > Matt, > > If you go through the various build options tried in that bug report, > you will see that it is a subtle bug and apparently still unresolved. > I couldn't tell you with any degree of confidence will fix it. The > bug report does show a couple of configure options for PHP which seem > to elimiate it, without shedding much light on the underlaying cause. > > Possible conflict in zlib versions or some other common library > between PHP and MySQL build.. I would closely examine the dependant libraries > via $ ldd... Hard to tell without knowing the OS, the specific > software builds and the various library versions especially any shared > ones.. > > If you have a test system you can use, you might consider > experimentally building both PHP and MySQL from source and explicitly > set them to use the same versions of any shared libraries? Might be a > bit of overkill... > > - michael dykman > > > On Tue, 2005-02-01 at 15:51, Matt Babineau wrote: > >>Weird thing is that I am running PHP 4.3.9I guess I can upgrade >>and see what happens? >> >> >>Matt Babineau >>Criticalcode >>w: http://www.criticalcode.com >>p: 858.733.0160 >>e: [EMAIL PROTECTED] >> >>-Original Message- >>From: Michael Dykman [mailto:[EMAIL PROTECTED] >>Sent: Tuesday, February 01, 2005 12:47 PM >>To: Matt Babineau >>Cc: 'MySQL General' >>Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS >> >>Matt, >> >> I suspect your problem is PHP, not MySQL. refer to >> >>http://bugs.php.net/bug.php?id=16906&edit=1 >> >>On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: >> >>>Hi All- >>> >>>I'm running a query that uses SQL_CALC_FOUND_ROWS for my search >>>engine on a real estate site. The problem is that I get an error when >>>I run my >> >>query: >> >>>"Warning mysql_query(): Unable to save result set in /clients/search.php" >>> >>>My Query is: >>> >>>SELECT SQL_CALC_FOUND_ROWS propertyData.*, >>>propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN >>>propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID >>>WHERE state = 'CA' limit 0, 5 >>> >>>Very odd that this happens, I am running MySQL 4.1.9 >>> >>>Thanks, >>> >>>Matt Babineau >>>Criticalcode >>>w: http://www.criticalcode.com >>>p: 858.733.0160 >>>e: [EMAIL PROTECTED] >> >>-- >> - michael dykman >> - [EMAIL PROTECTED] -- 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: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This is very strange behavior! Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Matt Babineau [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:52 PM To: 'Michael Dykman' Cc: 'MySQL General' Subject: RE: Problem with SELECT SQL_CALC_FOUND_ROWS Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906&edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: > Hi All- > > I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine > on a real estate site. The problem is that I get an error when I run > my query: > > "Warning mysql_query(): Unable to save result set in /clients/search.php" > > My Query is: > > SELECT SQL_CALC_FOUND_ROWS propertyData.*, > propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN > propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID > WHERE state = 'CA' limit 0, 5 > > Very odd that this happens, I am running MySQL 4.1.9 > > Thanks, > > Matt Babineau > Criticalcode > w: http://www.criticalcode.com > p: 858.733.0160 > e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- 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: Problem with SELECT SQL_CALC_FOUND_ROWS
Weird thing is that I am running PHP 4.3.9I guess I can upgrade and see what happens? Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED] -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:47 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS Matt, I suspect your problem is PHP, not MySQL. refer to http://bugs.php.net/bug.php?id=16906&edit=1 On Tue, 2005-02-01 at 15:20, Matt Babineau wrote: > Hi All- > > I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine > on a real estate site. The problem is that I get an error when I run my query: > > "Warning mysql_query(): Unable to save result set in /clients/search.php" > > My Query is: > > SELECT SQL_CALC_FOUND_ROWS propertyData.*, > propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN > propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID > WHERE state = 'CA' limit 0, 5 > > Very odd that this happens, I am running MySQL 4.1.9 > > Thanks, > > Matt Babineau > Criticalcode > w: http://www.criticalcode.com > p: 858.733.0160 > e: [EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SELECT SQL_CALC_FOUND_ROWS
Ok here is the code chunk: $rows = is_numeric($_GET['rows']) ? $_GET['rows'] : 5; $stRow = 0; // SEARCH CODE $sql = "SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = '$state' limit $stRow, $rows"; $search = mysql_query($sql); echo $sql; $sql = "SELECT FOUND_ROWS()"; $ctTotalResults = mysql_fetch_row(mysql_query($sql)); It errors out on the first $search = mysql_query(); statement. Matt Babineau Criticalcode w: http://www.criticalcode.com <http://www.criticalcode.com/> p: 858.733.0160 e: [EMAIL PROTECTED] _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 01, 2005 12:25 PM To: Matt Babineau Cc: 'MySQL General' Subject: Re: Problem with SELECT SQL_CALC_FOUND_ROWS "Matt Babineau" <[EMAIL PROTECTED]> wrote on 02/01/2005 03:20:49 PM: > Hi All- > > I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a > real estate site. The problem is that I get an error when I run my query: > > "Warning mysql_query(): Unable to save result set in /clients/search.php" > > My Query is: > > SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc > FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = > propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 > > Very odd that this happens, I am running MySQL 4.1.9 > > Thanks, > > Matt Babineau > Criticalcode > w: http://www.criticalcode.com > p: 858.733.0160 > e: [EMAIL PROTECTED] > I am by not a PHP expert but it seems that your error message is coming from PHP and not from MySQL (based on the name of the function in the message). Please post the code that surrounds this statement and try to determine and indicate which line is throwing the error, please. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Problem with SELECT SQL_CALC_FOUND_ROWS
Hi All- I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on a real estate site. The problem is that I get an error when I run my query: "Warning mysql_query(): Unable to save result set in /clients/search.php" My Query is: SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id = propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5 Very odd that this happens, I am running MySQL 4.1.9 Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Re: Mysql + PHP -> Search
I just answered my own question actually! - snip from php.net - MySQL 4.0 supports a fabulous new feature that allows you to get the number of rows that would have been returned if the query did not have a LIMIT clause. To use it, you need to add SQL_CALC_FOUND_ROWS to the query, e.g. $sql = "Select SQL_CALC_FOUND_ROWS * from table where state='CA' limit 50"; $result = mysql_query($sql); $sql = "Select FOUND_ROWS()"; $count_result = mysql_query($sql); You now have the total number of rows in table that match the criteria. This is great for knowing the total number of records when browsing through a list. ------ Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
MySQL + PHP -> Search Engine question!
Hi All - I'm building a search engine and what I would like to do is run a search and get the number of results, but still use the LIMIT command so I am not returning a ton of rows all at once. Is this the best way to go about searching? Thanks, Matt Babineau Criticalcode w: http://www.criticalcode.com p: 858.733.0160 e: [EMAIL PROTECTED]
Mysql 4.1 and the LIMIT sql statement
Hi all- Has anyone run into problems with this sql syntax? LIMIT -1 I've used this extensively in my code to get back all records rather then specifing a limit. I've done this programmatically with PHP, so all my queries have a limit even if I don't need one, I just have it specify LIMIT -1, but apparently this functionality doesn't seem to work in 4.1??? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
RE: Trying to dump from GUI client
I'm not sure what the answer here is but check your user's permissions on the database to make sure it has the necessary items GRANTED to it. Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eve Atley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 12:14 PM To: 'Adam'; 'MySQL General' Subject: RE: Trying to dump from GUI client I am trying MySQL Query Browser, but I get this error... SELECT * INTO OUTFILE 'c:\temp\candidate.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM candidate; ERROR 1045: Access denied for user: 'wowdba'@'%' (Using password: YES) ...even though I am logged in via the client. -Original Message- From: Adam [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 16, 2004 7:04 PM To: [EMAIL PROTECTED]; MySQL General Subject: Re: Trying to dump from GUI client Eve, >From the command line you can use `mysql`, command line tool that ships >with MySQL. You would want to use the SELECT ... INTO OUTFILE 'file.txt' FROM ...; See the MySQL manual for more information on this: - http://dev.mysql.com/doc/mysql/en/SELECT.html You can use another MySQL client. Such as Toad for MySQL or MySQL query browser - see URLs below. Toad for MySQL - http://www.toadsoft.com/toadmysql/toad_mysql.htm MySQL Query Browser: - http://dev.mysql.com/downloads/query-browser/index.html Both of these tools will allow you to export a record set as a comma delimited file. Good luck! Regards, Adam Eve Atley> > > I'm not sure how best to proceed in dumping data from 1 database and > getting a copy of the export, in order to transfer it to another > server. I usually use phpmyadmin to do an export, which nicely creates > a .zip file of everything. I managed to get it connected with Mysql > Control Center, but am not sure how to dump from this. So I figure I > can: > > A. use a command line (in which case, what commnands should I use to > dump and export to a file), Or > B. try to get phpmyadmin to connect (as I'm uncertain how to edit the config > file for this), > Or > C. learn how to dump from MySql CC (how? I saw no way of handling this from > MySQL CC), > Or > D. use another GUI client (which one?). > > The server in question is mysql.loosefoot.com. Oddly, it was > connecting fine until my company decided to move to a new server, and > suddenly, it throws an error that "Connection to database failed: > Unknown MySQL Server Host 'mysql.loosefoot.com' (0)". I've changed > *nothing* in my connect script, and as mentioned, I can connect to the > database via other means. > > What would you suggest as the least painful solution? > > Thanks, > Eve > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > Regards, Adam -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.2 is topping out at 1024 threads!
FIXED! Ok Thanks to Eric on this one, the wait_timeout configuration was what fixed my sleepy connection problems! Thanks ERIC! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Eric Gunnett [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:37 AM To: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! wait_timeout Will set the maximum amout of time a thread will be in the Sleep state before MySQL drops it. Eric Gunnett System Administrator Zoovy, Inc. [EMAIL PROTECTED] >>> "Matt Babineau" <[EMAIL PROTECTED]> 11/17/04 11:35AM >>> Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: > From: > Matt Babineau > <[EMAIL PROTECTED]> >To: > [EMAIL PROTECTED] > Subject: > RE: MySQL 4.0.2 is topping out at > 1024 threads! > Date: > Wed, 17 Nov 2004 11:18:04 -0800 > (16:18 CLST) > > This is a very strange problem. As you can see there isn't a lot going > on, under a million queries. No problem right? This is a dual cpu 2.8 > Ghz server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > Connection id: 25394 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.20-standard-log > Protocol version: 10 > Connection: 63.12.130.192 via TCP/IP > Client characterset:latin1 > Server characterset:latin1 > TCP port: 3306 > Uptime: 19 hours 40 min 2 sec > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush > tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- > > mysql> > > > == > > # Example mysql config file for very large systems. > # > # This is for large system with memory of 1G-2G where the system runs > mainly # MySQL. > # > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this # > installation this directory is /var/lib/mysql) or # ~/.my.cnf to set > user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients [client] > #password = your_password > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > connect_timeout = 10 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 > max_connections = 1024 max_user_connections = 1024 > > # Don't listen on a TCP/IP port at all. This can be a security > enhancement, # if all processes that need to connect to mysqld run on > the same host. > # All interaction with mysqld must be made via Unix sockets or named > pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! >
RE: MySQL 4.0.2 is topping out at 1024 threads!
Ok, this is making a bit more sense now, I took a look at show processlist and this is what I found: | 25687 | inetusr | 62.13.102.133:48206 | protected | Sleep | 454 | | NULL The screen scrolls up with these sleeping connections, any way I can get these guys dumped if they've been sleeping too long? I already have a connection_timeout in the my.cnfis there another option? Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Alvaro Avello [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 11:28 AM To: Matt Babineau Cc: [EMAIL PROTECTED] Subject: RE: MySQL 4.0.2 is topping out at 1024 threads! threads or connections ? if the problem is about connection maybe change the parameter in your my.cnf : max_connections = 1024 to a higher value ... Hope this helps... Saludos / Regards, Alvaro. On Wed, 2004-11-17 at 11:18 -0800, Matt Babineau wrote: > From: > Matt Babineau > <[EMAIL PROTECTED]> >To: > [EMAIL PROTECTED] > Subject: > RE: MySQL 4.0.2 is topping out at > 1024 threads! > Date: > Wed, 17 Nov 2004 11:18:04 -0800 > (16:18 CLST) > > This is a very strange problem. As you can see there isn't a lot going > on, under a million queries. No problem right? This is a dual cpu 2.8 > Ghz server. Ok Great. I am also including my.cnf so you can see my > configuration. > > Here is some more info on the problem I am experiencing: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > Connection id: 25394 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.20-standard-log > Protocol version: 10 > Connection: 63.12.130.192 via TCP/IP > Client characterset:latin1 > Server characterset:latin1 > TCP port: 3306 > Uptime: 19 hours 40 min 2 sec > > Threads: 1023 Questions: 781971 Slow queries: 0 Opens: 33 Flush > tables: > 1 Open tables: 27 Queries per second avg: 11.044 > -- > > mysql> > > > == > > # Example mysql config file for very large systems. > # > # This is for large system with memory of 1G-2G where the system runs > mainly # MySQL. > # > # You can copy this file to > # /etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this # > installation this directory is /var/lib/mysql) or # ~/.my.cnf to set > user-specific options. > # > # One can in this file use all long options that the program supports. > # If you want to know which options a program support, run the program > # with --help option. > > # The following options will be passed to all MySQL clients [client] > #password = your_password > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > connect_timeout = 10 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 > max_connections = 1024 max_user_connections = 1024 > > # Don't listen on a TCP/IP port at all. This can be a security > enhancement, # if all processes that need to connect to mysqld run on > the same host. > # All interaction with mysqld must be made via Unix sockets or named > pipes. > # Note that using this option without enabling named pipes on Windows > # (via the "enable-named-pipe" option) will render mysqld useless! > # > #skip-networking > > # Replication Master Server (default) > # binary logging is required for replication log-bin > > # required unique id between 1 and 2^32 - 1 # defaults to 1 if > master-host is not set # but will not function as a master if omitted > server-id = 1 > > # Replication Slave (comment out master section to use this) # # To > configure this host as a replication slave, you can choose between # > two methods : > # > # 1) Use the CHANGE MASTER TO command (fully described in our manual) > - > #the syntax is: > # > #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, > #MASTER_USER=, MASTER_PASSWORD
RE: MySQL 4.0.2 is topping out at 1024 threads!
he_size = 384M #bdb_max_lock = 10 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Thanks All! Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 8:26 AM To: Gleb Paharenko Cc: [EMAIL PROTECTED] Subject: Re: MySQL 4.0.2 is topping out at 1024 threads! In the last episode (Nov 17), Gleb Paharenko said: > > Help! I can't figure out a way to stop my server from topping out at > > 1024 threads. This is a very strange behavoir. I have tons of legit > > use on my database server but I don't think the threads are dying > > does anyone have any suggestions for this? > > Similar problems are often found in lists. Usually they are solved by > increasing file limits. You likely need to increase open-files-limit. If you're running Linux, you may need to recompile your linuxthreads library also: http://dev.mysql.com/doc/mysql/en/Source_notes-Linux.html -- Dan Nelson [EMAIL PROTECTED] -- 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]
MySQL 4.0.2 is topping out at 1024 threads!
Help! I can't figure out a way to stop my server from topping out at 1024 threads. This is a very strange behavoir. I have tons of legit use on my database server but I don't think the threads are dying does anyone have any suggestions for this? Thanks, Matt Babineau Web Developer Criticalcode - http://www.criticalcode.com
RE: Query to emulate what mysqldump does
On Wed, 2003-12-03 at 15:22, Jay Blanchard wrote: > [snip] > I thought about that Jay, but the mysql server is not on the webserver > machine. Any other suggestions? > [/snip] > > phpmyadmin will allow you to connect to the remote MySQL server and do > dumps What if I don't have phpmyadmin available? :) What I am trying to do, it setup a simple script to pull down essentially a backup of their database and write it to a file on my development machine so when they mess up their data (..and I said WHEN) I can be a hero and revert them to the last good backup before they "didn't touch a thing". Thx- M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query to emulate what mysqldump does
On Wed, 2003-12-03 at 15:13, Jay Blanchard wrote:
> [snip]
> Are there any query equivalencies to mysqldump? I am looking for a way
> to get a complete database dump via php and I don't have access to the
> system CLI to run mysql dump.
> [/snip]
>
> Run mysqldupmp in a php file using exec...see
>
> http://www.php.net/exec
> http://www.mysql.com/mysqldump
>
> looks like
>
> $foo = exec("mysqldump database [options]");
I thought about that Jay, but the mysql server is not on the webserver
machine. Any other suggestions?
-Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to emulate what mysqldump does
Hey All- Are there any query equivalencies to mysqldump? I am looking for a way to get a complete database dump via php and I don't have access to the system CLI to run mysql dump. TIA- Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql just stopped working on my server - any way to track down the problem?
I guess you could say that. The machine is Redhat 9 too. I logged in locally and connected to the mysql server fine. Everything had appeared as normal. I could see all the databases, and \u to them. Is there a way I can turn on some extended logging to get more info on the problem as it will probably arise again in the future? Thx- Matt On Tue, 2003-11-11 at 21:35, Matt W wrote: > Hi Matt, > > So one database "disappeared" all of a sudden while the others are OK? > Is MySQL running on FreeBSD? If so, that'll be the problem :-) and we'll > point you toward the fix. > > > Matt > > > - Original Message - > From: "Matt Babineau" > Sent: Tuesday, November 11, 2003 3:28 PM > Subject: RE: Mysql just stopped working on my server - any way to track > down the problem? > > > > Its not a startup problem, that works fine. What happened was my php > > application stopped working. This server has multiple databases on it > > mind you. The other databases were working fine. > > > > So one database stopped working and the application could not connect > to > > it. so I did a 'service mysql restart' and it started working again > like > > magic. I looked in the .err file and it only shows when the mysql > server > > stops and starts. I can't see the file on this machine, but it just > > seems to be logging starts and stops. > > > > Thanks for the reply hope this helps- > > > > Matt > > > > On Tue, 2003-11-11 at 19:20, Dathan Vance Pattishall wrote: > > > Can you post what the *.err file said? A key/value in /etc/my.cnf > might > > > be causing a startup problem. > > > > > > > > > - Dathan Vance Pattishall > > > - Sr. Programmer and mySQL DBA for FriendFinder Inc. > > > - http://friendfinder.com/go/p40688 > > > > > > > > > -->-Original Message- > > > -->From: Matt Babineau [mailto:[EMAIL PROTECTED] > > > -->Sent: Tuesday, November 11, 2003 1:16 PM > > > -->To: [EMAIL PROTECTED] > > > -->Subject: Mysql just stopped working on my server - any way to > track > > > down > > > -->the problem? > > > --> > > > -->Hi All- > > > --> > > > -->I am running 4.0.15-standard on RH9. My mysql database just > stopped > > > -->working, is there a way I can log information about why it stops > like > > > -->this? the *.err was unhelpful. > > > --> > > > -->Any help here is appreciated. Thanks > > > --> > > > -->-Matt > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql just stopped working on my server - any way to track down the problem?
Its not a startup problem, that works fine. What happened was my php application stopped working. This server has multiple databases on it mind you. The other databases were working fine. So one database stopped working and the application could not connect to it. so I did a 'service mysql restart' and it started working again like magic. I looked in the .err file and it only shows when the mysql server stops and starts. I can't see the file on this machine, but it just seems to be logging starts and stops. Thanks for the reply hope this helps- Matt On Tue, 2003-11-11 at 19:20, Dathan Vance Pattishall wrote: > Can you post what the *.err file said? A key/value in /etc/my.cnf might > be causing a startup problem. > > > - Dathan Vance Pattishall > - Sr. Programmer and mySQL DBA for FriendFinder Inc. > - http://friendfinder.com/go/p40688 > > > -->-----Original Message- > -->From: Matt Babineau [mailto:[EMAIL PROTECTED] > -->Sent: Tuesday, November 11, 2003 1:16 PM > -->To: [EMAIL PROTECTED] > -->Subject: Mysql just stopped working on my server - any way to track > down > -->the problem? > --> > -->Hi All- > --> > -->I am running 4.0.15-standard on RH9. My mysql database just stopped > -->working, is there a way I can log information about why it stops like > -->this? the *.err was unhelpful. > --> > -->Any help here is appreciated. Thanks > --> > -->-Matt > --> > --> > -->-- > -->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]
Mysql just stopped working on my server - any way to track down the problem?
Hi All- I am running 4.0.15-standard on RH9. My mysql database just stopped working, is there a way I can log information about why it stops like this? the *.err was unhelpful. Any help here is appreciated. Thanks -Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows to Linux
Well, he could do that but being inexperienced with Linux, I figured it would be more beneficial to use a familiar WYSIWYG so he doesn't blow and hour playing the with CLI like I did :) On Tue, 2003-11-04 at 16:58, Big Brother wrote: > err why not do a mysqldump then just import that? > > --- > > > Quoting Matt Babineau <[EMAIL PROTECTED]>: > > > Check out SQLYog, could can connect and copy databases...pretty much > > like MSSQL Enterprise manager. They have a trial version on their site: > > http://www.webyog.com/sqlyog > > > > > > > > On Mon, 2003-11-03 at 19:51, Matt Fletcher wrote: > > > Hi there, > > > I have taken the plunge and dropped windows in favour of linux. My > > > question is what is the best way to get the data from my windows mysql > > > databases into linux? Can I just copy some files from one partition to > > > another or what? > > > > > > Thanks, > > > > > > Matt > > > > > > -- > > 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: Windows to Linux
Check out SQLYog, could can connect and copy databases...pretty much like MSSQL Enterprise manager. They have a trial version on their site: http://www.webyog.com/sqlyog On Mon, 2003-11-03 at 19:51, Matt Fletcher wrote: > Hi there, > I have taken the plunge and dropped windows in favour of linux. My > question is what is the best way to get the data from my windows mysql > databases into linux? Can I just copy some files from one partition to > another or what? > > Thanks, > > Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT problem
Have you tried: GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @'%' IDENTIFIED BY ''; FLUSH PRIVILEGES; On Thu, 2003-10-23 at 21:03, Nathaniel Mallet wrote: > I was having the same problems trying to get a user added to a new DB, so > here's are the commands I used: > > GRANT INSERT, DELETE, UPDATE, SELECT ON *.* TO @localhost IDENTIFIED > BY ''; > FLUSH PRIVILEGES; > > where and are whatever you need it to be. > > I've never had much luck with % as host, so specify localhost for you user, > and you should be all set. > > Nat > > -Original Message- > From: Rory McKinley [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 23, 2003 6:21 AM > To: Datatal AB - Gauffin, Jonas > Cc: [EMAIL PROTECTED] > Subject: Re: GRANT problem > > > Ok. > > I'm stumped. If I'm reading the manual correctly, you were right about not > hving to specify the host..the only difference between your version and the > manual is that they use single quotes around the user name but I can't see > how that makes any difference. I normally just specfiy hosts because using > wildcards for hosts makes me nervous - hey, even paranoid people have > enemies :) > > If anyone knows the answer to this I would be interested in knowing what it > is too > > Sorry I can't be of more help > > Rory McKinley > Nebula Solutions > +27 82 857 2391 > [EMAIL PROTECTED] > "There are 10 kinds of people in this world, > those who understand binary and those who don't" (Unknown) > - Original Message - > From: "Datatal AB - Gauffin, Jonas" <[EMAIL PROTECTED]> > To: "Rory McKinley" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, October 23, 2003 12:05 PM > Subject: SV: GRANT problem > > > it works if I specify the host. Why doesn't % work as host? > > > -Ursprungligt meddelande- > > Från: Rory McKinley [mailto:[EMAIL PROTECTED] > > Skickat: den 23 oktober 2003 11:56 > > Till: Datatal AB - Gauffin, Jonas > > Ämne: Re: GRANT problem > > > > > > thinking. > > > > In one of your follow up posts I see that you have the > > command as this : > > > > C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -pMYPASSWORD > > > > Try > > > > C:\mysql\bin\mysql phonewatch -h ts2test -u datatal -p > > > > And enter password at the prompt > > > > > > Rory McKinley > > Nebula Solutions > > +27 82 857 2391 > > [EMAIL PROTECTED] > > "There are 10 kinds of people in this world, > > those who understand binary and those who don't" (Unknown) > > - Original Message - > > From: "Datatal AB - Gauffin, Jonas" <[EMAIL PROTECTED]> > > To: "Rory McKinley" <[EMAIL PROTECTED]> > > Sent: Thursday, October 23, 2003 11:45 AM > > Subject: SV: GRANT problem > > > > > > > Not sure if this will help - in your GRANT statement do you > > > not need to specify a host for the user e.g. GRANT.. to > > > datatal @ your_host_name.?. > > > > > > > > GRANT uses % as host if none is specified. > > % = all hosts. > > > > any other ideas? > > > > > > > > > > > > -- > 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: Improving Query speed - any suggestions?
Thanks everyone for your input, I'll try the ramdisk idea, I read about
someone else who tried that and had some success. Beyond, that I'm gonna
take the long route and redesign the database to be a bit more
conventional.
Thanks!
Matt
On Thu, 2003-10-23 at 20:28, Peter Buri wrote:
> Hello,
>
> as i see you use one table to store all the data, but the cohesive data are
> split into 15! different rows.
>
> I think to get the best performance you shoud redesign your tabel.
> Use at last first normal form [1NF], if the app_id is uniq this can be the
> primary key [which will speed up the query] .
>
> Data which have the same row_id should be in one row.
>
> Your table definition shoud look like this:
>
> create table content (
> app_id MEDIUMINT NOT NULL AUTO_INCREMENT,
> niche??? , -- maybe int
> type int,
> titlevarchar(200),
> description text,
> image???, -- maybe varchar
> last_update datetime,
> content_link varchar(200),
> unique_idint,
> date_added datetime,
> content_provider int,
> user_hitsint,
> vote_total int,
> vote_user_total int,
> channel int,
> --...
>
> primary key ( app_id )
>
> );
>
> As i see you get at last 129 different filed type [s9.field_id=129], you can
> split the data into different tables. [Use heigher normal form.]
>
> But if you don't want redesign the tables and all the different fields exists
> then use join instead of left join and then the "group by" is needless.
>
> burci
>
> Thursday, October 23, 2003, 9:05:26 PM, you wrote:
>
> MB> Hey All-
>
> MB> I am trying to improve the speed of a website and was wondering if
> MB> anyone had any ways I can rewrite these queries so that they actually
> MB> run with some descent speed.
>
> MB> Its a really nasty query and I'm not sure where to start, I'd like to
> MB> now have to redo the tables and I already put some indexes on it which
> MB> improved speed a lot but these queries are still very slow. You can most
> MB> certainly laugh to yourselves on this one... jsut trying to get some
> MB> opinions on what I should do with this.
>
> MB> Thanks-
> MB> Matt
>
> MB> SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
> MB> niche, s2.data AS type, s3.data AS title, s4.data AS description,
> MB> s5.data AS image, s6.data AS last_update, s7.data AS content_link,
> MB> s8.data AS unique_id, s9.data AS date_added, s10.data AS
> MB> content_provider, s11.data AS user_hits, s12.data AS vote_total,
> MB> s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
> MB> content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
> MB> content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
> MB> content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
> MB> content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
> MB> content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
> MB> content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
> MB> content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
> MB> content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
> MB> content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
> MB> content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
> MB> JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
> MB> LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
> MB> content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
> MB> = content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
> MB> s14.row_id = content.row_id WHERE content.app_id = 11 AND
> MB> unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') < 0 GROUP
> MB> BY row_id ORDER BY last_update desc LIMIT -1
>
>
>
>
> --
> [nick]:burci [hp]:http://peter.buri.hu [mailto]:[EMAIL PROTECTED] [motto]:"Music
> makes life easier to survive!"
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving Query speed - any suggestions?
Hey All-
I am trying to improve the speed of a website and was wondering if
anyone had any ways I can rewrite these queries so that they actually
run with some descent speed.
Its a really nasty query and I'm not sure where to start, I'd like to
now have to redo the tables and I already put some indexes on it which
improved speed a lot but these queries are still very slow. You can most
certainly laugh to yourselves on this one... jsut trying to get some
opinions on what I should do with this.
Thanks-
Matt
SELECT content.row_id AS row_id, content.app_id AS app_id, s1.data AS
niche, s2.data AS type, s3.data AS title, s4.data AS description,
s5.data AS image, s6.data AS last_update, s7.data AS content_link,
s8.data AS unique_id, s9.data AS date_added, s10.data AS
content_provider, s11.data AS user_hits, s12.data AS vote_total,
s13.data AS vote_user_total, s14.data AS channel FROM content LEFT JOIN
content s1 ON s1.field_id=69 AND s1.row_id = content.row_id LEFT JOIN
content s2 ON s2.field_id=70 AND s2.row_id = content.row_id LEFT JOIN
content s3 ON s3.field_id=71 AND s3.row_id = content.row_id LEFT JOIN
content s4 ON s4.field_id=72 AND s4.row_id = content.row_id LEFT JOIN
content s5 ON s5.field_id=73 AND s5.row_id = content.row_id LEFT JOIN
content s6 ON s6.field_id=74 AND s6.row_id = content.row_id LEFT JOIN
content s7 ON s7.field_id=76 AND s7.row_id = content.row_id LEFT JOIN
content s8 ON s8.field_id=84 AND s8.row_id = content.row_id LEFT JOIN
content s9 ON s9.field_id=129 AND s9.row_id = content.row_id LEFT JOIN
content s10 ON s10.field_id=116 AND s10.row_id = content.row_id LEFT
JOIN content s11 ON s11.field_id=118 AND s11.row_id = content.row_id
LEFT JOIN content s12 ON s12.field_id=120 AND s12.row_id =
content.row_id LEFT JOIN content s13 ON s13.field_id=121 AND s13.row_id
= content.row_id LEFT JOIN content s14 ON s14.field_id=125 AND
s14.row_id = content.row_id WHERE content.app_id = 11 AND
unix_timestamp(s6.data)-unix_timestamp('2003-10-23 23:59:59') < 0 GROUP
BY row_id ORDER BY last_update desc LIMIT -1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Config problems - logging
Hey all- I've been trying to track down some slow queries but I cant get the logging turned on properly, for some reason my server just wont create a slow query log and then when I tried mysqldumpslow, it gave me an error that I have no basedir setup. Has anyone had any similar problem that can shed some light on my situation? I have attached my cnf file thanks- Matt # Example mysql config file for large systems. # # This is for large system with memory = 512M where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M max_connections = 1000 table_cache = 256 sort_buffer_size = 8M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M wait_timeout = 60 log_slow_queries = 1 log = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, #MASTER_USER=, MASTER_PASSWORD= ; # #where you replace , , by quoted strings and # by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = # # The username the slave will use for authentication when connecting # to the master - required #master-user = # # The password the slave will authenticate with when connecting to # the master - required #master-password = # # The port the master is listening on. # optional - defaults to 3306 #master-port = # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 64M #bdb_max_lock = 10 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 256M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 64M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you a
Re: libmysqlclient.so
Thanks everyone! I installed MySQL-shared-X and it worked! On Tue, 2003-10-21 at 15:48, Kelley Lingerfelt wrote: > the mysql-devel rpm installs it. > > Kelley > > > Matt Babineau wrote: > > > Any ideas what packages install this library? I used the RPM to try and > > install MySQL-client on my redhat machine, but the libmysqlclient did > > not show up in /usr/lib - so I am wondering which RPM I need to get this > > file? > > > > Thanks, > > Matt > > > > -- > > 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]
libmysqlclient.so
Any ideas what packages install this library? I used the RPM to try and install MySQL-client on my redhat machine, but the libmysqlclient did not show up in /usr/lib - so I am wondering which RPM I need to get this file? Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just messed up my index on my table....ugh
Its weird, I dropped the indexes off that table, and re-added them but the queries used to take 5 sections but now take much much longer. I did a little reading on the EXPLAIN command and it looks like my query without an index has 9427^15 rows to query. So thats where I am seeing the huge timeout come in. What are some general rules on indexing a table? I have a table like this: id, app_id, row_id, field_id, data int PRI KEY, int, int, int, blob when I query the table I am querying for an item in the data field, lets say I'm looking for 'widget' there are other records with the same row_id as widget so I also want to pull those out of the table too. I hope this makes some sense... Thanks, Matt On Fri, 2003-10-03 at 20:14, Jeremy Zawodny wrote: > On Fri, Oct 03, 2003 at 04:23:56PM -0400, Matt Babineau wrote: > > now the query isn't finishing executing and its killing my cpu... > > > > any idea how to rebuild the index on a table? > > > > or how to get out of this mess? > > Drop and re-add the index? > > Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just messed up my index on my table....ugh
now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just messed up my index on my table....ugh
now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting the wait_timeout option - doesn't work?
Yeah mysql is reading the config file, because I have set other options like query_cache_size...so I'm not sure :-( On Thu, 2003-10-02 at 18:09, Jeremy Zawodny wrote: > On Thu, Oct 02, 2003 at 02:02:34PM -0400, Matt Babineau wrote: > > Hey All-- > > > > I have MySQL 4.0.14 running on Redhat Linux and it won't accept my > > change to the wait_timeout config option! > > > > In the my.cnf file I have a line > > > > wait_timeout = 60 > > > > but when I restart mysql and go into the server and type SHOW VARIABLES; > > > > it says the wait_timeout is still 28800 which is too long! > > Are you sure MySQL is reading the my.cnf file? > > Is the setting in the correct ([mysqld]) section? > > Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting the wait_timeout option - doesn't work?
Hey All-- I have MySQL 4.0.14 running on Redhat Linux and it won't accept my change to the wait_timeout config option! In the my.cnf file I have a line wait_timeout = 60 but when I restart mysql and go into the server and type SHOW VARIABLES; it says the wait_timeout is still 28800 which is too long! Any ideas? Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
