Solved

MySQLyog Timing Out. How can I change the settings?

Posted on 2014-10-09
11
516 Views
Last Modified: 2014-10-14
I'm using MySQLyog as an interface. I need to add an index to a table that's very large. I understand that in order to do this, the system is making a temporary copy of the table and that' s going to take some time given the amount of data that I've got. But regardless of how much time it takes, I'm confident this is a good and necessary step.

So, I run this command: "ALTER TABLE myable ADD INDEX ( posted_day );"

...and I get this error: "Error Code : 1878 Temporary file write failure. (524429 ms taken)"

I'm thinking this is because of the 250,000,000 rows I've got in the table.

How can I change my connection times so it doesn't timeout?

Screenshot of the interface is attached.
myqslyog.jpg
0
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 7

Expert Comment

by:Stampel
ID: 40370666
I believe its possible that you are hiting a free space problem or a size limit.
Can you give more info on the table  like table size and number of rows ?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370669
Oh sorry  250,000,000 rows .... definately a space problem.
Can you provide the size of the table and the space left on device ?
0
 

Author Comment

by:brucegust
ID: 40370694
Hey, Stampel!

Size of the table is 36538 and I'm not sure about the space left on the device, although I want to believe that it's pretty spacious. I'm not sure how to figure out how much space I have left on the device.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Expert Comment

by:Stampel
ID: 40370709
36538 ? is that Megabytes ?
What is the Operating System ? Linux ? Windows ?
Do you have access to a shell or a console ?
0
 

Author Comment

by:brucegust
ID: 40370753
That is MB and the Operating System is Windows. As far as the shell is concerned, no. I'm a contractor and I might be able to access it, but I was hoping to be able to do what needed to be done with the tools that were immediately at my disposal.
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370779
I understand.
I believe you do not have the required 36 Gigabytes free that are required to perform this operation.
You may check with them if there is sufficient free space.
0
 

Author Comment

by:brucegust
ID: 40370793
I'll do it!

Let me ask you this: Let's say they come back and say you've got plenty of room. What are my options at that point? If this is a situation where it's simply timing out because of a setting that can be adjusted, what would that setting be and how do I access it?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40370832
Ok if asking this is difficult, i propose that we try to verify you do not have sufficient space to do this.
For exemple try to make a simple copy of this table.
Then we will try to findout how much temporary space you can use before it fails.
With this error, it is not a timeout problem :)
0
 
LVL 7

Accepted Solution

by:
Stampel earned 500 total points
ID: 40370852
mysql> CREATE TEMPORARY TABLE foobar as (select * from liste);
Query OK, 1867635 rows affected (1 min 56.83 sec)
Records: 1867635  Duplicates: 0  Warnings: 0

Then play with "limit" to findout .. like this ....

mysql> CREATE TEMPORARY TABLE foobar2 as (select * from liste limit 1000);
Query OK, 1000 rows affected (0.42 sec)
Records: 1000  Duplicates: 0  Warnings: 0
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40371920
I'm going to make a slightly different suggestion.  Break the table up into several tables.  You may want to consult a DBA or Information Architect about the process, but the general strategy would go something like this.

1. Determine what data you will receive in the requests from your clients
2. Determine what data you will provide in response to the requests
3. Configure your table structure so that you will need to query the minimum number of tables to get from request to response.
4. Create a table organization that will let you know in advance (before starting any of the queries) which tables will need to be queried, based on the request data.

I would also add that any table with 250,000,000 rows is a different kind of beast from what we usually see in PHP + MySQL applications; this is into the scale of Facebook.  They get acceptable performance for data sets of this size by having rooms full of servers.  You might want to take this issue to the computer science department of a nearby college or university and get a grad student or teaching assistant to put this problem to their classes.  You'll probably get some innovative ideas about how to organize the data.  The reason that I think you need professional help with this is because of the data size.  If you break the big table into smaller tables with 250,000 rows each, you've got 1,000 tables to deal with.  If you break the big table into smaller tables with 1,000,000 rows each, you still have 250 tables to deal with.

So far, it seems we have been a little like the blind men with the elephant - looking at parts of the problem without seeing the big picture.  I've got a sense that the big picture may now need some serious attention in the form of creative thinking about information architecture!
0
 

Author Closing Comment

by:brucegust
ID: 40380569
Was able to increase size of tmp directory and, although it took a while, I was able to install all my indexes.

Rock on!
0

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question