try removing your client for the moment: can you try a load data infile and see at what speed mysql loads the rows?
> -----Messaggio originale----- > Da: Alfredo Marchini [mailto:[EMAIL PROTECTED] > Inviato: venerd́ 25 giugno 2004 18.21 > A: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; > [EMAIL PROTECTED]; [EMAIL PROTECTED] > Oggetto: Re: info about MySQL Server > > > Hi, > thank you very much for your reply. > > Now I answer you about your additional questions: > > Now the daemon is installed on a machine different than DB Server, so I > use DBServer NIC IP Address for connect to it from the daemon (So I don't > use Unix Sockets). > > But, looking at the Network Bandwidth, there isn't bottleneck (but I have > read that Unix Socket are more quickly). > > My SCSI HD are not in RAID. Actually are fully indipendent and works on a > RAID controller 160Mb/s (the same speed of the disks). > > Probably in the future I'll use RAID tech that improves performance > (RAID-5, RAID-0+1), but not in this beginning step. > > J2SE project source compiled becomes a bytecode (not exe and not source); > this is runnable only on a Java Virtual Machine.... (It is like > half-compiled). > > Before decide to develope in J2SE I have made some tests about performance > differences between JDBC and MySQL client API.... > > MySQL client API obviously are more quickly, but no so much. Also I have > read documentations about the MySQL JDBC Connector that are the best Java > Native DB Connector (also better than other famous DBMS JDBC). > > Here is MYISAM table structure..... > > create table pippo > ( > type varchar(10) not null, > time_1 varchar(8) not null, > value decimal(8,2) not null, > state char(2) not null, > primary key (type, time_1) > ) > type = MyIsam; > > How you can see the table structure is very simple.... > > The MotherBoard is a GigaByte GA-7VA. > > here is the link about it: > > http://tw.giga-byte.com/MotherBoard/Products/Products_GA-7VA.htm > > Red Hat 9.B info: > > the kernel version is 2.4.20-20.9 i686. > > on the os are installed and run only > > 2 MySQL server,sshd,syslog,cron,atd,anacron. > > When I boot the machine, I have only 40Mb Ram used. > > All It seems ok. > > I don't have anymore to say... > > If you need other info, I am here.... > Thank you very much again. > > Best Regards > Alfredo Marchini > Developer Manager > SysNet Solutions S.r.l. > Via Rimini, 49 - Centro Dir. Leonardo da Vinci > 59100 Prato (PO) - Italy > Tel. +390574/484822 - Fax +390574/448075 > > > > > Alfredo, > > > > I have just a few additional questions that you did not already answer > in > > your very well-formed post. I normally wouldn't worry about ANY of these > issues but you seem to be exploring the envelope of performance on your > system and every little bit counts: > > > > Which communication path are you using to connect to your MySQL servers > from your application (TCP/IP or sockets or ...)? If TCP/IP are you > pointing to the IP address of your NIC or to 127.0.0.1? > > > > Are your HD's in a RAID configuration? If so which one? > > > > Could you post the results of SHOW CREATE TABLE so that we can see what > indexes, if any, you have on your data? > > > > Are there any other applications running on the same machine? I know you > said your CPU usage didn't pass 40% but am looking for disk I/O > congestion > > or data bus congestion or some other resource contention. > > > > Is your application compiled or interpreted? I am not familiar with J2SE > but I do know that some Java-based implementations give you the option > to > > compile applications into either a stand-alone executable or to object > code. Object code is interpreted by the Java runtime engine during > execution which may be slowing you down. > > > > (as a last resort) What is the speed of the data bus on your > motherboard? > > > > Thank you for your patience, > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > > > > > > > "Alfredo Marchini" > > <[EMAIL PROTECTED] To: > > [EMAIL PROTECTED] > > lutions.it> cc: > > [EMAIL PROTECTED] > > Fax to: > > 06/25/2004 03:24 AM Subject: info > > about MySQL Server > > Please respond to > > alfredo.marchini > > > > > > > > > > > > > > Dear Sir, > > > > Sergei Golubchik gives me this e-mail address telling me that you can > help > > me about a project that my company is developing. > > > > we need to develope a J2SE project that works on N MYSQL 4.0.20 servers > installed on the same machine. > > > > Now I try to explain my problem. > > > > The Hardware, actually, is a normal PC: > > > > CPU AMD ATHLON XP 2000+ > > RAM 770Mb DDR > > 2 * HD SCSI 160Mb/s. > > > > With a Red Hat Linux 9.B installed. > > > > Now (the beginning) I use only 2 MYSQL server, one for SCSI disk. > > > > The two MYSQL servers are totally indipendent. > > > > In either of these, I have created a database, and a MYISAM table. The > size of one record is 30 bytes and there is only an index made by two > fields (varchar(8) and varchar(10)). > > > > I need to insert in this table-structrure an amount of data that have to > be, at least, the size of 35.000 records for second, 24 hours for day, > 31 > > days for month. > > > > I reached, using only one MYSQL server, 20.000 records for second. > > > > Installing two services I hoped that I could reach 40.000 records for > second (or some minus) because MySQL servers should work in paraller > mode. > > > > But it isn't the truth, the speed doesn't increment. > > > > the J2SE project use multithreading technology, and is like a MySQL > servers load-balancer. > > > > Looking at the performance (with top), I see that the CPU never goes > over > > 40%, and the RAM is about 40%. > > These performance are similar using only one MySQL server or two MySQL > servers. > > > > I tried to correctly set MySQL system variables, I also tried to do an > higher priority to MySQL server processes, but the performance are the > same. > > Another strange thing is the thread_concurrencing running on MySQL > servers, looking at the status, threads running value never oltrepass 2, > and I set 8 thread_concurrency. > > > > Now I explain you how the final project should be: > > > > there is a J2SE software that runs as a daemon, it listen to a TCP port. > this daemon is connected (as a client with JConnector 3.0.14) to N MySQL > servers on a DBServer (at least 2 MYSQL services). > > > > the DBServer will be a very powerful server (because my customer needs > more than 35.000 records for second and many clients connected to the > daemon). > > > > A client connected to the daemon can do usually two things: > > > > asks the daemon to do insert data in database, or/and read data from > database. > > (So the daeamon uses only SELECT .. FROM ... WHERE ... LIMIT ... UNION > and > > INSERT INTO ... VALUES (),(),()). > > > > The client data requests are sent to the daemon that launchs queries on > the N MySQL Servers (as a balancer) and eventually gives the replies. > > > > The data have to be in real time (so I can't use INSERT DELAYED). > > > > the speed of the inserts have to be maximum as possible (at least 35.000 > records for second). > > > > During the insert operations another clients, in the same moment, could > ask the daemon to give him the data inserted in a period. > > > > usually the data have about a size of 70Mb (1 day data), and the maximum > request is to give data of one entire month. > > > > More Clients can do these types of requests. > > > > Obviously I will create 4 tables for a day for either MySQL service. Now > I > > don't know if I'll use MySQL commands (MERGE, RAID or others) or I'll do > all from my daemon (I have to do test to take this decision). > > > > This is all.... > > > > In your MySQL doc, I read that you have tested MySQL for a big Sweden > company, but you have not mentioned anything about the size of the data > that > > this company store, the clients connected, the size of the queries or > the > > number of inserts. > > > > First I would like to know if MySQL could reach these high > performance... > > > > Because my actually problem is the Hardware resource that aren't full > using my little PC. > > Why have I to buy a powerful server If this hardware isn't full used? > The > > bottleneck isn't in the SCSI disk (I installed MYSQL servers on RAMDISK > partitions with the same results). > > The bottleneck I think is MySQL, but I also think that it is well > configured. > > > > Here is my.cnf file (I have two equals configurations files, one for > MySQL > > server) > > > > port = 330[0-1] > > socket = /ptolle/ssc/db/mysql_[0-1].sock > > key_buffer = 256M > > max_allowed_packet = 1M > > table_cache = 512 > > sort_buffer_size = 70M > > read_buffer_size = 70M > > query_cache_size = 70M > > thread_cache = 8 > > thread_concurrency = 8 ---> no more than 2 threads run!!! > > max-connections = 200 > > skip-innodb > > > > Finally I tried also to compile the MySQL tarball with the gcc (the > version is 3.2.2-5) compiler parameters that are mentioned in your > documentation without better results. > > > > > > have you got any parameters that limit MySQL performance (in the header > libraries or others)? > > > > or have you got some informations about MySQL configuration that could > speed up MySQL or bring MySQL services to work in paraller mode and use > all hardware resource avalaible? > > > > Thank you very much. > > Best Regards > > > > > > Alfredo Marchini > > Developer Manager > > SysNet Solutions S.r.l. > > Via Rimini, 49 - Centro Dir. Leonardo da Vinci > > 59100 Prato (PO) > > Tel. 0574/484822 - Fax 0574/448075 > > > > -- > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
