MYSQL :: Join Tables On A Large Database 200 Meg

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

View Replies !

Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing

View Replies !

Equi-join On A Large Table?
I'm running on MySQL 5.x

Let's say I have the following table of data:

Code:

Table: test
USER_ID|CLASS_ID
12
13
14
22
24
32
34

I'm trying get all the classes that these users have in common. E.g. If i ran a query to do this I would get class_id 2 and 4, because those are the only classes that all three users have in common.

I think I want to do a equi-join, something like:

Code: ....

View Replies !

Optimizing Join W/ Large Table
Here is a high level description of my tables

I have a table called Items
items are grouped by an ID, but it is NOT unique
I have an index on ID for the Items table

and a table called CompletedItems
completed items consolidates information about the items, and here the ID is unique.
I have unique index on ID for the CompletedItems table

What i need is a query that will return all item id's which have not been completed.

I have this query:

select distinct(items.id) from items
left join (completeditems) on
(items.id = completeditems.id)
WHERE completeditems.state is null;

this gives me the data I want except there are two problems

a) this query takes a long time to run.
while it is running, the completed items table is locked, which causes several other processes to block while this is happening.

to give you an idea of scale, the items table has about 20 million records in it, and the completed items has about 4 million.

View Replies !

Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username
FROM (( " . phpbb_forums . " f
LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id)
ORDER BY f.cat_id, f.forum_order";

Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions?

The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?

View Replies !

Large Tables
So i've got a table with 15 million rows. id is the primary key

SELECT COUNT(id) FROM listings WHERE id != 0;

Takes about a minute to load... which is insanely slow..
anyone know what could be causing this?

View Replies !

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

View Replies !

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

View Replies !

Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life:

If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient?

The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key.

The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?

View Replies !

Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user.
Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details.
Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?

View Replies !

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

View Replies !

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

View Replies !

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.
This is very new to me and I am noticing that my queries are really slowwwwww!
What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

View Replies !

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

View Replies !

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

View Replies !

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

View Replies !

Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size.

The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table.

But the question I have now, can unused large tables affect the perfomance of Mysql?
I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )

View Replies !

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

View Replies !

Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems?

as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?

View Replies !

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

View Replies !

Random Records :: Rand() On Large Tables?
Im told that ORDER by rand() isnt a good idea to use on large tables, so Im interested to know how else this could be done. I need to display 20 random records (there are conditions in the WHERE clause as well).

View Replies !

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

View Replies !

Create Table Syntax For Large Tables
How do i create tables with sizes larger than 4 GB? Do I need to specify any additional clauses such as heap etc when creating tables?

View Replies !

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

View Replies !

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

View Replies !

VERY Large Database
I have such question - how I should split large data for the better performance?
Its seems to be old question, but can't find it in search

Lets say if I have 10000 items, each have 10000 records - at the start and much more later.
Main operations - INSERT/SELECT (search for the records on some item by key)
DELETE/UPDATE almost will not be used (because of the implementation)

I see few possible options
1. Create 100 databases, in each 100 tables, in each 10000 records (records for the 1 item).
2. Create 1 large table with internal index.

As I understand, main difference between 1 and 2, that in case 2 main load will be drived to the mysql and index, in case 1 main load will be drived to the FAT (or whatever else used at the server).

But before mysql will use index large file (or its index) will need to be loaded, so in case 2 FAT will be loaded too.

IMHO option 1 is better from performance side.

View Replies !

Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database.

The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles.

I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field.

I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.

View Replies !

Import Large Database
I saved my website databgase to my home computer. I am setting up a local
version of the site, and need to import the database. I have mysql and
everything set up.

But when I try to run the sql script that I exported from
my site's control panel, I get an out of memory error. What is the proper
way to import this database? Do I need to increase some memory setting?

View Replies !

Large DataBase Columns
I am developing, what i consider to be, an extremely large database, using mysql and php.
im concerned that one of my sql tables will be too large.
tonight, im running some tests, but i was hoping the forums
could toss me some good advise.
The table will be around 1500 columns long, and
about 500 rows on average.
each column will not be any larger than 20 varchars.
i've never created anything beyond 30 columns.
is this beyond what's recommended with using mysql ???
i know the queries may take a little bit to load, but is
this simply a bad idea anyway, or am i just getting nervous
for no reason at all.

View Replies !

Large Database Table
I am using MYSQL in order to develope a database table that currently has about 20 million records. The table grows on a daily basis and is expected to grow to a size of about 300 million records.I wanted to get some suggestions in order to handle such large database. What kind of indexes are best? When should splitting the table be considered? What are ways of achieving good performance of queries?

View Replies !

Large MySQL Database
There are many threads on here about large db designs etc but I'm looking for a little information I have been unable to find anywhere.
What sort of design to places like facebook/myspace use for their database(s).
Is it best to have a table for user info (id, username, email, etc) then have a table for every feature of your site (blogs, pictures, friends, comments etc etc) or do they have a table for every user.
I would assume the first as a table for every user would soon grow very very large and become inefficeint.

View Replies !

Split Large Database
I made a backup from cpanel and install a forum and website in localhost, with easyphp. Everthing works fine, but i need to install using pypmyadmin my database.
But phpmyadmin only import files at 8 Mb. My database are larger, almost 20Mb. I would like to know how to split my database in small files, in order to import from phpmyadmin. Maybe exist a software with a grafic interface?

View Replies !

Reduce Large Database
I have run into some issues as of late.I have a php program that stores information 4 times / day for multiple items in my database. This has been running for quite some time now and there are many, many records in the database.
The records are the same most of the time for each 'item', but it is used to archive information. At this point there are so many records that when I run queries from either PHP or a .NET application, I am receiving errors (which I think is really just the database timing out, but giving me different errors instead). I think this because when I run a smaller query, the code runs just fine.
So, I wrote a program in .NET that goes thru the whole database on an 'item' basis, iterates through and compares, if the current item is identical (besides timestamp) to the previous, then the current is deleted. This would probably move the records down from about 2 million to about 1000. However, this query will not run because the database is just too big (that's my guess)

View Replies !

Movement Of A Large Database Over A Wan
I have a need to transfer a 80GB live database over the Internet (one colo to another colo, no VPN's established). Then keep it consistent up to the colo cutover date.

I understand how to setup the replication once the initial copy is made, but my problem is over how to get the initial backup from colo1 to colo2....while maintaining the ability to keep both databases consistent.

My initial thoughts...
Can one lock the master DB, take a backup, establish SSH replication between the two servers, transfer the backup file (WAN, FedEx, etc..), and then import the backup file once it's transfered from A to B.

From how I see it, the DB will be taken down in a consistent state and replication will run for all new transactions. I'll miss all the historical data, but that will be solved once the backup is transfered and imported.

View Replies !

Large Database In Mysql
I have a database with two tables in MySQL 4.1. Die one tabel contains about 300,000 records and the other table contains about 70,000 records. I tried joining and subquering (very simple sintax) these two tables and received no result. The processor of the server was being tied down. I went to a bigger computer with the same results. I then tried minimizing the tables and had to go to about 10000 and 8000 records respectively, before it worked.
I did not try indexes, because I wanted to make a comparison between no indexes and with indexes. Granted I did not tweak many server variables, but shouldn't this
just make the query slower and not completely tie it up?

View Replies !

Is A 100mb Database Large Enough?
I run a forum on www.TheFloorPro.com and just added a gallery add-on. Images are stored on the server as files rather than in the database, but I am still concerned about the database size.

After doing a database backup today, I worry that my allowed 100mb database will become too small in a short time. It is already at about 10mb in just over 2 months time. My host is 1and1 and they allow 100 databases of 100mb each, which I don't understand. Don't know why I would need a hundred of them and don't know why the one or 2 that I might need are restricted to 100mb.

View Replies !

Question About A Large Database
a quick overview of what I'm trying to do. I have a database with about 3.5 million unique items. There is a table that covers which categories each item belongs to. The average item belongs to 3 categories.....the categories are used for navigation on a website.

The question - is it better to split each entry for each category into a its own row (~10m rows) or to have each unique item and list all of it's categories in a delimited fashion (category2323|category23489|category382233) in a single row for each category and only have around 3.5m rows.

View Replies !

Uploading Large Database.
I am trying to upload a large mySQL database of 120mb.

the file was downloaded from cpanel using the backup facility and is called forum.gz.

I have placed it on the root of my server for the time being.

View Replies !

Uploading A Large Database
Can someone please comment on how to host a large database - a database of about 500 MBs. Is a database of this size going to work on a shared hosting environment or is a dedicated server a must? And, how would you go about upload or importing the database to the host? I could be wrong, but I don't believe phpMyAdmin will allow importing a file over 50 MBs.

View Replies !

Large Database Problem
I am developing a PHP & Mysql application. I am using one big table to collect data and some other small tables. The problem is that with the growing number of the rows in the big table my application is getting slower and slower.

The application executes a lot of INSERTS almost all the time ( while collecting data). And then it calculates and summarize the data into statistical reports (this is done on demand at realtime). I opimize my queries with EXPLAIN as much as I can and rewrite a lot of them and it became faster but only for some time.

I read about the MyIsam storage machine that its locking mechanism is on table level so when an INSERT is executing the table is locked for SELECTs so they have to wait. If that is so is there any solution to this problem?

As more of the SELECTS are used to fetch number of rows or other statistical information I am thinking of writing a module to proccess and calculate the data (for example once a day) and to store it in summary tables and then fetch the summarized information for the reports. Is the a correct approach or its a waste of time?

And there is one more question. Accept for the summarize data one part of my application uses also the raw data, usually a small part of it (let say 100 rows). I was thinking with the processition and calculation of the data to perform also dublication of the information (maybe optimize it a little bit) in another table to use later for that kind of queries. Do you have any other suggestions. (A couple of month ago I tried to summarize the data while inserting it but it wasn't a good idea)

My table right now has 67000 rows and it works just fine when I am summarizing on about 10000 rows but it is geting really slow for 25000 rows. I expect to reach 50,000,000 rows for an year and the reports are going to be on 3,000,000 rows at least.

And some more information to help you help me . I execute each of the queries alone and they work fast but some of my reports (web pages) need to exequte 10 queries and it takes 10-15 seconds to load. All of the reports are made depending on a selected timeframe so do you know any working schema about storing data depending on time.

View Replies !

How Large Can Be A Mysql Database?
I have a q:

how large can be a mysql database in order to operate in optimal conditions?

View Replies !

Migrating Large Database
We've recently upgraded our DB server, and I now have to move our fairly large (10GB) DB to the new server. The DB consists of both MyISAM and InnoDB tables. The largest table is a 9GB InnoDB table with binary image data.

What's the best way to a) back the data up, b) migrate the db to the new server?

View Replies !

How Do I Backup Large Database?
I have a forum with over 130k members and the database is quite large. I'm on a shared server through hostmonster.com

My question is, how do I completely backup this forum database so if anything ever happens, I can restore every single detail.

Is there a way to do so that doesnt require codin or anything?

View Replies !

Large Database To Handle
I need to store more than 500 million records in a single table. These are basically historical data of different stock market for last 40 years.
Initially I thought about partitioning for each company. But there are more than 1024 companies. At the same time if I partition the database based on year then to show the chart for a particular company I need to call almost all the partitions.

View Replies !

Large Database & Distinct Values
I have a rather large database table that has a column with maybe 20 different values repeated on and off throughout the database (around 4000 records total). Could someone shoot me a query example of how I can pull each value from this column once to create the list of the 20 or so reoccuring values? Lets call the column work_type. I think I'm maybe just missing knowledge of a particular query command for the WHERE portion maybe?

View Replies !

Changing Field Of Large Database
I have a fairly large database of aprox 7 million records. One of the columns is of type TEXT and I was wondering if it would be possible to change this to MEDIUMTEXT or LONGTEXT without complications?

Is this doable or is it discourraged to perform this operation on such a large database? The field is not indexed, it's simply there containing text and now I want to stuff even more text into it.

View Replies !

Large Database With Many Concurrent Users
We are looking into how to setup MySQL to have the best performance for mostly reads (around 70%) and some writes (around 30%). The database size is expected to start around 200GB for the first year and expand to around 1.5TB in 4 years. There will be around 350 concurrent users starting off, and it will expand to around 700 concurrent users within 4 years. It seems that clustering is out of the question due to RAM requirements.

One scenario I thought about involved having around 5 servers with replication setup to store a copy of the DB on every server. Another way involves a SAN, but I'm not sure how MySQL supports a SAN to store a DB that size while allowing just one copy of the DB to be located on the SAN. In other words, all MySQL servers access one DB on a SAN. Does anyone know which scenario would work best, or if there are better alternatives out there?

View Replies !

Making Large Test Database
I was using mysqldump to handle my backups. I had a script that ran nightly and would dump my tables and copy them over to a directory, which then got backed up.

This worked great when our DB was only 400mb. We have since grown past 2Gb and my mysqldumps keep losing the connecton the mysql server. I'm not sure if this is due to the size of the db, mysql, or scripts timing out.

To test, I'd like to create varying sizes of dbs from 2gb to 10gb to test backups with my forseen growth.

How do I create a db with erronous data that isn't customer information to test?

We aren't using innodb exclusively, so we needsomething that can make a mexed up db.

View Replies !