VIEW and ROW_NUMBER
I need to create a view, about a ranking. The select from which I generate the view has a ORDER BY and I need to have a column in that select that shows the position of the object in that ranking. I have searched on google, and I have found that it's possibile to do it using the SET command and using variables.. but I don't think in a VIEW I can use variables and SET. Any idea? Thanks. GF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW and ROW_NUMBER
Hi,
On Jan 3, 2008 9:28 AM, GF [EMAIL PROTECTED] wrote:
I need to create a view, about a ranking.
The select from which I generate the view has a ORDER BY and I need
to have a column in that select that shows the position of the object
in that ranking.
I have searched on google, and I have found that it's possibile to do
it using the SET command and using variables.. but I don't think in a
VIEW I can use variables and SET.
Correct. You can use ordinary SQL, like this:
create table fruits (
type varchar(10) not null,
variety varchar(20) not null,
primary key(type, variety));
insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');
select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
on l.type = r.type
and l.variety = r.variety
group by l.type, l.variety;
+++-+
| type | variety| num |
+++-+
| apple | fuji | 1 |
| apple | gala | 2 |
| apple | limbertwig | 3 |
| cherry | bing | 1 |
| cherry | chelan | 2 |
| orange | navel | 1 |
| orange | valencia | 2 |
| pear | bartlett | 1 |
| pear | bradford | 2 |
+++-+
It is not very efficient on large data sets, though. What about a
stored procedure, or a UDF (a C UDF, not a SQL stored function)? Can
you use either of those?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I need to add to content somehow
I have a DB that has a field in it that currently just holds single or double numbers - these numbers are basically a reference to a category in which the particular record should be displayed. However I have now been asked if I can make it so that a particular record can be displayed in more than one category. All I need to do is somehow ask the field to add a forward slash to the front and end of the data, so the data will go from this: 1 33 21 9 11 to this: /1/ /33/ /21/ /9/ /11/ How do I get MySQL to do this? I guess I might have to do it in two steps by firstly adding the slash to the front and then lastly to the back. But I have no idea on how to do it. My reason for doing this is so that I get the webpage to look for numbers that are between the slashes. this will enable me to have numbers like this: /1/33/9/ enabling me to have a record in more than one category. Any help would be appreciated. Thanks Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: I need to add to content somehow
Use CONCAT().
SELECT CONCAT('/',your_field,'/') 'newData' FROM your_table.
Then use similar logic to do an UPDATE statement, i.e. UPDATE your_table SET
your_field = CONCAT('/',your_field,'/')
You need to first make sure that the field you are trying to update is a
VARCHAR, because as it stands, it looks as though you have that field as an INT.
J.R.
From: Matthew Stuart [EMAIL PROTECTED]
Sent: Thursday, January 03, 2008 12:55 PM
To: MySQL email support mysql@lists.mysql.com
Subject: I need to add to content somehow
I have a DB that has a field in it that currently just holds single
or double numbers - these numbers are basically a reference to a
category in which the particular record should be displayed. However
I have now been asked if I can make it so that a particular record
can be displayed in more than one category.
All I need to do is somehow ask the field to add a forward slash to
the front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is
so that I get the webpage to look for numbers that are between the
slashes. this will enable me to have numbers like this: /1/33/9/
enabling me to have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automation question
Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table join trouble
Hi, I am trying to join a bunch of tables together. I want a row for each learner, and on the row, I want some user, centre, client and centreManager information if there is any. Thus, I am trying to outer join from the learner table to 4 other tables. The query I have so far is below but it is returning 0 rows where I know that learner 215 exists and it should return a line with the l1 information on it. Any help would be very much appreciated. select `vPamUser`.`initials`, l1.`learnerId`, l1.`usedName`, l1.`fullFirstNames`, l1.`surname`, `vPamCentre`.`centreName`, `vPamCentreManager`.`managerName`, `vPamCentreManager`.`managerUsedName`, `vPamClient`.`clientShortName` from `vPamLearner` l1 left join vPamUser on l1.`assessorId` = `vPamUser`.`userId`, `vPamLearner` l2 left join vPamCentre on l2.`centreId` = `vPamCentre`.`centreId`, `vPamLearner` l3 left join vPamClient on l3.`clientId` = `vPamClient`.`clientId`, `vPamLearner` l4 left join vPamCentreManager on l4.`centreManagerId` = `vPamCentreManager`.`centreManagerId` where l1.`learnerId` = 215 and l1.learnerId = l2.learnerId and l1.learnerId = l3.learnerId and l1.learnerId = l4.learnerId ;
Re: I need to add to content somehow
Matthew Stuart wrote:
I have a DB that has a field in it that currently just holds single or
double numbers - these numbers are basically a reference to a category
in which the particular record should be displayed. However I have now
been asked if I can make it so that a particular record can be displayed
in more than one category.
All I need to do is somehow ask the field to add a forward slash to the
front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is so
that I get the webpage to look for numbers that are between the slashes.
this will enable me to have numbers like this: /1/33/9/ enabling me to
have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
First, I would like to suggest you to rethink about your whole idea
because you are going wrong directions.
It' possible to do it your way but it's not correct/the best way. For
example, one day you will need to select all products for specific
category and then you have to use LIKE to find them - what's wrong.
Think about idea to create new table in_categories with only 2
columns: prod_id and cat_id:
prod_id| cat_id
123 | 1
123 | 33
123 | 9
45 | 33
28 | 33
If you still want to do it your way, I'll suggest to use comma instead /
1,33,9 (even it really doesn't change a lot) and don't put / at the
front and at the end: 1/33/9
explode('/', '/1/33/9/'); will create 5 elements of the array
explode('/', '1/33/9'); will create 3 elements of the array - the number
you need.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I need to add to content somehow
Matthew Stuart wrote:
I have a DB that has a field in it that currently just holds single or
double numbers - these numbers are basically a reference to a category
in which the particular record should be displayed. However I have now
been asked if I can make it so that a particular record can be displayed
in more than one category.
All I need to do is somehow ask the field to add a forward slash to the
front and end of the data, so the data will go from this:
1
33
21
9
11
to this:
/1/
/33/
/21/
/9/
/11/
How do I get MySQL to do this? I guess I might have to do it in two
steps by firstly adding the slash to the front and then lastly to the
back. But I have no idea on how to do it. My reason for doing this is so
that I get the webpage to look for numbers that are between the slashes.
this will enable me to have numbers like this: /1/33/9/ enabling me to
have a record in more than one category.
Any help would be appreciated.
Thanks
Mat
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
actually, forgot to answer on your question:
if your table name is your_table and column with categories is named
categories:
UPDATE your_table SET categories=CONCAT('/', categories, '/');
it will update your whole table at once.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table join trouble
Hi, I hope this finds itself as an answer to my last post. I'm new to this email message format of lists. It turns out that the problem with the join was that I had a column being selected that I removed from my posting example for brevity which was causing the select to fail. The extra column being selected was: l1.`status`, Adding this to the select meant it returned zero rows. Removing it from the select made the select work OK. A bug maybe? The only reason why I can think this particular column might be causing the problem is that status is a keyword of some sort. Cheers
RE: automation question
3 ideas come to mind- cron and or cruisecontrol for off-hours scriptingant for handling cross-platform scriptingmaven for implementing dependency checking as well as the ability to pull from online repositories Youtube.comI worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate???Thanks/Martin Gainty__Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 3 Jan 2008 13:06:07 -0500 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: automation question Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. http://www.xbox.com/en-US/hardware/wheretobuy/
Re: automation question
Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 3 Jan 2008 13:06:07 -0500 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: automation question Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! http://www.xbox.com/en-US/hardware/wheretobuy/ -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem forcing indexes
I have the following 2 tables:
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and
CREATE TABLE media_views (
media_id int(20) unsigned NOT NULL,
user_id int(12) unsigned NOT NULL,
views int(20) unsigned NOT NULL,
30d int(20) unsigned NOT NULL,
7d int(20) unsigned NOT NULL,
24h int(20) unsigned NOT NULL,
site30d int(11) unsigned NOT NULL default '0',
site7d int(11) unsigned NOT NULL default '0',
site24h int(11) unsigned NOT NULL default '0',
click int(20) NOT NULL,
last_dt timestamp NOT NULL default '-00-00 00:00:00' on update
CURRENT_TIMESTAMP,
PRIMARY KEY (media_id),
KEY last_dt (last_dt),
KEY user_id (user_id),
KEY 7d (7d,24h)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and the following query using them:
select
SQL_BIG_RESULT media.*, media_views.*, media.created as dt,
media_views.views + media_views.embeds as alltime_views
FROM media
JOIN media_views ON ( media.id = media_views.media_id )
where
media.status = 'active'
and media.type = 'whatever'
order by
24h DESC, media.created desc LIMIT 0, 20
each table has about 125,000 records, and the query takes about 4 seconds to
run.
When I run explain on the query, it says:
++-+-++---+-+-+---+---+--+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra|
++-+-++---+-+-+---+---+--+
| 1 | SIMPLE | media | ref| PRIMARY,type | type|
2 | const | 56518 | Using where; Using temporary; Using
filesort |
| 1 | SIMPLE | media_views | eq_ref | PRIMARY | PRIMARY |
4 | awv_free.media.id | 1
| |
++-+-++---+-+-+---+---+--+
When I try to add a force index (PRIMARY) after the media table to try and
make is use PRIMARY, rather than TYPE, the optimizer switches and uses no
key at all. I've tried to change the order in which the tables are selected,
but it seems to have no effect. In some scenarios it will switch and use the
media_views table, but the rows is still 125,000+ using temporary and
filesort.
how can I get this query time down?
Re: Performance problem - MySQL at 99.9% CPU
Hi, Thanks. mysql show processlist; ++---+---+---+-+--+--+--+ | Id | User | Host | db| Command | Time | State | Info | ++---+---+---+-+--+--+--+ | 698938 | vpopmail_edit | localhost | vpopmail | Sleep | 4068 | | NULL | | 704841 | vpopmail_edit | localhost | vpopmail | Sleep | 723 | | NULL | | 704995 | vpopmail_edit | localhost | vpopmail | Sleep | 648 | | NULL | | 705040 | vpopmail_edit | localhost | vpopmail | Sleep | 627 | | NULL | | 705145 | root | localhost | NULL | Query |0 | NULL | show processlist | | 706248 | vpopmail_edit | localhost | vpopmail | Sleep | 51 | | NULL | | 706291 | vpopmail_edit | localhost | vpopmail | Sleep | 26 | | NULL | | 706292 | bmwguiden | localhost | bmwguiden | Query |0 | Sending data | SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_interests, u.user_website, u.user_em | | 706293 | bmwguiden | localhost | bmwguiden | Query |0 | Locked | SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_interests, u.user_website, u.user_em | | 706295 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706296 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706297 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706298 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706303 | bmwguiden | localhost | bmwguiden | Query | 12 | Sending data | SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post | | 706306 | bmwguiden | localhost | bmwguiden | Query |0 | Sorting result | SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post | | 706309 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706310 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706311 | bmwguiden | localhost | bmwguiden | Query |0 | Locked | DELETE FROM phpbb_sessions WHERE session_time 1199391883 AND session_id 'f378eae | | 706312 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706313 | bmwguiden | localhost | bmwguiden | Query |0 | Sending data | SELECT t.forum_id, t.topic_id, p.post_time FROM phpbb_topics t, phpbb_posts p WHERE p.post_i | | 706317 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL | | 706320 | bmwguiden | localhost | bmwguiden | Sleep |0 | | NULL
RE: Performance problem - MySQL at 99.9% CPU
Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9%, AND there's a processor queue (4.36)? Cheers, Gunnar R. On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote: Hi, If you can follow this document: http://www.ufsdump.org/papers/uuasc-june-2006.pdf You should be able to figure out what's happening. Cheers, Andrew -Original Message- From: Gunnar R. [mailto:[EMAIL PROTECTED] Sent: Tue, 01 January 2008 23:31 To: mysql@lists.mysql.com Subject: Performance problem - MySQL at 99.9% CPU Hello, I am running a community site mainly based on phpBB. It has about 9.300 registered users, 650.000 posts and about 200.000 visitors/month (12 mill hits). The SQL database is about 700MB. It's all running on a couple of years old Dell box with two P4 Xeon 1.7Ghz CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- 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 forcing indexes
On Jan 3, 2008 4:23 PM, Tanner Postert [EMAIL PROTECTED] wrote: When I try to add a force index (PRIMARY) after the media table to try and make is use PRIMARY, rather than TYPE, the optimizer switches and uses no key at all. It usually knows better than you do about indexes. I've tried to change the order in which the tables are selected, but it seems to have no effect. It should be able to choose the best order most of the time. You can force it, but that's nearly always a mistake. In some scenarios it will switch and use the media_views table, but the rows is still 125,000+ using temporary and filesort. For this relatively small result set, temporary and filesort may not be a big deal. They are probably being used to handle your ORDER BY. how can I get this query time down? You can try some combined indexes, like one on media (id, status, type, created) and one on media_views (media_id, 24h). I don't think you can eliminate the temp table with that ORDER BY though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automation question
Martin, I took a quick look at the tools that you mentioned. Seems you are heavily involved in Java :) We don't use Java at all, and other than using cron (which I do use for backups and standard stuff like that) not really what I am looking for. After thinking about this, I suspect that this will all just be a custom scripting with something like dsh for the distributed part. Not really looking for source code control -- more like things to manage the distribution of standardized my.cnf files (which we already control using subversion) to 100 servers at a time. Looking to scale efforts so I don't have to manually do things. thanks, Keith B. Keith Murphy wrote: Martin, Martin Gainty wrote: 3 ideas come to mind- cron and or cruisecontrol for off-hours scripting ant for handling cross-platform scripting maven for implementing dependency checking as well as the ability to pull from online repositories I will take a look at these. I am certainly familiar with cron. The others not so much. Youtube.com I worked with a company last summer that implemented identical functionality and wanted to firewall youtube videos to use their (considerably more capable as far as supporting every codec on the planet) player..youtube said if you link/use/href any amount of content to our site then you MUST use our player..its no WONDER they have that incredible pageview traffic .. Is there some specific functionality you want to Automate??? I have some things in mind, but basically any task that I am performing across multiple servers I want to automate. While I might have time to manually do things across 20 servers (not really... but that is the current state) I won't be able to do so across 100. Make sense? Thanks/ Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 3 Jan 2008 13:06:07 -0500 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: automation question Everyone, So, I have been thinking recently about automation. This morning I listened to a talk by one of the three dbas at Youtube (from the MySQL Users Conference last year). Think about that. They mentioned 100,000,000 pageviews in one day (the data was from 2006). And THREE dbas. Of course this is done by automating anything you can. This isn't the first time I have heard of such ratios of servers/dbas. While I don't have the servers or the traffic that they do at Youtube we are growing quite nicely and adding db servers on a fairly regular basis. It is time to put some serious thought into automating some things. I was wondering what experience others have had with any technologies (I am modestly familier with expect and have touched dsh). Thoughts?? thanks, keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! http://www.xbox.com/en-US/hardware/wheretobuy/ -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
Gunnar, us = user (things like MySQL/PHP/Apache) sy = system (memory management / swap space / threading / kernel processes and so on) ni = nice (apps running only when nothing else needs the resource) id = idle (extra cpu cycles being wasted) wa = wait state (io wait for disk/network/memory) hi si - interrupts Generally acceptable load should be #processors (so in your case 2 is okay - machine is performing well - 4 somethings being over utilized somewhere) Also in top 100% = 100% of one processor, so in a dual processor (or core) setup, you can actually go to 200% Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59% overall) of system resources. Your system processes are taking up 66.2% (of 200% or 33% overall) and it's leaving about 14% (of 200% - so 7% overall) of the system idle. The remainders are I/O waits etc (your numbers look pretty good there, but IO wait can spike and so may be misleading without using other tools. You may be encountering a thrashing problem with the amount of memory left or any number of things, but I would look at memory use on this box, because your load is pretty high and your performance is suffering if it's staying there. Your memory is at about 92% utilized too... while 91Mb seems like a lot of memory - it's easily consumed by a couple of large queries, sorts and so on which then goes right to disk swapping for virtual memory - never good for performance. It might also be impacted by IO and you just can't see it in the one slice of top we have here. If that number spikes up to 5% and then falls back down - it might be time spent going to disk with temp tables etc. Also turn on slow query logging (yes, I know it's another performance hit) and see if there is one query that's particularly problematic, perhaps optimizing the indexes etc on the table might help with the performance. Also, make sure your HD's aren't full... that will kill performance very quickly if the needed disk space isn't there. Erik On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote: Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9%, AND there's a processor queue (4.36)? Cheers, Gunnar R. On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote: Hi, If you can follow this document: http://www.ufsdump.org/papers/uuasc-june-2006.pdf You should be able to figure out what's happening. Cheers, Andrew -Original Message- From: Gunnar R. [mailto:[EMAIL PROTECTED] Sent: Tue, 01 January 2008 23:31 To: mysql@lists.mysql.com Subject: Performance problem - MySQL at 99.9% CPU Hello, I am running a community site mainly based on phpBB. It has about 9.300 registered users, 650.000 posts and about 200.000 visitors/month (12 mill hits). The SQL database is about 700MB. It's all running on a couple of years old Dell box with two P4 Xeon 1.7Ghz CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list
INNODB ENGINE NOT AVAILABLE
Hi All, I was trying to change the data directory of mysql on debian. I have added two thins in my.cnf innodb_data_home_dir = /data/mysqldata/ datadir = /data/mysqldata/ Then restarted mysql server. mysql shows there is no innodb storage engine available. after then i again verified my.cnf. all skip-innodb is commented. I don't understand whats screwy is going on. Same thing i have tested on linux fedora its works properly. Whats wrong with debian. Thanks, -- Krishna Chandra Prajapati
debian-sys-maint
Hi all, Mysql on debian operating system has debian-sys-maint user. What is the role of this user debian-sys-maint. After stopping mysql. If i delete all the things from mysql data directory. Executed mysql_install_db to create mysql directory with mysql data directory and start mysql server. It gives warning it don't found the debian-sys-maint user. I am trying to restart and stop mysql server, but it is not working. What can be the issue. Thanks, -- Krishna Chandra Prajapati
Re: debian-sys-maint
Hello, Mysql on debian operating system has debian-sys-maint user. What is the role of this user debian-sys-maint. After stopping mysql. If i delete all the things from mysql data directory. Executed mysql_install_db to create mysql directory with mysql data directory and start mysql server. It gives warning it don't found the debian-sys-maint user. Corrupt Mysql-Debian installation? What kind of Debian version? Etch, Testing, Sid?? Try to purge and reinstall mysql-server. -- Best Regards Vlad Vorobiev http://www.mymir.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debian-sys-maint
Hi , mysql server started with warning and working properly. But it is not stopping and not restarting. Server version: 5.0.32-Debian_7etch1-log Debian etch distribution Krishna Chandra Prajapati On Jan 4, 2008 11:04 AM, Vladislav Vorobiev [EMAIL PROTECTED] wrote: Hello, Mysql on debian operating system has debian-sys-maint user. What is the role of this user debian-sys-maint. After stopping mysql. If i delete all the things from mysql data directory. Executed mysql_install_db to create mysql directory with mysql data directory and start mysql server. It gives warning it don't found the debian-sys-maint user. Corrupt Mysql-Debian installation? What kind of Debian version? Etch, Testing, Sid?? Try to purge and reinstall mysql-server. -- Best Regards Vlad Vorobiev http://www.mymir.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: debian-sys-maint
mysql server started with warning and working properly. But it is not stopping and not restarting. Server version: 5.0.32-Debian_7etch1-log Debian etch distribution I have never had problems with mysql installation on Debian. I would try to reinstall mysql. Something like this: killall -9 mysql-server (make backup of your data) dpkg -r mysql* dpkg --purge mysql* apt-get install mysql-server -- Best Regards Vladislav Vorobiev http://www.mymir.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb_buffer_pool_pages_free heading towards 0
Hi All, On mysql production server one of the innodb component Innodb_buffer_pool_pages_free =1 . Although there are no issue with the performance with the server. What should be the optimum value of Innodb_buffer_pool_pages_free. Thanks, -- Krishna Chandra Prajapati
Re: debian-sys-maint
This is one of the few decisions the debian package maintainers made that I disagree with, but the idea is that when you install mysql, there is this user created with a random password. This gives the package maintainers a way to script updates in SQL if necessary to run on the database during an upgrade. The SysV startup script uses this account somehow (I forget now and I'm too lazy to open it up in a text editor) but that's what's generating the warning. Not MySQL. It's just a shell script warning. Edit the startup script and remove the user. It won't affect anything but you may get some errors during an upgrade at some point if they decide to use it. It's not a big security issue unless someone gets root access to the server, but if that's the case, I don't think mysql security will rank among the greatest of your worries. -Micah On 01/03/2008 09:23 PM, Krishna Chandra Prajapati wrote: Hi all, Mysql on debian operating system has debian-sys-maint user. What is the role of this user debian-sys-maint. After stopping mysql. If i delete all the things from mysql data directory. Executed mysql_install_db to create mysql directory with mysql data directory and start mysql server. It gives warning it don't found the debian-sys-maint user. I am trying to restart and stop mysql server, but it is not working. What can be the issue. Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debian-sys-maint
Krishna Chandra Prajapati wrote: Hi all, Mysql on debian operating system has debian-sys-maint user. What is the role of this user debian-sys-maint. Read the /etc/mysql/debian-start file to see everything that debian does when mysql starts up. It uses the debian-sys-maint user for all of those tasks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
