Re: Performance problem - MySQL at 99.9% CPU
Gunnar R. wrote: 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. I think you've got an application problem somewhere which you should look into first. Hardware-wise I think you're doing fine, except you could probably increase overall performance with more memory. MySQL is pretty good at query-caching. /Per Jessen, Zürich -- 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
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 02, 2008 7:51 AM To: mysql@lists.mysql.com Subject: Re: Performance problem - MySQL at 99.9% CPU Gunnar R. wrote: 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. I think you've got an application problem somewhere which you should look into first. Hardware-wise I think you're doing fine, except you could probably increase overall performance with more memory. MySQL is pretty good at query-caching. Just for general info I tested Heap tables vs the query cache, query cache one and it makes a lot of sense why once I saw that. Even in-memory tables can't be as fast(giving queries in the cache) because of the cost of parsing and optimization of the query. The query cache being basicly a fast in memory hash lookup. However, if you have a system that doesn't have a lot of repetative queries, the Heap table would win again that just makes sense, but my little test proved the query cache is pretty good for most things. -- 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
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]
RE: order by in query
Hello i use this query: select i.item_id from orders o INNER JOIN item i ON i.nr=i.nr Should the line above not be ... ON i.nr = o.nr ? INNER JOIN user_cart u ON u.nr=i.nr where (i.count !=0 or i.count!=NULL) and i.isactive=1 and i.kolWo0 order by i.count DESC LIMIT 5 It works quickly without ORDER BY With ORDER BY, CPU goes to 100% and I have to wait. i.count has Btree Index Allways if I use order by is the query very slow also ORDER BY i.item_id is slow to. i.item_id, i.nr has Btree indexes. Mysql version 5.0.45-5 Any Ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up via slave
Hi Martin, Hi folks, I have two MySQL servers running in a master-slave configuration, and I want to set up a process for backing up our application's data in which backups are sent to a server at another location. Ideally, I'd like to do a full backup once a week, and then incremental backups every 6 hours. It seems to make the most sense for this to happen on the slave. I was thinking it could work something like this: Weekly job: - Stop slave - Flush and delete binlogs on slave - Use mysqldump to generate full backup on slave - Create a directory on the remote server for this week's backups, and copy the full backup file over to it - Start slave Every-6-hours job: - Stop slave - Flush binlogs on slave - Copy over any newly created binlog files from the slave to the current weekly directory on the remote server - Start slave Then, if I needed to restore the backup, I'd: - Copy the weekly directory from the remote server to the MySQL server - Play back the full backup on the MySQL server - Play back the binlogs on the MySQL server I'm just curious as to whether the more experienced folks here think this is a logical approach, and if so, whether there are any caveats in particular to watch out for. (I've already stumbled upon the fact that I need to set log-slave-updates in order to have binlogs on my slave to be incrementally backed up.) Any thoughts? If this is a totally boneheaded approach, how would you recommend going about it? Thanks, Martin We use replication solely for the purpose of creating backups. Because we use NetApp filers, there's no need to use mysqldump (which would take very long since we have of lot of data in MySQL). What I do is the following. Every hour: - stop slave threads - stop slave server - make snapshot of NetApp qtree and store it in an 'hourly' directory - start slave server - start slave threads Once a day: - do the same thing, store the snapshot in a 'daily' directory. The hourly snapshots are rotated, and we keep five of these. Every day a 'daily' snapshot is made which is kept online for 30 days. I haven't used Baron's tools yet, but I will definitely give them a try, since rebuilding the slave from scratch after replication cannot be reliably restarted (it has issues sometimes) takes much too long with the amount of data we have. Regular (daily) snapshots are created on the master as well, with a running server. This causes data inconsistencies (we use InnoDB as well) but that is of no concern: what it does for us is that it keeps backups of the master binary logs. That way I don't have to run the slaves with 'log-slave-updates', which saves diskspace (couple of gigs a day). I wrote a script that purges the master's binary logfiles, to prevent the disks from filling up with them. This script is run once a day, and goes something like this: - start - check the master servers to find out the names/ports of their slaves - check on the slaves that replication is running and that there's no replication lag - if the slave is not running: alert the admins by email, and quit - purge the binary logs on the master till 'now() - interval 1 days' - end Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up via slave
Hi Martijn, Master will not have any information of SLAVE how r u doing this in you second step. - check the master servers to find out the names/ports of their slaves regards anandkl On 1/2/08, Martijn van den Burg [EMAIL PROTECTED] wrote: Hi Martin, Hi folks, I have two MySQL servers running in a master-slave configuration, and I want to set up a process for backing up our application's data in which backups are sent to a server at another location. Ideally, I'd like to do a full backup once a week, and then incremental backups every 6 hours. It seems to make the most sense for this to happen on the slave. I was thinking it could work something like this: Weekly job: - Stop slave - Flush and delete binlogs on slave - Use mysqldump to generate full backup on slave - Create a directory on the remote server for this week's backups, and copy the full backup file over to it - Start slave Every-6-hours job: - Stop slave - Flush binlogs on slave - Copy over any newly created binlog files from the slave to the current weekly directory on the remote server - Start slave Then, if I needed to restore the backup, I'd: - Copy the weekly directory from the remote server to the MySQL server - Play back the full backup on the MySQL server - Play back the binlogs on the MySQL server I'm just curious as to whether the more experienced folks here think this is a logical approach, and if so, whether there are any caveats in particular to watch out for. (I've already stumbled upon the fact that I need to set log-slave-updates in order to have binlogs on my slave to be incrementally backed up.) Any thoughts? If this is a totally boneheaded approach, how would you recommend going about it? Thanks, Martin We use replication solely for the purpose of creating backups. Because we use NetApp filers, there's no need to use mysqldump (which would take very long since we have of lot of data in MySQL). What I do is the following. Every hour: - stop slave threads - stop slave server - make snapshot of NetApp qtree and store it in an 'hourly' directory - start slave server - start slave threads Once a day: - do the same thing, store the snapshot in a 'daily' directory. The hourly snapshots are rotated, and we keep five of these. Every day a 'daily' snapshot is made which is kept online for 30 days. I haven't used Baron's tools yet, but I will definitely give them a try, since rebuilding the slave from scratch after replication cannot be reliably restarted (it has issues sometimes) takes much too long with the amount of data we have. Regular (daily) snapshots are created on the master as well, with a running server. This causes data inconsistencies (we use InnoDB as well) but that is of no concern: what it does for us is that it keeps backups of the master binary logs. That way I don't have to run the slaves with 'log-slave-updates', which saves diskspace (couple of gigs a day). I wrote a script that purges the master's binary logfiles, to prevent the disks from filling up with them. This script is run once a day, and goes something like this: - start - check the master servers to find out the names/ports of their slaves - check on the slaves that replication is running and that there's no replication lag - if the slave is not running: alert the admins by email, and quit - purge the binary logs on the master till 'now() - interval 1 days' - end Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by in query
2008/1/2, Edward Kay [EMAIL PROTECTED]: Hello i use this query: select i.item_id from orders o INNER JOIN item i ON i.nr=i.nr Should the line above not be ... ON i.nr = o.nr ? Autch. thank you! It works now. -- 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]
Database Migration Path / Activity List?
Hey there
I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24,
mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1
Master + Slave Replication server, with InnoDB tables only, over a new
hardware.
Unfortunately, this is the first time I play a role as MySQL DBA, and
don't have a complete migration plan (yet).
Can someone here please review my migration plan and help me
adding/prunning missing/exceeding bits?
My current plan is more or less like below. I removed task descriptions
removed, guess the task name is descriptive enough. Indenting marks
sub-tasks, as expected.
Many thanks in advance.
Cheers!
- BEGIN TASK LIST -
Database Migration Project
Current Database Procedures Mapping
Plan Database Test Procedure
Determine Current Database Backup Procedures
Determine Current Database Restore Procedures
Obtain Copies of the Current Database Backup Scripts
Obtain Copies of the Current Database Restore Scripts
Determine Current Database Stored Procedures / Triggers
Database Statistics Collection
Determine Current Database Size
Determine Current Database Grow Ratio
Determine Current Database Schema
Operating System Installation and Configuration Plan
Determine Hardware Requirements
Determine Operating System Requirements
Determine Required Operating System Configuration
Plan Operating System Installation
Plan Operating System Configuration
Document Changes on Operating System
Database Software Installation and Configuration Plan
Study Need for Database Build
Plan Database Build
Plan Database Installation
Plan Database Configuration
Database Performance Tunning
Determine Current Database Queries
Determine Current Database Performance Indicators
Plan Measurement Procedure for the Performance Indicators
Measure Current Database Performance
Database Schema Refactoring
Obtain Current Database Schema Definition
Review Database Schema
Review Database Stored Procedures and Triggers
Suggest Changes for Database Schema
Study Desired Database Backward Compatibility Level
Design Views to Allow Database Backward Compatibility
Redesign Database
Write SQL for Changes
Staging System Deployment and Testing
Install Operating System in the Staging Environment
Configure Operating System in the Staging Envinronment
Build Database Software in Staging Environment
Install Database Software in Staging Environment
Configure Database Software in Staging Environment
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Fix or Improve Restore Procedure
Test Database in Staging Environment
Measure Staging Database Performance Indicators
Compare Performance Figures for the Staging Database
Production Database Deployment
Plan Database Maintenance Stop
Advertise Database Maintenance Stop
Install Operating System in the Production Environment
Configure Operating System in the Production Envinronment
Build Database Software in Production Environment
Install Database Software in Production Environment
Configure Database Software in Production Environment
Stop Production Database
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Test Database in Production Environment
Start Database in Production Environment
Database Monitoring
Follow Up Database Behavior and Performance
- END TASK LIST -
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Migration Path / Activity List?
Hi Luis, On Jan 2, 2008 8:53 AM, Luis Motta Campos [EMAIL PROTECTED] wrote: Hey there I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24, mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1 Master + Slave Replication server, with InnoDB tables only, over a new hardware. Unfortunately, this is the first time I play a role as MySQL DBA, and don't have a complete migration plan (yet). Can someone here please review my migration plan and help me adding/prunning missing/exceeding bits? My current plan is more or less like below. I removed task descriptions removed, guess the task name is descriptive enough. Indenting marks sub-tasks, as expected. Many thanks in advance. Cheers! - BEGIN TASK LIST - Database Migration Project Current Database Procedures Mapping Plan Database Test Procedure Determine Current Database Backup Procedures Determine Current Database Restore Procedures Obtain Copies of the Current Database Backup Scripts Obtain Copies of the Current Database Restore Scripts Determine Current Database Stored Procedures / Triggers Database Statistics Collection Determine Current Database Size Determine Current Database Grow Ratio Determine Current Database Schema Operating System Installation and Configuration Plan Determine Hardware Requirements Determine Operating System Requirements Determine Required Operating System Configuration Plan Operating System Installation Plan Operating System Configuration Document Changes on Operating System Database Software Installation and Configuration Plan Study Need for Database Build Plan Database Build Plan Database Installation Plan Database Configuration Database Performance Tunning Determine Current Database Queries Determine Current Database Performance Indicators Plan Measurement Procedure for the Performance Indicators Measure Current Database Performance Database Schema Refactoring Obtain Current Database Schema Definition Review Database Schema Review Database Stored Procedures and Triggers Suggest Changes for Database Schema Study Desired Database Backward Compatibility Level Design Views to Allow Database Backward Compatibility Redesign Database Write SQL for Changes Staging System Deployment and Testing Install Operating System in the Staging Environment Configure Operating System in the Staging Envinronment Build Database Software in Staging Environment Install Database Software in Staging Environment Configure Database Software in Staging Environment Obtain Current Copy of the Database Backup from Production Restore Database Using the Restore Procedure Fix or Improve Restore Procedure Test Database in Staging Environment Measure Staging Database Performance Indicators Compare Performance Figures for the Staging Database Production Database Deployment Plan Database Maintenance Stop Advertise Database Maintenance Stop Install Operating System in the Production Environment Configure Operating System in the Production Envinronment Build Database Software in Production Environment Install Database Software in Production Environment Configure Database Software in Production Environment Stop Production Database Obtain Current Copy of the Database Backup from Production Restore Database Using the Restore Procedure Test Database in Production Environment Start Database in Production Environment Database Monitoring Follow Up Database Behavior and Performance - END TASK LIST - What are the biggest changes you anticipate? I'd say they will be the version upgrade, converting to InnoDB, and using replication. It looks like you have planned well for all but using replication. Unless you are familiar with it, that is likely to be a bigger change than the version upgrade and switch to InnoDB. There's a lot to learn about replication if you haven't used it before. I'd suggest that you read the manual chapters about replication and binary logging, and definitely experiment with replication. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inter-version client/server compatibility
To what extent can MySQL clients interact with servers of different versions? In particular, if I link an Apache PHP module with the client libraries for MySQL version 5.0.51, will I be able to use it to connect to a version 4.1.22 server? I ask this because I am running a web hosting service, and have a number of different users using MySQL 4.1 servers via PHP from Apache. I need to help them to convert their servers to MySQL 5. If the mixed-version set-up described above is OK, I could conveniently upgrade them all to the same new PHP module _before_ upgrading the servers individually. If the mixed-version set-up is not OK, I have a more challenging task. I have to keep the clients and servers in step. P.S. I am more familiar with Oracle than with MySQL. I know that an Oracle 10 client can be used with with servers going back at least to Oracle 8. -- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED]Tel: +44 1223 334506, Fax: +44 1223 334679 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Migration Path / Activity List?
Baron Schwartz wrote:
What are the biggest changes you anticipate? I'd say they will be the
version upgrade, converting to InnoDB, and using replication. It
looks like you have planned well for all but using replication.
That's interesting. What kind of activities (besides configuration,
maybe?) are interesting to add?
Unless you are familiar with it, that is likely to be a bigger change
than the version upgrade and switch to InnoDB.
That's alarming. I was expecting replication to be a lesser change, not
something this big. Isn't predicting that the replication is one of the
three biggest changes I have in my database a bit of an exaggeration?
There's a lot to learn about replication if you haven't used it
before. I'd suggest that you read the manual chapters about
replication and binary logging, and definitely experiment with
replication.
Thanks for the advice. I will start reading right away.
I would like to read more comments from the members of this list.
Thank you all for your patience and help.
Cheers!
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Tables
Either I don't understand your reply or I miscommunicated. How do I do that when it is the tables that I want to select; that is *all* tables that are called xyz$variable, where variable is unknown but all tables begin xys$? TIA, Victor On Dec 31, 2007 3:59 PM, mos [EMAIL PROTECTED] wrote: At 12:51 PM 12/31/2007, you wrote: Hi; Is it possible to sort tables within a given database? How? TIA, Victor Victor, You mean physically sort the table based on a field or key so you don't have to do an Order By clause each time you do a Select? Not really because the order of the table is expected to be random unless you specify an Order by clause. The only thing I can think of is to create a new table, maybe temporary or Memory table and copy the data into it already sorted. drop table if exists newtable; create newtable like oldtable; insert into newtable select * from oldtable order by col1, col2; Now you should be able to Select * from NewTable; without sorting (if you don't update it). The order should be by col1,col2. (No guarantee) If you want to sort it in order to speed it up, then run an Optimize on the table. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Migration Path / Activity List?
Hi, On Jan 2, 2008 10:04 AM, Luis Motta Campos [EMAIL PROTECTED] wrote: Baron Schwartz wrote: What are the biggest changes you anticipate? I'd say they will be the version upgrade, converting to InnoDB, and using replication. It looks like you have planned well for all but using replication. That's interesting. What kind of activities (besides configuration, maybe?) are interesting to add? Lots of experimentation! Let me ask it another way: how are you planning to use replication? -- for load balancing/scaling reads, backups, a hot standby machine? Unless you are familiar with it, that is likely to be a bigger change than the version upgrade and switch to InnoDB. That's alarming. I was expecting replication to be a lesser change, not something this big. Isn't predicting that the replication is one of the three biggest changes I have in my database a bit of an exaggeration? Definitely not. It is a huge change. From one server to a two-server replication setup is a quantum leap. You'll have all kinds of new things to think about, such as data consistency, dealing with replication lag, performance changes on the master due to binary logging, etc. It depends a lot on WHY you're using replication (see my earlier question). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Tables
Hi, Try this: SHOW TABLES LIKE 'xyz%'; Baron On Jan 2, 2008 10:17 AM, Victor Subervi [EMAIL PROTECTED] wrote: Either I don't understand your reply or I miscommunicated. How do I do that when it is the tables that I want to select; that is *all* tables that are called xyz$variable, where variable is unknown but all tables begin xys$? TIA, Victor On Dec 31, 2007 3:59 PM, mos [EMAIL PROTECTED] wrote: At 12:51 PM 12/31/2007, you wrote: Hi; Is it possible to sort tables within a given database? How? TIA, Victor Victor, You mean physically sort the table based on a field or key so you don't have to do an Order By clause each time you do a Select? Not really because the order of the table is expected to be random unless you specify an Order by clause. The only thing I can think of is to create a new table, maybe temporary or Memory table and copy the data into it already sorted. drop table if exists newtable; create newtable like oldtable; insert into newtable select * from oldtable order by col1, col2; Now you should be able to Select * from NewTable; without sorting (if you don't update it). The order should be by col1,col2. (No guarantee) If you want to sort it in order to speed it up, then run an Optimize on the table. 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]
Re: Database Migration Path / Activity List?
Baron Schwartz wrote:
On Jan 2, 2008 10:04 AM, Luis Motta Campos wrote:
Baron Schwartz wrote:
What are the biggest changes you anticipate? I'd say they will be
the version upgrade, converting to InnoDB, and using
replication. It looks like you have planned well for all but
using replication.
That's interesting. What kind of activities (besides configuration,
maybe?) are interesting to add?
Lots of experimentation!
Let me ask it another way: how are you planning to use replication?
-- for load balancing/scaling reads, backups, a hot standby machine?
OK, I guess a bit more of information can help. I have a very sensitive
database at the company, almost unprotected at the moment. It's big
hardware, lots of hot-replaceable parts, and a quite big energy
generator attached, but nothing guarantees our data between the last
backup and the present moment case the building burns down to the ground.
The database I/O activity obey the 80-20 rule: 80% reads and 20% writes.
I have an average of 80,000 read-queries per second (that's average - I
got 'nice' peaks sometimes, with 4 times more activity). We have 20,000
write-queries per second in the same database.
As we're addressing the risk of major disasters (like the building
burning down to ashes), the plan is spreading this database through the
two data centers we hire: one data center would hold the master
database, and a spare reading slave, and the other data center would
hold the reading slave and a spare master database.
Don't worry about connectivity issues: I have plenty of bandwidth
between those two sites, and I can ask for more. I just need to know in
advance how much to ask for, and I will surely get it.
The master/spare and slave/spare machines would be connected through
heartbeat and will keep the database in a DRDB filesystem. This will
guarantee that, in case of failure, the other machine can raise the same
IP address in it's own interface and continue operations after a short
delay.
Now, about the existing database: it runs Debian Stable, and a
pre-compiled mysql server. As I said before, it's a big machine, but
it's getting old (more than 2 years already), and must be replaced.
The system running on it is quite old, more than 10 years old. This
means that the design and implementation aren't nice. And the
maintenance added new improvements where needed (as InnoDB tables).
The basic idea is to completely re-design and re-implement the database
as soon as the new server is in place (being stability and failure
resistance the priorities fixed by management for the first stage).
Isn't predicting that the replication is one of the three biggest
changes I have in my database a bit of an exaggeration?
Definitely not. It is a huge change. From one server to a
two-server replication setup is a quantum leap. You'll have all
kinds of new things to think about, such as data consistency, dealing
with replication lag, performance changes on the master due to
binary logging, etc.
It depends a lot on WHY you're using replication (see my earlier
question).
Well, I guess I don't need to worry about multi-master replication for
now, the application must be re-designed and re-implemented before we
can start thinking about this. It's currently quite hard to maintain,
and we don't know a lot of important things about it. There is a team
working on this already.
About data consistency, I would like to have some pointers: is this an
issue, provided that I stick to single-master architectures?
About binary logging, I must first read more about this. Thanks for the
warning, though.
Replication lag is another thing that worries me a lot - is there a
mathematical model I can use in order to forecast the expected average
replication lag?
The application can handle quite big replication lags at the moment.
It's not web-based, and there is no users directly interacting with it
at the present moment, what makes everything much simpler - you can have
a program waiting for 5 minutes to see a change without a lot of fuss
about it.
Sorry, I guess this email is a bit bigger than I was expecting... :(
I hope this gives you a general idea about my current problem.
Please feel free to ask more about it if you feel that this details
aren't enough.
Kind regards.
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedding MySQL
Hi, I want to embed MySQL and install it with a freeware application I make. I hope it is legally to do this. Please tell me where can I found more information about how can I do this. The app will run under Windows. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Tables
Ah, yes. Thank you. On Jan 2, 2008 11:51 AM, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Try this: SHOW TABLES LIKE 'xyz%'; Baron On Jan 2, 2008 10:17 AM, Victor Subervi [EMAIL PROTECTED] wrote: Either I don't understand your reply or I miscommunicated. How do I do that when it is the tables that I want to select; that is *all* tables that are called xyz$variable, where variable is unknown but all tables begin xys$? TIA, Victor On Dec 31, 2007 3:59 PM, mos [EMAIL PROTECTED] wrote: At 12:51 PM 12/31/2007, you wrote: Hi; Is it possible to sort tables within a given database? How? TIA, Victor Victor, You mean physically sort the table based on a field or key so you don't have to do an Order By clause each time you do a Select? Not really because the order of the table is expected to be random unless you specify an Order by clause. The only thing I can think of is to create a new table, maybe temporary or Memory table and copy the data into it already sorted. drop table if exists newtable; create newtable like oldtable; insert into newtable select * from oldtable order by col1, col2; Now you should be able to Select * from NewTable; without sorting (if you don't update it). The order should be by col1,col2. (No guarantee) If you want to sort it in order to speed it up, then run an Optimize on the table. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Migration Path / Activity List?
Hi Luis, On Jan 2, 2008 11:12 AM, Luis Motta Campos [EMAIL PROTECTED] wrote: Baron Schwartz wrote: On Jan 2, 2008 10:04 AM, Luis Motta Campos wrote: Baron Schwartz wrote: What are the biggest changes you anticipate? I'd say they will be the version upgrade, converting to InnoDB, and using replication. It looks like you have planned well for all but using replication. That's interesting. What kind of activities (besides configuration, maybe?) are interesting to add? Lots of experimentation! Let me ask it another way: how are you planning to use replication? -- for load balancing/scaling reads, backups, a hot standby machine? OK, I guess a bit more of information can help. I have a very sensitive database at the company [snip] The master/spare and slave/spare machines would be connected through heartbeat and will keep the database in a DRDB filesystem. This will guarantee that, in case of failure, the other machine can raise the same IP address in it's own interface and continue operations after a short delay. I suspect that this won't go smoothly on the first try, and if it's very sensitive... No offense, but you're talking about a lot of complexity here, and you're not familiar with replication yet. DRBD, failover etc are even more complex. I personally would hire an expert to help me plan and implement this upgrade. MySQL AB has consultants, but there's also Pythian, Proven Scaling, and Percona, to name a few whom I think are very familiar with the tools and technologies you're talking about. (Why do the consulting companies all have names that start with P, I wonder?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with mysql 4.1+suse 10
hi, i got a problemas in mysql with suse 10 enterprise server, the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1. when i treat to create a new data base, appears this message. please i need help. thanks.
Re: problems with mysql 4.1+suse 10
mario henriquez wrote: hi, i got a problemas in mysql with suse 10 enterprise server, the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1. when i treat to create a new data base, appears this message. please i need help. thanks. what exact command do you issue to create the DB ? Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database Cleaner
Do it anyways. Release it as Open Source. IBM is big into the FOSS community, and I seriously doubt they will persue this. Just because they have a patent, you can always build a better mouse trap. You are allowed to improve upon an idea that is patented, or do it a slightly different way the patent states. Honestly, sometimes ignorance is bliss. It might have been a better idea to NOT research patents -- it's not like you were going to build a company around this little script. -Original Message- From: Kugel,Miriam [mailto:[EMAIL PROTECTED] Sent: Saturday, December 29, 2007 2:29 PM To: mysql@lists.mysql.com Subject: Database Cleaner I am thinking to write a database cleanup tool. According to my research they are many application specific database cleaners but not a general one. I found a patent: US Patent Number *07188116* - METHOD AND APPARATUS FOR DELETING DATA IN A DATABASE As far as I got it I cannot write my tool because I am infringing this patent. Just unbelievable Any idea would be welcome. Miriam [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Cleaner
Can you say codefendant? David T. Ashley [EMAIL PROTECTED] (This e-mail was sent from a handheld wireless device.) On Jan 2, 2008, at 5:20 PM, Daevid Vincent [EMAIL PROTECTED] wrote: Do it anyways. Release it as Open Source. IBM is big into the FOSS community, and I seriously doubt they will persue this. Just because they have a patent, you can always build a better mouse trap. You are allowed to improve upon an idea that is patented, or do it a slightly different way the patent states. Honestly, sometimes ignorance is bliss. It might have been a better idea to NOT research patents -- it's not like you were going to build a company around this little script. -Original Message- From: Kugel,Miriam [mailto:[EMAIL PROTECTED] Sent: Saturday, December 29, 2007 2:29 PM To: mysql@lists.mysql.com Subject: Database Cleaner I am thinking to write a database cleanup tool. According to my research they are many application specific database cleaners but not a general one. I found a patent: US Patent Number *07188116* - METHOD AND APPARATUS FOR DELETING DATA IN A DATABASE As far as I got it I cannot write my tool because I am infringing this patent. Just unbelievable Any idea would be welcome. Miriam [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem with ~0.5 GB tabel
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, I've learned a bit about the environment this server is running in. It's VMware with root NFS and storage NFS mount points for MySQL. I've been told the throughput over NFS for my Server is from 20 to 30 MB/s. The server has 3GB ram. I'm not sure about it's CPU performance, but the information I've is that when connections start hanging and the effect basically multiplies, the CPU load goes aup. Does this sound like a possible bottleneck? thanks, - - Markus Markus Fischer wrote: Hi, I'm using phorum [1] and made some custom queries against their database. My query looks like this: SELECT message_id, subject, datestamp, forum_id, thread FROM phorum_messages WHERE forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND thread != 0 AND status = 2 AND closed = 0 ORDER BY datestamp DESC LIMIT 3 The table phorum_message is about 500MB in size. The problem is that such a query often starts to hang in the Sorting result phase. This can take up to minutes and during this time problems really start: more and more such queries are coming in, each of them hanging for the same reason too and after a few minutes the maximum of connections are reached (currently 170) and everything is dead. Only killing the queries manually helps. My guess is that the filesort is problematic and so I tried to avoid it with the following things. When I use explain on the query I get back the following: id: 1 select_type: SIMPLE table: phorum_messages type: range possible_keys: thread_message, thread_forum, status_forum, list_page_float, list_page_flat, dup_check, last_post_time, forum_max_message, post_count key: post_count key_len: 9 ref: NULL rows: 1311 Extra: Using where; Using filesort When I remove the ORDER BY statements, the query is *not* using filesort. However, as you can guess, it is necessary. The goal of the query is to get the top-most posters in the selected forums. The MySQL documentation [2] says that under certain cases it should be possible to create appropriate keys so that even an ORDER BY can take advantage of, but I was unable to come up with such an. Is there a recommendation how to go for it? thanks, - Markus [1] http://www.phorum.org/ [2] http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHfCl+1nS0RcInK9ARAqEaAJ9JsofQIzoVBfCJQRKE/8X6wW1/SwCg0+en 0HDQBTAB4U87Nuua/h4pDiU= =utDe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Migration Path / Activity List?
I know now how to reply to all mail list
On Jan 2, 2008 9:53 PM, Luis Motta Campos [EMAIL PROTECTED]
wrote:
Hey there
I have a not-so-big (50GB, including indexes) database (MySQL 4.0.24,
mixed MyISAM and InnoDB tables) that I need to migrate to a MySQL 5.1
Master + Slave Replication server, with InnoDB tables only, over a new
hardware.
Unfortunately, this is the first time I play a role as MySQL DBA, and
don't have a complete migration plan (yet).
Can someone here please review my migration plan and help me
adding/prunning missing/exceeding bits?
My current plan is more or less like below. I removed task descriptions
removed, guess the task name is descriptive enough. Indenting marks
sub-tasks, as expected.
Many thanks in advance.
Cheers!
- BEGIN TASK LIST -
Database Migration Project
Current Database Procedures Mapping
Plan Database Test Procedure
Determine Current Database Backup Procedures
Determine Current Database Restore Procedures
Obtain Copies of the Current Database Backup Scripts
Obtain Copies of the Current Database Restore Scripts
Determine Current Database Stored Procedures / Triggers
Database Statistics Collection
Determine Current Database Size
Determine Current Database Grow Ratio
Determine Current Database Schema
Operating System Installation and Configuration Plan
Determine Hardware Requirements
Determine Operating System Requirements
Determine Required Operating System Configuration
Plan Operating System Installation
Plan Operating System Configuration
Document Changes on Operating System
Database Software Installation and Configuration Plan
Study Need for Database Build
Plan Database Build
Plan Database Installation
Plan Database Configuration
Database Performance Tunning
Determine Current Database Queries
Determine Current Database Performance Indicators
Plan Measurement Procedure for the Performance Indicators
Measure Current Database Performance
Database Schema Refactoring
Obtain Current Database Schema Definition
Review Database Schema
Review Database Stored Procedures and Triggers
Suggest Changes for Database Schema
Study Desired Database Backward Compatibility Level
Design Views to Allow Database Backward Compatibility
Redesign Database
Write SQL for Changes
Staging System Deployment and Testing
Install Operating System in the Staging Environment
Configure Operating System in the Staging Envinronment
Build Database Software in Staging Environment
Install Database Software in Staging Environment
Configure Database Software in Staging Environment
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Fix or Improve Restore Procedure
Test Database in Staging Environment
Measure Staging Database Performance Indicators
Compare Performance Figures for the Staging Database
Production Database Deployment
Plan Database Maintenance Stop
Advertise Database Maintenance Stop
Install Operating System in the Production Environment
Configure Operating System in the Production Envinronment
Build Database Software in Production Environment
Install Database Software in Production Environment
Configure Database Software in Production Environment
Stop Production Database
Obtain Current Copy of the Database Backup from Production
Restore Database Using the Restore Procedure
Test Database in Production Environment
Start Database in Production Environment
Database Monitoring
Follow Up Database Behavior and Performance
- END TASK LIST -
--
Luis Motta Campos (a.k.a. Monsieur Champs) is a software engineer,
Perl fanatic evangelist, and amateur {cook, photographer}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
