MYSQL :: Huge Site

Huge Pagefile
Am running mysql 5 on a win2k server. Installed with defaults, no changes. Have 8 databases running in the single instance. Have 2GB ram, 2 2.8GHz processors on the server and after about 2 weeks of running have a 1.8GB pagefile. Neither of the CPUs is running higher than 5% with peaks at about 30%. The database is supporting multiple websites with moderate traffic. The biggest table in each of the databases has only about 35,000 rows.

Needless to say, the bigger the pagefile gets, the slower the respnse time. Any ideas on what settings I can make to keep the pagefile size down? Right now am rebooting the server once a week, but that's just a band-aid..

Huge Log Files
The database is completely backed up and nothing has been lost; the problem is the giant log files MySQL creates ate up our available disk space (all MySQL queries are logged, and with that worm hitting our forum we probably had about 8 gigabytes of logs).

I deleted them all, but for some reason most services (e.g. MySQL, FTP, email) continue to report the disk as full, while I am free to copy files all over without problem using SSH. "du -h" executed in / indicates that we now have used 800 MB of our 10 GB disk usage quota.

The most detailed error message I get is "/jail1: write failed, file system is full";

Huge Table
I have a table with over 500k customers in it. When my program loads the select statement takes so long the user never gets fully logged in. Is this the right forum to post a select statement for possible advice? It is a mySQL database.

Huge Table
I want to create a table to store tens of millions of records.
Is my create table statement suitable for the job?

CREATE TABLE `table_format` (
`id` int(11) default NULL,
`name` varchar(100) default NULL,
`addresses` char(200) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 AVG_ROW_LENGTH=200 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED

is fixed row format making average row format parameter obsolete?

Huge Server Configuration
I have a server like 8way Intel Pentium 4 Xeon processor with 12GB RAM and 1TB harddisk space. All the tables size are over 10GB and they have over 100mm records.

Could some one help me get an appropriate mysql configuration(my.conf)file for the machine.

I understand ther are lots of factors depends on it to get a steady working configuration. All I need is some model configuration. I think later on I can tune thar up.

Very Huge Database In MYSql
I am having very huge database (12GB) in mysql, with php i am using this data but i am not able to retrieve it. Itâ€™s giving time out every time because of its size. Is there any way to optimize this data?

Huge Database Problem
I have a database with 8.500.000 (aprox.) rows and
I want to select a row like this:

SELECT *,((DEGREES(ACOS((SIN(RADIANS(36.517)) * SIN(RADIANS(f4)) + COS(RADIANS(36.517)) * COS(RADIANS(f4)) * COS(RADIANS((-4.883)) - RADIANS(f5)))))*69.09)*1.6) as km FROM locdb order by km limit 0,1

(I've divided the lines for better reading). The syntax selects the nearest city acording to the latitude (36.517) and longitude (-4.883). The latiude and longitude colums on my table are f4 and f5, respectively.

The problem is that (obviously) it takes about a minute to do it. Has anyone an idea how to improve the table or the syntax to get a better results?

SELECT On A Huge Table
I am using a 512MB server with a mysql 4.1.13.

I have a table with 5 milion row ( table has a varchar 255 ). I need to download all the rows into memory ( from a C client ).

After executing the SELECT query, I use mysql_store_result to take the data, but after one or two minutes, the server crashes and restarts and the clien connection is lost.
I tried to use mysql_use_result, but strangely, the client doesn't load anything with that :/ ( it returns immediately )

In both cases I use mysql_fetch_rows to load de resultset.
I have tried to put max_allowed_packets to 200MB, it doesn't change anything..

Does someone have and idea to load such table without crashing the server ?

Copy A Really Huge Table
I have a really huge table that I need to copy however using the Export function in MySqlAdmin does not work because it "times out". All I really need to do is generate an SQL script that will, drop the table if it exists, create the table and its structure, and then insert the data line by line.

Usually when you perform a command in MySqlAdmin through Internet Explorer the MySQL command line is displayed with the results. However, this does not happen when you export.

Anyone know how to do this from the MySQL command line?

In a nutshell: I need to backup a table to an SQL script file.

Select With HUGE Criteria
what the best method is for doing a select where i have say 40,000 items of criteria.
eg select * from clients where 1 or 2 or 3 or 4 ... or 40,000.
I can buid the query up no problem using php, but the query would be huge. Is there a better way of doing this or should it be no problem for mysql.

Huge Server Load
This is the TOP stats from my vps:
http://img28.imageshack.us/img28/3528/clipboard014rg.gif
As you see server load is too much and its getting more then mysql service stops and everything is normal. This happens for 2 days, hosting company made a Linux kernel update maybe that can be the reason.
My VPS load was always too high and there is only 1 site got 10.000 unique visitors. I did so many optimizations on the mysql queries and also php. I cant afford a dedicated server at this time and I dont think it's not so much traffic.

Huge Problem With Mysql Locking Up
I was hoping for some urgent help. One of my websites is having big problems with MySQL. Its a popular website and MySQL hasn't been able to cope. The site recently froze, here is a processlist from the time it started to freeze, its very messy:

http://www.trailblasters.co.uk/sitepoint/main.htm

The query that shows up the most has been removed and the site is running ok again but I sense it will freeze again at some point. UPDATES are run very regularly, could this be the problem? How can I overcome this issue?

MYSQL Causing A Huge Load
I just got my new VPS with liquidweb up and running and I'm experiencing a huge amount of load, a lot i think is due to mysql queries, so I think I'm going to need to do some serious optimization of my queries. I'm pretty new to this stuff.. Can anyone make sense of this SHOW STATUS and SHOW VARIABLES info, and let me know what the issue may be?

SHOW STATUS
Variable_nameValue
Aborted_clients0
Aborted_connects0
Binlog_cache_disk_use0
Binlog_cache_use0
Bytes_received13775592
Bytes_sent180075883
Com_admin_commands541
Com_alter_db0
Com_alter_table0
Com_analyze0
Com_backup_table0...............

Huge Databases And Nightly Backups
We have a huge database of several gigs. During our nightly rsync backup, performance is terribly degraded. Anyone have any hints on how to alleviate this?

Huge Single Quert Vs Forloop
i m writting a complex query which link up bout 7-8 tables. By now, i cant see any slowdown on the query coz the db stil fresh. But i knwo this query wil going to slow down my system when times go by.

I also know i could optimize the table and i do try my best on it.

So i wonder should i modify it into smaller, less complex query, which can use a forloop to loop the smaller query and get back the same result.

Question:

- forlooping the smaller query is the best solution to avoid a single-complex query?

- I know the less-complex query wont bang my server in one shot(complex query). But will forlooping it giv me a worse result?

One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ?

ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?

Altering Huge Innodb Table
I need to add an index to an Innodb table 110gb big. When I last tried it took forever and made the db unusable to visitors.

My solution was to create another table with the same structure with the extra index and copy everything over with a php script. This is working so far but is taking a long time and still hitting the performance for visitors. Is my solution the best way or is there another?

Huge Database With 800 Customers / 3.500 Products
I need for a webshopproject all customers with all products in one
file. I have already both informations in two different ones. Pls.
find below the relevant info.
product
ProductID|pricegroup|
customer
knr|pricegroup|%calc|price

It is possible that one product is related to 800 customers with
800 different prices. In my thinking the new DB should look like
pricefile
ProductID|knr|pricegroup|%calc|price.

Huge Performance Difference In Two Forms Of Writing A Subquery
I wrote the following subquery to return the names of people that have a phone number in common with someone else:

SELECT Name
FROM People
WHERE Phone IN (SELECT Phone
FROM People
GROUP BY Phone
HAVING COUNT(*) > 1)
The table has about 100K rows and this takes about 20 seconds which does not make sense because running the subquery by itself takes only about 0.20 secs and the main query about that much if I enter the result of the subquesry manually (10 rows returned). So I would have expected this to run in 0.40 seconds and not 20!

So MySQL must be doing something wrong here, interpreting this the wrong way or something or somehow running the subquery multiple times? Though I can't see why.

Playing around with this a little I then (almost accidentally) found that if I rewrite the above as:

SELECT Name
FROM People
WHERE Phone IN (SELECT Phone FROM (SELECT Phone
FROM People
GROUP BY Phone
HAVING COUNT(*) > 1) InnerTable)
Does indeed run in only 0.40 secs!!! Now I can't figure out why these two forms should behave so differently. I can't understand why MySQL would interpret the first one in any other way than the obvious and even more puzzling (to me) that the second form fixes the problem.

I am happy to use the second form but I would also like to understand why this difference. The only other problem is that the second (and faster) form is not allowed to be written as a View, it complains that the View's SELECT contains a subquery in the FROM clause.

Here are the explain plans for the two queries (I left out the columns that were NULL for all rows):

First Form (SLOW):

+----+--------------------+--------+------+--------+----------------------------------------------+
| id | select_type | table | type | rows | Extra |
+----+--------------------+------- +------+--------+----------------------------------------------+
| 1 | PRIMARY | People | ALL | 108423 | Using where |
| 2 | DEPENDENT SUBQUERY | People | ALL | 108423 | Using where; Using temporary; Using filesort |
+----+--------------------+--------+------+--------+----------------------------------------------+
Second Form (FAST):

+----+--------------------+------------+------+--------+----------------------------------------------+
| id | select_type | table | type | rows | Extra |
+----+--------------------+------------+------+--------+----------------------------------------------+
| 1 | PRIMARY | People | ALL | 108431 | Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | ALL | 3 | Using where |
| 3 | DERIVED | People | ALL | 108431 | Using where; Using temporary; Using filesort |
+----+--------------------+------------+------+--------+----------------------------------------------+
Note that there is also a time difference to come up with each plan, the first one (SLOW) takes no time (0.00 sec) while the second one takes 0.20 sec, not sure if this matters or is in any way significant.

Config Mysql For Huge Data But Single User
IÂ´m new to mysql and want to know how I set up the my.ini for best performance.
IÂ´m running a PC with dual core and 3GB RAM, Windows XP, XAMPP
I want to configure mysql for best performance for a singele user acess.

I just want to do some sql-query on a huge database (25GB) - only one query at once.
At the moment my query nedds about two hours....

Site Down
Need help figuring out ohow to unlock tables.
Everytime I try a repair with my Web Host Manager my whole site freezes up.
I have one table that is "in use" a sessions table.
I can use putty and get to the
>Mysql prompt but I am lost from there...
Is there a way to repair or fix this? I have tried using phpmyadmin and cpanel and neither work, they just time out....
what command should I use?

MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same).

My server specifications:
Red Hat 9.0
Pentium 4 3.0 GHz
2 GB Ram, Burst RAM 5 GB
Running webserver, mail server as well.

Is there a way I can view the minute process details, as what table record is being updated ?

Connect To My Db From Any Site
Can I connect to my db (via php scripts, etc) FROM a client's website ??

Web Site Form
I want to have a form on my website, almost like a feedback form but not for feedback. It will be for getting info from customers. It will simply be about 30 fields that will need to post to a database. How hard is this with mysql? How would I do this on the web page? Is there a certain language I would need to use? Also is it possible to get an email everytime a record is added via the web form?

Search From Web Site
I have a simple search function. Just an input field and a submit button that should search a table in mysql.

I do some regexp stuff in mysql so i use the REGEXP syntax but you don't have to bother about that, since we'll just asume that the user have input "x y z" in the search field.

Problem 1
I have three fields in a mysql table that i want to search. The simplest way I came up with to do this was:

SELECT * FROM table WHERE CONCAT_WS(' ',field1,field2,field3) REGEXP 'x y z'

But this won't work if field1 = z, field 2 = y and field3 = x .
How can I achive this? It's quite impossible to ad an OR statement for every possible combination of x, y and z. (perhaps I will need to search more fields in a go in the future)... Can I change the regexp to be able to match words in any combination?

Problem 2
How do I set up an index in mysql for the kind of search mentioned above? Is it usable to have an index on field1, field2, field3 if you're going to do a concatenation on them anyway?

Search My Site
All my documents and archives are in mysql database.
How do i setup that i can search my website?

MySQL On A Web Site
I am working on a web page that is currently using a MySQL database to manage our product inventory on our web site.The problem is that I am not the person who developed the web page, but it is now my responsibility. I am going to continue using the existing database, but the management process of a MySQL database on a web server is a new process for me.I am going to assume that I should be using the phpMyAdmin tools to interface with the database, but the php programming is new to me, as well.

I'm a fast learner, so what I'm really looking for is a bit of direction in how I can improve or reconfigure the output for the end-user.Anyway, I don't know if I have provided enough information, but if any of you guys can point or steer me in the direction that would make me an educated contributor to these forums, I would appreciate the introductory patience.

Multilingual Site
like...should i just have a base price and calculate the rest when needed?

i use php, mysql and mod_rewrite

Connect My Site
Im trying to connect my webpages to the MySql database that i have as part of my hosting account, ive dropped a MySqldump file into the database and have been trying to connect my site to the database but im somewhat confused about how to do this, i understand ive got to use a little bit of code to make the connection but where do i place that code? and do i place it on every webpage, im trying to put together GET variables so that when someone clicks the "Buy"
link it should then make the billing popup appear, but mainly at the moment my main confusion is about actually what to insert and where, in order to make the connection between my pages and the database

Remote Site.
i jus completed my first database site and ready to upload. but need to know 2 things.
1. where exactly is the database? ive got my site in the inetpub.. is the database in here C:Program FilesMySQLMySQL Server 4.1 ?
2. how do i upload it along with the site? is there just 1 file to upload or is there lots... ?

Building A 1 Page Site?
I have a couple of review site and I'm thinking of building a new one, but along the lines of a 1 page (well, maybe 2 or 3) site. I think the database is pretty basic and the queries dead simple (okay, except for the "I'm thinking about")*, but I'm concerned about 2 things.

1 - site simplicity and fast page loads
2 - some duplicate content

The reviews will have 2 separate text fields.

One will be a description written by me .. unique text.

Another will be a review = customer comments supplied by an affiliate .. duplicate content.

Each text field (description and review) will be up to 10k in size. The database records will also have fields for : name, image link, affiliate link, and a few more .. very brief. Probably a total of 10 fields.

What Im wondering:

1 - what problems should I watch out for, if any, by adding this much text. *I'm thinking about making the first query show up to 10 or 20 items with just maybe the first 30 or 40 words of each text field plus a thumbnail; and then a link in each that would produce a single page with the full text and larger image.

2 - I'm a little concerned that I may get a duplicate content penalty. Will having it in a database help? I'm pretty sure I could put the 'echo' in an iframe and the iframe in a directory and exclude SE bots from that directory with robots.text .. does that make sense?

Finally, there are hundreds of items so I plan on breaking them into categories to keep the menus a reasonable length .. and using javascript to to show an *items* menu when the *category* menu is selected. So should I go ahead and use AJAX to query the database?

Compute Time On Site
I have moderators and i need to compute their time spend on site. I have table moderators_actions(mod_id,action_date). I make up to count sum periods of time between moderate_actions, but only them which are shorter than 5 minutes(if it is longer than 5 minutes I assume the moderator made a break in his work).

My queries:

1. The query finds for each moderators_action next action of this moderator, and time between the action. Choose only shorter than 5 miunutes.

One Database For Site And Forum?
I have a site and a forum and I want to use one database for them, so when somebody registers in the site - to be registered automatically in the forum.
Is this possible and if it is - how?

SIte Search Engine
I am currently working on a site which visitors can submit & search links to various resources. I am trying to set up an advanced search feature (similar to google) and I have several ENUM Fields within the table that I want visitors to be able to check or uncheck within the search.

I also have a username table and would like to be able to measure the popularity of a link so that I can have rankings for different types of resources and count the numbr of times a user submits a link, which user submits which link, number of times a user clicks on a link etc. I am v. new to database design and am not sure whether the ENUM fields should be in a seperate table or whether to keep it as it is: Code:

AIX 5.1 Binary From UCLA Site?
I wonder if anyone would share your experience with the binary download from the UCLA library? I am thinking of an AIX 5.1 box (32 bit) first. If things go well, and if manager allows, I'd like to try another box (AIX 5.1, 64 bit). It'd be nice to hear a success story before taking the jump.

Also, is UCLA's binary the same as the one at mysql.com site? If different, in what way? Is one of them 64bit and the other 3 2bit? Or are they both 32 or 64 bit?

Porting A MySQL Site
I am creating a site here on my local machine, mainly to learn Mysql and php. I am using Mysql 4.1.10, php4, apache 2.0 and windows xp home. After I get an appplication built and running well, what am I going to have to do to make it run on the internet?
Will I simply have to change all occurances of 'locahost' to 'mydomain.com' ?

Cms Site Lo My Local Server
i'm having problem running my cms in my local server, i've downloaded our company sms from our remote webhosting server and tried running it in my local server but a lot of errors popped-up? like:
in index: Notice: Undefined index: pagsanjan_cookie in C:Program FilesApache GroupApache2htdocspagsanjanindex.php on line 568

and what's worst is as i logged in try to modify contents through its php cms alot of errors would pop up like: Notice: Undefined index: fcategory in C:Program FilesApache GroupApache2htdocspagsanjanDynamiclist.php on line 65

Notice: Undefined variable: sqlstring_temp in C:Program FilesApache GroupApache2htdocspagsanjanDynamiclist.php on line 118

Building My First Database Driven Site.
Before I begin, can I just state I have never installed anything on Linux server, so I am very new to this...

Okay at the mo I have and using MYSQL version 3.23.58 on my Linux Sever, incase this is important, heres the details of my server: WEB SERVEUR
Platform : Small - Fedora Core 3
Control panel : Plesk 100
Config : AMEN 2400 - AMD Athlon XP 2400+ Disk: 80 Gb - Memory: 512 Mb

OK, I need to upgrade to the latest MYSQL version. So far, I have downloaded "mysql-standard-5.0.24a-linux-i686.tar" and unpacked the file uner /usr/local

This is where I am stuck, I have no idea what to do next.. I need help for someone never done this before, preferbally a guide like... STEP by STEP, type this,

Linking Site To MySql Database
I have bought the PHP and Database book and have created a database [windows](saved under the default directory under Program FilesMySql...) and I have created a server. How do I link the website (localhost - same PC as one with MySQL) with the database or is it done automatically?

Transfer A Data Base To My Site
i've create a data base with phpmyadmin in my computer and now I want to put it in my site...it's possible to do it? And how?

Create Php Site From Existing Database
I have been contacted by one of my clients to construct a knowledge bank based on their ms access database. I have successfully converted the ms access database to MySQL using the GUI conversion tools supplied by MySQL.com . I am wondering, and I don't know whether this is the right site to do this, if I can easily create a php site based on the database tables that have been converted.

Capitilised Words (values Within Site)
I have have changed servers for my website and when I reuploaded my database backup some text within my site is capitalised. The text that is in highercase seems to be values for example the words I have noticed are SOME, TO, FROM etc it seems to be the same words.

here is another example: "This is Kidderminster's 4th annual Rock Festival, this YEAR taking place ON 28th September 2007, FROM 6:30pm until Midnight AT Kidderminster Town Hall IN the West Midlands."

Has anyone ever come across anything like this before? If I hav backed up wrong or imported wrong please could you provide steps with the DBtools program.

Db On Live Site And Replica On Development PC
I have a Db on my live site, I use DW to manage the site and phpMyAdmin to
manage the mySQL Db, and I'd like to do the following:

1. Replicate the Db on my Dev PC, to modify then reset the live version on
the site to match it.

2. Set phpMyAdmin config to look at the PC version of the Db when developing
and the Live version when its up on the site.

Hope these make some sort of sense - just new to the world of php and mySQL
(actually an MS developer).

MySql 5 On Hoster's Remote Site
I set up an application using Flex, C# web services and MySql 5. On localhost the app works fine. No problems.

However, when I uploaded the site to our hosters, C# can't find MySql db. I followed all the same directory structures layout as on the original.

Using MySql Maestro as my tool I can connect to the remote MySql db.

Database Design For Ecommerce Site
I am looking to design an ecommerce site with categorised products.
Once built, I want my client to be able to add additional categories and be abel to add their products into their chosen category.Would I be best displaying this dynamnically after creating a table with the products category names in it or doing it static?
Also, if I wanted to show a different header / meta tags for weach category how would I best tie this in?

Dating Site Zip Code Distance Question
I'm doing a few tests with a table of 10,000 bogus users and i'm comparing their zip codes against the the user searching and figuring out the distance using the formula you can find in these forums. Problem is that it is slow...especially when I'm joining other tables to the query.

Is there a better way of doing this? Maybe I should just search using the Metro area instead of the zip code. That would make quicker I think since there are far fewer metro areas than zip code entries.

How Do I Change The Date Of A Post On My Forum Site?
Hi, I have started a forum site with a couple of friends. The forum runs on xoops using php 4 with a mysql (4) database. I accidently deleted a few posts. No problem there because I had a copy of the text and posted it again. But the problem is the date and time - obviously it shows the date of the new post and not the date of the deleted one. As I am a newbie still experimenting with mysql and php, I'd sure appreciate your help.

Summary:
=======
I need to change the date of a post in the mysql database. I access the database using phpmyadmin.

Preoblemwith Displaying Utf8 Chars In Web Site
I have the following problem: I have a database entries with cyrillic characters and I cannot display them in my website (instead I get "?????"). I have set the files for the website in utf-8 character encoding and all the static characters in the web site are show as they should. The according table is in utf8_general_ci encoding and all the system character-/collation- variables are in utf8/utf8_general_ci encoding.

I would be very thankful for some help!

P.S.
character set client utf8
character set connection utf8
character set database utf8
character set filesystem binary
character set results utf8
character set server utf8
character set system utf8
collation connection utf8_general_ci
collation database utf8_general_ci
collation server utf8_general_ci