Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQLyog Timing Out. How can I change the settings?

Posted on 2014-10-09
11
Medium Priority
?
534 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 2000 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 111

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

688 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